Different Types of Joins in SQL Server
Aim :- This article will make you learn about Joins in SQL Server. I will provide you some Pictorial view and examples of Joins in SQL Server. I will be discussing on the 2 sections -
- Define joins in SQL Server?
- Different types of joins in SQL Server?
Description :- Now, I am going to explain you each section of joins in SQL Server into detail.
Question #1. What is a Join?
We all know how to fetch data from a SQL table. It is very easy – Just fire a Select query. You will get your desired output.
But what you will do if you require data which is present in 2 or more SQL tables. This gives rise to a powerful SQL clause known as Join. Joins in SQL Server are used to combine rows from two or more tables. This join operation is based on a common field between the tables.
Question #2. What are different types of joins in sql server?
Generally we have three types of joins. They are as following -
- Inner Join.
- Outer Join.
- Self Join.
These 3 categories are than further sub-divided into various types of Joins in SQL Server. I am mentioning below all the joins you will hear in your SQL life.
- Inner Join is divided into – Equi Join || Natural Join || Cross Join.
- Outer Join is divided into - Left Outer Join || Right Outer Join || Full Outer Join.
I will provide some brief information about each Joins in SQL Server. Also for better understanding, I will show all types of joins in Pictorial format. The pictorial format will give more idea to understand the functionality of each join. I hope you will like and enjoy learning Joins in SQL Server this way.
Type 1. Inner Join -
An Inner join in SQL Server returns “Matched Rows” from the multiple tables. Just follow the below picture, it will clearly show how inner join performs operations internally. Inner join can be further sub divided into 3 types -
- Equi Join.
- Natural Join.
- Cross Join.
Equi Join - The Equi join in SQL Server is used to display all the “Matched Rows” from the Joined tables and also display Redundant data. In this join, we need to use * sign to join the table.
Natural Join - The Natural Join in SQL Server is same as Equi Join but the only difference is that it will not display Redundant data.
Cross Join - This join is a Cartesian join and does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables. In Simple words, Cross join in SQL Server gives a Cartesian product of multiple tables.
Type 2. Outer Join -
An Outer join in SQL Server returns “Matched Rows” as well as “Unmatched Rows” from the multiple tables. Outer join is classified into 3 types -
- Left Outer Join.
- Right Outer Join.
- Full Outer Join.
Left Outer Join - A Left Outer Join in SQL Server returns the “Matched Rows” from multiple tables and “Non Matched Rows” from Left side table. Follow the above picture for more understanding.
Right Outer Join - A Right Outer Join in SQL Server returns the “Matched Rows” from multiple tables and “Non Matched Rows” from Right side table. Follow the above picture for more understanding.
Full Outer Join - A Full Outer Join returns “Matched Rows” from multiple tables and also “Non Matched Rows” from multiple tables. Follow the above picture for more understanding.
Summary :- In this article on Joins in SQL Server, we learned -
- What is a Join and Why we use this Clause.
- What are different types of Joins available in SQL Server.
- Pictorial representation of each Join.
I hope you enjoyed my article on Joins in SQL Server. Please drop your valuable comments once you go through this. Also, provide your suggestions for my Skills enhancement. I will do my level best to implement your suggestions from next article. Thanks for spending your valuable time with us. Stay tune to PhpRing Tutorials foe more easy and Simple Tutorials.