Different types of joins in SQL

We have three types of joins in SQL:

a. INNER JOIN

b. OUTER JOIN

c. CROSS JOIN

To understand how the joins work let us first create two table Employee and Department:

CREATE TABLE [dbo].[Employee]

(

[EmpID] [int] NOT NULL,

[FirstName] [nvarchar](250) NOT NULL,

[LastName] [nvarchar](250) NULL,

[Salary] [money] NULL,

[DeptID] [int] NULL

)

INSERT INTO [dbo].[Employee]

(

[EmpID]

,[FirstName]

,[LastName]

,[Salary]

,[DeptID]

)

VALUES

(1, 'Anil', NULL, 10000, 1)

,(2, 'Srikanth', NULL, 20000, 2)

,(3, 'Ramesh', NULL, 30000, 4)

,(4, 'Sunil', NULL, 40000, 1)

,(5, 'Suresh', NULL, 50000, 3)

,(6, 'Shravan', NULL, 60000, 6)

CREATE TABLE [dbo].[Department]

(

[DeptID] [int] NOT NULL

,[DeptName] [nvarchar](50)

)

INSERT INTO [dbo].[Department]

(

[DeptID]

,[DeptName]

) VALUES

(1, 'CSE')

,(2, 'ECE')

,(3, 'EEE')

,(4, 'MEC')

,(5, 'CIV')

,(1, 'ARE')

a. INNER JOIN:

It will give only the matching records from both the tables on the basis of joining column.

In this, joining column from the first table will be matched with the joining column from the second table for each and row available in the second table and only the matching records will be returned.

Most of the people think like we don't get more than number of rows available in joining tables.
That is if we have 4 rows in left table and 3 rows in right table then we may think like we will get maximum of 4 rows but that is not true.

Go through below example to know how we will get more number rows than we have in both the tables.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

INNER JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Before looking at the result set returned by this query, guess how many rows this query will return.

Number of rows returned by this query is not equal to the number of rows available in left table (i.e)6.

Why because here the joining column is DeptID and for DeptID = 1 we have two matching rows from the right table. So it will return two rows for the record with DeptID = 1 form the left table that's why we will see more number of rows than present in left table.

Result:

b. OUTER JOIN:

We have three types of joins in this.

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN

In all these commands OUTER clause is optional, we need not to mention that in our query just we can write LEFT JOIN, RIGHT JOIN, FULL JOIN.

I. LEFT JOIN:

It will give all records from the left table and only matching records from right table.

If any value is not matching with the right table then it will return NULL value in the column coming from the right table.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

LEFT JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Result:

If we see the result set for DeptID coming from Employee (DeptIDFromEmp) table we have value 6 in the last row and for the DeptID column (DeptIDFromDept) coming from Department table we have NULL value because for that value we don’t have any matching record from right table where as from the left table we have some value that is the reason it is showing up in the column coming from left table but not in the column coming from right table.

II. RIGHT JOIN:

It will give all the records from right table and only the matching records from left table.

If any value is not matching with the left table then it will return NULL value in the column coming from the left table.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

RIGHT JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Result:

III. FULL JOIN:

It's a combination of both LEFT and RIGHT join that means it will give all matching records from both the tables, non matching records from left table (in this case it will insert NULL values in the columns coming from the left table) and non matching records from right table (in this case it will insert NULL values in the columns coming from the right table).

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

FULL JOIN [dbo].[Department] D

ON E.[DeptID] = D.[DeptID]

Result:

CROSS JOIN:

It will return all the records from the right table for each record in left table.

For example if we have 2 rows in left table and 3 rows in right table then each record will be mapped with all the records from the right table so we will get 2*3 = 6 records.

Here we need not to mention any ON condition as it matches each record from left table with all the records from right table.

SELECT

E.[EmpID]

,E.[FirstName]

,E.[LastName]

,E.[Salary]

,E.[DeptID] AS DeptIDFromEmp

,D.[DeptID] AS DeptIDFromDept

,D.[DeptName]

FROM

[dbo].[Employee] E

CROSS JOIN [dbo].[Department] D

Result:

Hope you understood the joins, if you still have any questions please comment.