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.


The result will be as below.

Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL