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 –

  1. Define joins in SQL Server?
  2. 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 –

  1. Inner Join.
  2. Outer Join.
  3. 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.

Different types of Joins in  SQL Server

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 -

  1. Equi Join.
  2. Natural Join.
  3. 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 –

  1. Left Outer Join.
  2. Right Outer Join.
  3. 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 –

  1. What is a Join and Why we use this Clause.
  2. What are different types of Joins available in SQL Server.
  3. 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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.8/10 (16 votes cast)
VN:F [1.9.22_1171]
Rating: +12 (from 12 votes)
Different Types of Joins in SQL Server, 9.8 out of 10 based on 16 ratings

Comments

  1. By madhubabu

    Reply

    • Reply

  2. By madhubabu

    Reply

    • Reply

  3. By vinay

    Reply

    • Reply

  4. By Ashok

    Reply

    • Reply

  5. By Aanchal Kapoor

    Reply

    • Reply

  6. By sanjiv

    Reply

    • Reply

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Current ye@r *