SQL Joins

What is SQL Join ?

A SQL Join is used to fetch or combine data (rows or columns) from two or more tables based on the defined conditions

Types of SQL Join

Following are the different types of joins used in SQL:

• Inner Join / Join
• Left Outer Join / Left Join
• Right Outer Join / Right Join
• Full Outer Join
• Cross Join
• Self Join

Venn diagram for Joins :

To explain the examples we will consider the below tables as our input for all the joins except for Cross/Self Join :

Student

Roll_No	Name	Address	Age
1	Dheeraj	Delhi	18
2	Rakesh	Karnataka	19
3	Naveen	Andhra Pradesh	20
4	Sandhya	Tamil Nadu	21
5	Rohit	Punjab	22
6	Pratik	Maharashtra	23
7	Niranjan	Kerala	24
8	Harsh	MP	25

StudentCourse

Course_ID	Roll_No
1	1
2	2
2	3
3	4
1	5
4	9
5	10
4	11

1. Inner Join

• The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies
• This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e. value of the common field will be same

Example :

Refer Input tables : tables

In this example we are selecting and inner joining the table StudentCourse and Student based on the common field that is Roll_No.

Inner Join Query: 

Select StudentCourse.Course_Id, Student.Name, Student.Age
   from Student INNER JOIN StudentCourse 
   ON Student.Roll_No = StudentCourse.Roll_No;

Output :

Course_Id	Name	Age
1	Dheeraj	18
2	Rakesh	19
2	Naveen	20
3	Sandhya	21
1	Rohit	22

2. Left Outer Join/Left Join

• This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join
• The rows for which there is no matching row on right side, the result-set will contain null value for right side table

Example :

Refer Input tables : tables

In the example query below, Left table : Student and Right table : StudentCourse

Left Join Query: 

Select Student.Name, Student.Age, StudentCourse.Course_Id 
from Student LEFT JOIN StudentCourse 
ON StudentCourse.Roll_No = Student.Roll_No;

Output :

• Name, Age - Left table(Student) - All rows are returned
• Course_Id - Right table(StudentCourse) - only matching rows based on Roll_No has the value for the column Course_Id, the non-matching rows are set as null values.

Name	Age	Course_Id
Dheeraj	18	1
Rakesh	19	2
Naveen	20	2
Sandhya	21	3
Rohit	22	1
Pratik	23	Null
Niranjan	24	Null
Harsh	25	Null

3. Right Outer Join/Right Join

• Right Join is similar to Left Join
• This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join
• The rows for which there is no matching row on left side, the result-set will contain null

Example :

Refer Input tables : tables

In the e.g. query below, Left table : Student and Right table : StudentCourse

Right Join Query :

SELECT Student.Name, Student.Age, StudentCourse.Course_Id
FROM Student RIGHT JOIN StudentCourse 
ON StudentCourse.Roll_No = Student.Roll_No;

Output :

• Name, Age - Left table(Student) -  Returned the values for the matching rows on the left side of join, non-matching rows are returned as null
• Course_Id - Right table(StudentCourse) - Returned all the rows

Name	Age	Course_Id
Dheeraj	18	1
Rakesh	19	2
Naveen	20	2
Sandhya	21	3
Rohit	22	1
Null	Null	4
Null	Null	5
Null	Null	4

4. Full Outer Join/Full Join 

• FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. 
• The result-set will contain all the rows from both the tables. 
• The rows for which there is no matching, the result-set will contain NULL values

Output :

Refer Input tables : tables

Full Join is the combination of left and right join. It returns all the rows from both left and right join, matching rows will have values, non-matching rows will have the value set as null

Full Join Query :

SELECT Student.Name,Student.Age, StudentCourse.Course_Id
FROM Student FULL JOIN StudentCourse 
ON StudentCourse.Roll_No = Student.Roll_No;

Output :

Name	Age	Course_Id
Dheeraj	18	1
Rakesh	19	2
Naveen	20	2
Sandhya	21	3
Rohit	22	1
Pratik	23	Null
Niranjan	24	Null
Harsh	25	Null
Null	Null	4
Null	Null	5
Null	Null	4

 5. Cross Join

• The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. 
• This join type is also known as cartesian join
• The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2)

Example :

Consider 2 input tables

 CROSS JOIN Query : 

    SELECT *
    FROM table1 CROSS JOIN  table2

Output : 

Number	Alphabet
1	A
2	A
3	A
1	B
2	B
3	B
1	C
2	C
3	C

 6. Self Join

• A self join allows you to join a table to itself
• It helps query hierarchical data or compare rows within the same table
• A self join uses the inner join or left join clause
• Because the query that uses the self join references the same table, the table alias is used to assign different names to the same table within the query

Example :

Consider the input table

Staffs table

Staff_Id	FirstName	LastName	Manager_Id
1	Ramesh	Kumar	NULL
2	Rohit	M	1
3	Aarav	K	2
4	Aditya	Sharma	2
5	Dhruv	Patil	3

Self Join Query using INNER JOIN : 

SELECT
    e.[FirstName] + ' ' + e.[LastName] employee,
    m.[FirstName] + ' ' + m.[LastName] manager
FROM
   [Staffs] e
INNER JOIN [Staffs] m ON m.staff_id = e.manager_id
ORDER BY  manager;

Output :

employee	manager
Dhruv Patil	Aarav K
Rohit M	Ramesh Kumar
Aarav K	Rohit M
Aditya Sharma	Rohit M

Using LEFT JOIN Query: 

SELECT
    e.[FirstName] + ' ' + e.[LastName] employee,
    m.[FirstName] + ' ' + m.[LastName] manager
FROM
   [Staffs] e
LEFT JOIN [Staffs] m ON m.staff_id = e.manager_id
ORDER BY  manager;

Using LEFT JOIN output: 

employee	manager
Ramesh Kumar	NULL
Dhruv Patil	Aarav K
Rohit M	Ramesh Kumar
Aarav K	Rohit M
Aditya Sharma	Rohit M