How To Join Tables Data in SQL Server

Joins in SQL Server, used to join two or more tables according to a specified conditions. There are three major types of Joins in SQL Server.

Inner Join

Inner Join is used to retrieve rows from two or more tables based on a common column or join-predicate. The query compares each row of table A with each row of table B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. For Example, we have two tables tblDepartment and tblEmployee, and we want to retrieve data whose DeptID matches in both tables.

Firs of all, we create two tables tblDepartment and tblEmployee which has Primary-Foreign key relationship on DeptID and EmpDeptID columns.

In tblDepartment table we have 4 departments (hr, finance, marketing and sales), and in tblEmployee table we did not have any entry which has EmpDeptID 4(Sales) so in INNER JOIN query Sales department will not appear.

Let’s create table tblEmployee.

Create INNER JOIN on two tables tblDepartment and tblEmployee with a common field DeptID and EmpDeptID.

Result of above INNER JOIN query is as below.

Outer Join

Outer Join is further divided into three categories

1-Left Outer Join/Left Join

Left Outer Join/Left Join is used to retrieve matching records from the left table A and right table B and non-matching rows from the left table A.

In the below example tblDepartment is the left table which has 4 departments(hr, finance, marketing and sales) and the right table tblEmployee has 10 records which are matching in table tblDepartment and tblEmployee table. But in the left table, tblDepartment sales does not match with any entry in tblEmployee table.

Let’s see the result as below.

2. Right Outer Join/Right Join

Right Outer Join/Right Join is used to retrieve records from the left table A and right table B and also the non-matching records from the right table B.

In the below example, tblEmployee is the left table and tblDepartment is the right table. Now, the query for RIGHT JOIN is as below.

The result will be as below.

3. Full Outer Join/Full Join

In Full Outer Join/Full Join, matching and non-matching records are retrieved from the left table A and right table B.

The result will be as below.

Cross Join

Cross Join is like a Cartesian product of two sets. Here, in SQL Server, Cross Join is used to generate a paired combination of each row of the first table with each row of the second table.

In the below example, we have two tables, tblEmployee and tblDepartment. Where tblEmployee has 10 rows and tblDepartment has 4 rows. The CROSS JOIN is the Cartesian product of two tables(each row of tblEmployee will be paired with each row of tblDepartment), so as a result, 40 rows will be retrieved.