SQL Day 12: Joins

LO1: Inner Join

LO2: Outer Join

LO3: Cross Join

Prerequisite: Creating Tables & Enforcing Primary and Foreign Key

Joins are used to retrieve data from 2 or more tables based on given conditions.In SQL Server, there are different types of joins.

Inner Join or Join

Inner Join is used to retrieve the matching rows of two tables. When we use the inner join then it fetches the rows that are common in both the table and ignore the non-matching rows if any.

Let’s say we have a tblEmployee tables which contain the DepartmentID column and tblDepartment table which contain different types of the department including locations.

select * from tblEmployee
select * from tblDepartment

The tblEmployee table contains the department id’s for the employee and tblDepartment table contain the name of the department including the id of the department.

Let’s say we need to find the department name of the employees, to do that we need to join the tblEmployee and tblDepartment tables using the inner join.

Syntax:

Select         ColumnList
from  LeftSideTable
INNER JOIN RightSideTable
ON  JoinCondition

Note: The above syntax will work for all types of joins. The left side table is tblEmployee and right side table is tblDepartment.

Example:

select Name, Email, DepartmentName
from tblEmployee
INNER JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

Here tblEmployee.DepartmentID tells the SQL server that we are comparing DepartmentID column of the tblEmployee table to ID column of the tblDepartment table.

It is possible that two different table have the same column so to differentiate the column we need to specify for which table column we are comparing values. If not specified then SQL throw Ambiguous Column error if two table has the same column name.

We can also use the alias name instead of specifying the complete table name.

There are two ways to do that, first is explicit defines the alias using as the keyword.

select Name, Email, DepartmentName
from tblEmployee as e
INNER JOIN tblDepartment as d
ON e.DepartmentID = d.ID

Second is not defining the as the keyword.

select Name, Email, DepartmentName
from tblEmployee e
INNER JOIN tblDepartment d
ON e.DepartmentID = d.ID

You can also give only Join instead of Inner Join keyword in the query. SQL server considers Join as inner join by default.

select Name, Email, DepartmentName
from tblEmployee
JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

In all of above ways, the result will be the same.

So the Inner Join will give us only the matching rows in the two or more tables.

Outer Join

Outer Join can be of three types:

1. Left Outer Join or Left Join
2. Right Outer Join or Right Join
3. Full Outer Join or Full Join

Left Outer Join or Left Join

Left outer join is used to retrieve the matching row of tables and non-matching rows of the left table.

In the tblEmployee table, record id 12 not has any department id value so when we use the Left Join then this row will return in the result.

select Name, Email, DepartmentName
from tblEmployee
LEFT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

In the above result, the record is returned which has NULL in department id column.

We can also use the LEFT JOIN keyword instead of Left Outer Join; both will return the same result.

select Name, Email, DepartmentName
from tblEmployee
LEFT JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

So the Left Outer Join will give us matching rows + non-matching rows from the Left table.

Right Outer Join or Right Join

Right outer join is used to retrieve the matching row of tables and non-matching rows of the right table.

In the tblDepartment table, we have the Department Name as Other for which none of the records exists in the tblEmployee table so when we use the Right Outer Join then this row will return in the result.

select Name, Email, DepartmentName
from tblEmployee
RIGHT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

We can also use the RIGHT JOIN keyword instead of Right Outer Join; both will return the same result.

select Name, Email, DepartmentName
from tblEmployee
RIGHT JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

So the Right Outer join will give us matching rows + non-matching rows from the Right table.

Full Outer Join or Full Join

Full outer join is used to retrieve the matching row of tables and non-matching rows of both tables.

All the rows will return of both tables including matching and non-matching records.

select Name, Email, DepartmentName
from tblEmployee
FULL OUTER JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

We can also use the FULL JOIN keyword instead of Full Outer Join; both will return the same result.

select Name, Email, DepartmentName
from tblEmployee
FULL JOIN tblDepartment
ON tblEmployee.DepartmentID = tblDepartment.ID

So the Full Outer join will give us matching rows + non-matching rows from Both tables.

Cross Join

Cross Join produces the Cartesian product of the two tables involved in the join. For example, we have the tblEmployee table which has 8 rows and tblDepartment table which has 4 rows so the Cartesian products of this will be 32 rows i.e. Cross join will return the 32 rows.

Cross Join not have any condition i.e. ON clause is not used for the Cross Join.

select Name, Email, DepartmentName
from tblEmployee
CROSS JOIN tblDepartment

That’s all for today. Thank You.