Different Type of SQL Joins

Joins in SQL server are used to retrieve data from two or more related tables. In general tables are related to each other using foreign key constraints.

In SQL server, there are different types of joins

1. Inner Join
2. Outer Join
3. Cross Join

Outer Joins are again divided as

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

Let’s understand Join types with examples and the differences between them.

Read More: Different Types of SQL Keys

1.Employee Table (tblEmployee)

2.Department table (tblDepartment)

1. Inner Joins: -

Return only matching rows between both the tables. On matching rows are eliminated.  

Read: Top 97 Data Modeling Interview Questions and How To Answer Them

SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee INNER JOIN tblDepartment ON tblEmployee.DepartmentId=tblDepartment.Id

Read More: Different Types of SQL Database Functions

If you look at the output we got only 8 rows but in Employee table it has 10 rows. We didn’t got James and Russell records. This is because DepartmentId, in Employee table is NULL for these two employees and doesn’t match ID column in Department table.

2.Left Join or Left Outer Join: -

Returns all the matching rows and non-matching row from left table. In reality, LEFT JOIN and INNER JOIN are extensively used.  

 SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee LEFT OUTER JOIN tblDepartment ON tblEmployee.DepartmentId=tblDepartment.Id

Read: Skill Yourself by Learning SQL & Enhance Your Career Prospects

3.Right Join or Right Outer: -

Returns all the matching rows and non-matching row from right table.

 SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee RIGHT JOIN tblDepartment ON tblEmployee.DepartmentId=tblDepartment.Id

4. Full Join or Full Outer Join: -

Returns all the rows from both left and right of the table, including non-matching rows.

 SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee FULL JOIN tblDepartment ON tblEmployee.DepartmentId=tblDepartment.Id

Cross Join: -

Read: How to Create Database in Microsoft SQL Server?

Read More: Different Types of SQL Injection

Cross Join produces the cartesian product of the two tables involved in the join. For example, in the employee table we have 10 records and in the department table we have 4 records. So as a cross join between two tables it will produce 40 records. Cross join shouldn’t have ON clause.

 SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee CROSS JOIN tblDepartment