Understanding Different Types of SQL JOINs with Examples

SQL JOINs are used to retrieve set of information from two or more different tables based upon certain common values between them. We use joins to combine tables with Select SQL statements.

Related Articles:

Types of Joins

 There are four types of Joins and each type is having separate ways to retrieve data.

  • Inner Join
  • Right Join
  • Left Join
  • Full Join

We will use two tables Employees and Leavesdetails to explain you each type of Joins with example. The details of both tables are given below.

Table Employees:

Employees Table

Table LeavesDetails:

LeavesDetails Table

 We will apply all types of Joins between these two tables to show you how they work. Let’s start with Inner Join. 

Inner Join 

Inner join will show data that is matched between both joined tables. This is default join where result is based upon where clause and matched value from all rows fetch data. The visual representation of inner join will look like below image. You can see the coloured area that is common between both tables and that will be output of INNER JOIN.

Inner Join

As per above image, both tables Employees and Leavesdetails have been joined together to get output details for rows that are matched to each other.

SELECT *
FROM EMPLOYEES
INNER JOIN [dbo].[LeavesDetails]
ON [LeaveDetails].[Employeeid]=[Employees].[Employeeid]

inner join example

Right Join

When we use Right Join keyword, it will return all rows from table 2 that is placed right side of RIGHT JOIN keyword and matched rows from table 1 that is mentioned just left to the RIGHT JOIN keyword in SQL statement. In right join, if rows do not match with table 1 it will return null value. Sometimes, we call it as Right Outer Join as well. The visual diagram of Right Join is given below.

right join

Example

We can see Employees table is mentioned as table 2 and placed just right after keyword RIGHT JOIN and Leavesdetails table has been mentioned as table 1 that is just left to the keyword RIGHT JOIN.

SELECT * FROM Leavesdetails
RIGHT JOIN Employees
ON [Employees].[Employeeid]=[Leavesdetails].[Employeeid]

You can see all values have been displayed from table Employees  along with corresponding details from table 2 Leavesdetails whereas there is one null value row from table Leavesdetails because there is no matched entry for this row from Employees table.

Right Join Example

Left Join

When we use LEFT JOIN keyword, it will return all rows from table 1 that is placed just before or left side to the LEFT JOIN keyword and matched rows from table 2 that is mentioned just right side to the LEFT JOIN keyword in SQL statement. In left join, if rows do not match with table 2 it will return null value. Left Join is also known as left outer join. You can see the visual representation of Left Join in below image.

left join

Example

you can see, now Employee table is mentioned left side of LEFT JOIN keyword and Leavesdetails table is mentioned in right side of this keyword so Employee table will work as table 1 and Leavesdetails will work as table 2.

SELECT *
FROM Employees
LEFT JOIN Leavesdetails
ON [Leavesdetails].[Employeeid]=[Employees].[Employeeid]

We can see all records from Employees table along with its corresponding details from table 2 Leavesdetails whereas one null values from table2 that is Leavesdetails because it’s matching with the data presented in table 1 Employees table. As highlighted record does not exist in table 2 it is showing null value.

Left Join Example

Full Join

When we use FULL JOIN, it fetches results from both tables when there is a match in either in left or right table. We can also say it is kind of combination of left and right join.

full join

Example

As visual representation shows, it will show all records from Employees table and Leavesdetails table. You can see in below result set Employee Manvendra has not any leaves detail and same way Employee ID 108 has not any record in Employees table but both rows are showing their values.

SELECT *
FROM EMPLOYEES
FULL JOIN [dbo].[Leavesdetails] on [Leavesdetails].[Employeeid]=[Employees].[Employeeid]

full join example
I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Maruti Nandan

I am working at one of the top MNCs and have extensive experience on all versions of SQL Server since 2000,I have good hands on experience onfollowing database features: Database Mirroring, Always ON, Replication, Log Shipping, Geo-Clustering and Performance Tuning and delivered many SQL Server projects on consolidation, upgrades, heterogeneous replication, HA / DR solutions, automation and major performance tuning
Summary
Article Name
Understanding Different Types of SQL Joins
Description
SQL JOINs are used to retrieve set of information from two or more different tables based upon certain common values between them. We use joins to combine tables with Select SQL statements.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *