Joins, Joining Multiple Tables, different types of joins, Sub queries, Types of

subqueries, single row subquery, multiple row subquery, Top N Analysis, DML
Statements insert, update, delete. Merge. Oracle data types, TCL Command:
commit, rollback and savepoint Set Operators: Union, intersect & Minus Clause

JOIN: In relational databases, join refers to an operation that combines rows from
two or more tables based on a related column between them. Joins allow querying
across tables to retrieve data that’s connected.

The join clause allows us to retrieve data from two or more related tables into a
meaningful result set. We can join the table using a SELECT statement and a join
condition. It indicates how SQL Server can use data from one table to select rows
from another table. In general, tables are related to each other using foreign
key constraints.

INNER JOIN
This JOIN returns all records from multiple tables that satisfy the specified join
condition. It is the simple and most popular form of join and assumes as
a default join. If we omit the INNER keyword with the JOIN query, we will get
the same output.
 The following visual representation explains how INNER JOIN returns the
matching records from table1 and table2:

INNER JOIN

This type of SQL server JOIN returns rows from all tables in which the join condition is
true. It takes the following syntax:

Table: Student

Table: Fee

We can demonstrate the INNER JOIN using the following command:

1. SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.cou

rse, Fee.amount_paid
2. FROM Student
3. INNER JOIN Fee
4. ON Student.admission_no = Fee.admission_no;
 This command gives the below result:

In this example, we have used the admission_no column as a join condition to

get the data from both tables. Depending on this table, we can see the
information of the students who have paid their fee.

SELF JOIN
A table is joined to itself using the SELF JOIN. It means that each table row is
combined with itself

The SELF JOIN can be thought of as a JOIN of two copies of the same tables. We can
do this with the help of table name aliases to assign a specific name to each table's
instance

It's a useful way to extract hierarchical data and comparing rows inside a single table.

SELF JOIN Syntax

1. SELECT T1.col_name, T2.col_name...
2. FROM table1 T1, table1 T2
3. WHERE join_condition;

Example

We can demonstrate the SELF JOIN using the following command:

1. SELECT S1.first_name, S2.last_name, S2.city

2. FROM Student S1, Student S2
3. WHERE S1.id <> S2.iD AND S1.city = S2.city
4. ORDER BY S2.city;

This command gives the below result:

 In this example, we have used the id and city column as a join condition to get
the data from both tables.

CROSS JOIN
CROSS JOIN in SQL Server combines all of the possibilities of two or more tables
and returns a result that includes every row from all contributing tables. It's also
known as CARTESIAN JOIN because it produces the Cartesian product of all
linked tables. The Cartesian product represents all rows present in the first table
multiplied by all rows present in the second table.

The below visual representation illustrates the CROSS JOIN. It will give all the
records from table1 and table2 where each row is the combination of rows of
both tables:

CROSS JOIN Syntax

1. SELECT column_lists
2. FROM table1
3. CROSS JOIN table2;

Example

We can demonstrate the CROSS JOIN using the following command:

1. SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.cou

rse, Fee.amount_paid
2. FROM Student
3. CROSS JOIN Fee
4. WHERE Student.admission_no = Fee.admission_no;
This command gives the below result:

OUTER JOIN
OUTER JOIN in SQL Server returns all records from both tables that satisfy
the join condition. In other words, this join will not return only the matching
record but also return all unmatched rows from one or both tables.

We can categories the OUTER JOIN further into three types:

o LEFT OUTER JOIN

o RIGHT OUTER JOIN
o FULL OUTER JOIN

LEFT OUTER JOIN

The LEFT OUTER JOIN retrieves all the records from the left table and
matching rows from the right table. It will return NULL when no matching
record is found in the right side table. Since OUTER is an optional keyword, it is
also known as LEFT JOIN.

The below visual representation illustrates the LEFT OUTER JOIN:

LEFT OUTER JOIN Syntax

The following syntax illustrates the use of LEFT OUTER JOIN in SQL Server:
1. SELECT column_lists
2. FROM table1
3. LEFT [OUTER] JOIN table2
4. ON table1.column = table2.column;

Example

We can demonstrate the LEFT OUTER JOIN using the following command:

1. SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee

.amount_paid
2. FROM Student
3. LEFT OUTER JOIN Fee
4. ON Student.admission_no = Fee.admission_no;

This command gives the below result:

This output shows that the unmatched row's values are replaced with NULLs in
the respective columns.

RIGHT OUTER JOIN

The RIGHT OUTER JOIN retrieves all the records from the right-hand table
and matched rows from the left-hand table. It will return NULL when no
matching record is found in the left-hand table. Since OUTER is an optional
keyword, it is also known as RIGHT JOIN.

The below visual representation illustrates the RIGHT OUTER JOIN:

 RIGHT OUTER JOIN Syntax

The following syntax illustrates the use of RIGHT OUTER JOIN in SQL Server:

1. SELECT column_lists
2. FROM table1
3. RIGHT [OUTER] JOIN table2
4. ON table1.column = table2.column;

Example

The following example explains how to use the RIGHT OUTER JOIN to get
records from both tables:

1. SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee

.amount_paid
2. FROM Student
3. RIGHT OUTER JOIN Fee
4. ON Student.admission_no = Fee.admission_no;

This command gives the below result:

In this output, we can see that no column has NULL values because all rows in
the Fee table are available in the Student table based on the specified condition.

FULL OUTER JOIN

The FULL OUTER JOIN in SQL Server returns a result that includes all rows
from both tables. The columns of the right-hand table return NULL when no
 matching records are found in the left-hand table. And if no matching records
are found in the right-hand table, the left-hand table column returns NULL.

The below visual representation illustrates the FULL OUTER JOIN:

FULL OUTER JOIN Syntax

The following syntax illustrates the use of FULL OUTER JOIN in SQL Server:

1. SELECT column_lists
2. FROM table1
3. FULL [OUTER] JOIN table2
4. ON table1.column = table2.column;

Example

The following example explains how to use the FULL OUTER JOIN to get records
from both tables:

1. SELECT Student.admission_no, Student.first_name, Student.last_name, Fee.course, Fee

.amount_paid
2. FROM Student
3. FULL OUTER JOIN Fee
4. ON Student.admission_no = Fee.admission_no;

This command gives the below result:

In this output, we can see that the column has NULL values when no matching
records are found in the left-hand and right-hand table based on the specified
condition.

Qualifying Ambiguous Column Names:

• Use table prefixes to qualify column names that are in multiple tables.

• Improve performance by using table prefixes.

• Distinguish columns that have identical names but reside in different tables by using
column aliases.

Using Table Aliases

• Simplify queries by using table aliases.

• Improve performance by using table prefixes.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

FROM employees e , departments d
WHERE e.department_id = d.department_id;

Creating join with USING clause

• If several columns have the same names but the data types do not match, the
NATURAL JOIN clause can be modified with the USING clause to specify the
columns that should be used for an equijoin.
• Use the USING clause to match only one column when more than one column
matches.
• Do not use a table name or alias in the referenced columns.
• The NATURAL JOIN and USING clauses are mutually exclusive.

Creating Joins with the ON Clause

• The join condition for the natural join is basically an equijoin of all columns with the
same name.

• To specify arbitrary conditions or specify columns to join, the ON clause is used.

• The join condition is separated from other search

Conditions.

• The ON clause makes code easy to understand.