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.
- Read UNION vs UNION ALL
- Logon Trigger to Restrict sysadmin logins
- Why rou count showing incorrect value in sys.partitions?
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.
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 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.
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]
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.
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.
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.
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.
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.
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]
- How to Fix SQL Server Installation Error 1639? - February 3, 2018
- Understanding Different Types of SQL JOINs with Examples - December 14, 2017
- How to update License Key or Product Key of SQL Server Instance - September 6, 2017