Blog

Avinash Reddy Munnangi
Jan 5 th, 2014
SQL12 Comments

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.7/10 (19 votes cast)
VN:F [1.9.22_1171]
Rating: +14 (from 14 votes)
Different Types of Joins in SQL Server, 9.7 out of 10 based on 19 ratings

12 Comments on this article

  • madhubabu January 5, 2014

    What is differences between the natural join and inner join with examples.

    Reply

    • Avinash Reddy Munnangi January 13, 2014

      First of all Thanks a lot for visiting PHPRING. I hope u found this blog and Article as useful.

      A Natural join is a one kind of join belongs to Join’s family but it doesn’t work in Sql server. I thought it may work in Oracle and Mysql. There is no syntax to apply Natural join in Sql server. Apart from this a inner join is a join it will take the Matched rows result as output from multiple tables.

      Suppose i have two tables called as employee and one more table dept.
      Follow Below Example for more understanding

      –create table Employee
      create table employee
      (id int,
      name varchar(5),
      deptno int)

      –create table Department

      create table department
      (deptno int,
      dname varchar(10))

      –insert few rows in Employee and Department table
      insert into employee values(1,’A’,10),(2,’B’,20),(3,’C’,30)(4,’D’,40),(5,’E’,10),(6,’F’,20),(7,’G’,30)

      insert into department values(10,’Sales’),(20,’Purchase’),(30,’Finance’)
      –NATURAL JOIN:-
      Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns.

      –Apply Natural Join on both the tables Employee and Department

      select * from employee natural join department
      on employee.deptno=department.deptno

      — The above Query will throw the below Error
      Msg 4104, Level 16, State 1, Line 3
      The multi-part identifier “employee.deptno” could not be bound.

      Suppose if you apply Natural join in Oracle and MYsql U may see the below result.
      id name deptno dname
      1 A 10 Sales
      2 B 20 Purchase
      3 C 30 Finance
      5 E 10 Sales
      6 F 20 Purchase
      7 G 30 Finance

      Note :- Natural Join will only Works in Oracle and MySql it Can’t work in Sql server. I hope u understand this .

      –Inner Join

      select * from employee inner join department
      on employee.deptno=department.deptno

      id name deptno deptno dname
      1 A 10 10 Sales
      2 B 20 20 Purchase
      3 C 30 30 Finance
      5 E 10 10 Sales
      6 F 20 20 Purchase
      7 G 30 30 Finance

      Just see the above query result it will get the all matched rows from both the tables.

      I hope This answer will clear your doubt.

      Thanks,
      Avinash Reddy Munnangi.

      Reply

  • madhubabu January 5, 2014

    and working of Natural join and Equi joins in Sql sever.
    Please give me some examples.

    Reply

    • Avinash Reddy Munnangi January 13, 2014

      Already i have given example for Natural join . please take a look at above comment.
      Apart from this Equi join is a special type of join in which we use only equality operator.

      1. Inner join can have equality (=) and other operators (like ,) in the join condition.
      2. Equi join only have equality (=) operator in the join condition.
      3. Equi join can be an Inner join, Left Outer join, Right Outer join.

      Reply

  • vinay January 6, 2014

    Then Whats the difference between join and view and Subquries ?

    Reply

    • Avinash Reddy Munnangi January 13, 2014

      Hi Vinay,
      Thanks for visiting Phpring. Hope u found this Blog and Article as useful.

      Coming to your doubt,
      First i will give definition to each one ,later i will explain the differences between all of them.

      Join:-
      Join is used to fetch the data from multiple tables depends on the Where Condition.

      View:-
      A view is nothing but its just an image of a base table.

      Subquery:-
      A Query with in a Query called as Subquery.

      The above three will play a vital role, we can use above three depends upon our Requirement.

      JOin:-
      suppose we want to Fetch data from two or multiple table we can go for
      Joins.

      Subquery:-
      Suppose any one of the person asked you to find the the person who is having Highest salary . at this time u can go for subquery or u can also find out the same thing by using TOP or u can also achieve by using this by using ranking functions.

      So there are different approaches to achieve one thing. Ranking functions were introduced in Sql server 2008 prior to that we can use subquery to find out these kind of results.
      for example i have one table say (Emp)

      Create table Emp
      (id int,
      name varchar(10),
      sal money)

      — insert few rows in above table
      insert into Emp values(1,’A’,1000),(2,’B’,2000)(3,’C’,3000)

      Now i want to find the person name who is having highest salary.

      Select name from Emp
      Where sal=(select max(sal) from Emp)

      The above query will give you the person who is having Highest salary
      ===================================================================================
      Coming to views
      suppose you want to use above query again and again. Every time u must rewrite the same query to find the person who is having highest salary.
      Queries doesn’t save in the database , but views can save in database.So we can reduce our time by creating view on Emp table with above Query. For this Simply i can wrap the above query in view ,So from next time on words there is need to write the above query again and again, Why because already i wrapped my query in View. So i can simply call my view

      –Follow below example
      create view v_Emp
      select name from Emp
      where sal=(select max(sal)from emp)

      Execute above query then call the view to find out the person who is having highest salary.

      select * from V_Emp

      I hope my explanation will clear a bit to you.
      Thanks,
      Avinash Reddy Munnangi

      Reply

  • Ashok January 9, 2014

    Hi Avinash,
    Bro really nice work. Keep up the good work and Please provide some examples too for better understanding of Joins in SQL Server.
    Thanks,
    Ashok

    Reply

    • Avinash Reddy Munnangi January 13, 2014

      Hi Ashok,
      Thanks for visiting our website. I hope u found this article useful.
      Sure bro, i have a plan to write one more article on joins with example. stay tune for that.

      Thanks,
      Avinash

      Reply

  • Aanchal Kapoor January 14, 2014

    Avinash I love your spirit. You provided explanation to each query so brilliantly. Great work dear.
    God bless you.
    Thanks ;)

    Reply

    • Avinash Reddy Munnangi January 15, 2014

      Hey aanchal ,
      Very glad to hear your kind of words. Thanks a lot for your support, your kind of words will give more motivation to me

      Thanks,
      Avinash

      Reply

  • sanjiv January 20, 2014

    an awsome blog of phpring

    Reply

    • Avinash Reddy Munnangi January 22, 2014

      Hey Sanjiv,
      Welcome to PhpRing. I am very glad to see you on PhpRing.
      Thanks for your support

      Reply

Add a comment