SQL SERVER – The Self Join – Inner Join and Outer Join

Self Join has always been an note-worthy case. It is interesting to ask questions on self join in a room full of developers. I often ask – if there are three kind of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. In fact, it can be classified under any type of join. I have previously written about this in my interview questions and answers series. I have also mentioned this subject when I explained the joins in detail over SQL SERVER – Introduction to JOINs – Basic of JOINs.

When I mention that Self Join can be the outer join, I often get a request for an example for the same. I have created example using AdventureWorks Database of Self Join earlier, but that was meant for inner join as well. Let us create a new example today, where we will see how Self Join can be implemented as an Inner Join as well as Outer Join.

Let us first create the same table for an employee. One of the columns in the same table contains the ID of manger, who is also an employee for the same company. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need use self join.

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

SQL SERVER - The Self Join - Inner Join and Outer Join  selfjoini1

We will now use inner join to find the employees and their managers’ details.

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

SQL SERVER - The Self Join - Inner Join and Outer Join  selfjoini2

From the result set, we can see that all the employees who have a manager are visible. However we are unable to find out the top manager of the company as he is not visible in our resultset. The reason for the same is that due to inner join, his name is filtered out. Inner join does not bring any result which does not have manager id. Let us convert Inner Join to Outer Join and then see the resultset.

-- Outer Join
SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

SQL SERVER - The Self Join - Inner Join and Outer Join  selfjoini3

Once we convert Inner Join to Outer Join, we can see the Top Manager as well. Here we have seen how Self Join can behave as an inner join as well as an outer join.

As I said earlier, many of you know these details, but there are many who are still confused about this concept. I hope that this concept is clear from this post.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Upper Case Shortcut SQL Server Management Studio
Next Post
SQLAuthority News – I am a MVP and I Love SQL Server

Related Posts

50 Comments. Leave new

  • Sultan Ibrahim
    July 24, 2012 10:46 pm

    Very good explanation with examples in a nutshell.

    Reply
  • Ketan Barasara
    August 21, 2012 10:45 am

    TY

    Reply
  • Very Good Concept Sir.

    Reply
  • shiva
    good examples and concept

    Reply
  • Lokesh ghildiyal
    December 18, 2012 2:44 pm

    You dont need to use IsNULL when ur using outerjoin
    select a1.name as employee, a2.name as ManagerName from employee as a1
    left join employee as a2
    on
    a1.managerid = a2.employeeid

    Reply
  • select e1.EmployeeID,e1.name as employeename,e2.ManagerID,isnull(e2.Name,’-HEAD-‘) as managername
    from Employee e1 LEFT join Employee e2
    on e1.ManagerID=e2.EmployeeID

    Reply
  • My query is as below :

    select b.Booking_id,booked_by,Datetime,FP_no,Name,co_name,
    Booking_type,occupation,Nationality,Address,email_id,
    Pan_no,sitting_arrang,state,city,pin_code,Booking_from,Booking_to,
    Venue_type,Type_of_function,Food_Pickup_hour,Food_Pickup_min,Min_people,
    Cost_per_plate,Total,Av_item_total,Grant_amount,Advance,Due,Payment_mode,card_type,
    Card_no,av.av_item,av.av_rate, food.Food_menu
    from Booking_details b, av_details av, booking_food_details food
    where b.Booking_id=@Booking_id
    and b.Booking_id=av.Booking_id
    and b.Booking_id=food.Booking_id

    Now the problem here is that av_details and booking_food_details have different number of rows,Suppose
    booking_food_details has a,b,c and, av_details has d,e,f,g,h

    so when I run it with data it shows me 5 lines of entry and av_details rows repeats itself 5 times, like :

    booking_food_details av_details
    d a
    e b
    f c
    g a
    h b
    Rather it should display:

    booking_food_details av_details
    d a
    e b
    f c
    g
    h

    Reply
  • excellent nice explination

    Reply
  • Nice explanation. Thx

    Reply
  • Clear article with great explanation. It made it easy to understand the differents way to join some table. Thanks.

    Reply
  • hello all,
    I have data in following format

    id productID price1 price2 price3
    1 100 4.0 null null
    2 100 NULL 8 null
    3 100 null null 12
    4 108 18 null NULL
    5 108 null 28 null
    6 108 null null 20

    However requirement of data is in following data..

    id productID price1 price2 price3 IDLIst
    1 100 4.0 8 12 1,2,3
    4 108 18 28 20 4,5,6

    Thanks In Advance

    Reply
  • Rohit Rathod
    May 19, 2013 12:14 pm

    This is really good and it has resolved my issue in a second.

    Reply
  • we want self join but there u didn’t explain about that with example

    Reply
  • TY

    Reply
  • There are specific breeds lower susceptible to chocolate however never give your dog chocolate
    by choice. Often described as “a large dog in the body of a small dog,” it iis simipar to the Norwegian Buhund and related to modern Welshi Corgis as well
    aas Shetland Sheepdogs. Territorial aggression is common in
    canines.

    Reply
  • how to find employee who is not a manager ??

    Reply
  • Select A.ID, A.FirstColumn, A.SecondColumn,SUM(B.FirstColumn) + SUM(B.SecondColumn) As Total
    From Employee A, Employee B
    Where B.ID <= A.ID
    Group By A.ID, A.FirstColumn, A.SecondColumn

    Reply
  • sir its very helpful, sir here i have the situation Composeletterid linkedid
    1 2
    2 4
    4 7
    7 10
    10 12
    using self join

    Reply
  • NEED HELP

    Suppose I have 2 Tables

    1. Transactions(Transactionid(pk),Transactiontypeid,Relatedtransactionid(which is also a transaction id from Transactions table))

    2. Transactiontypes (Transactiontypeid,Transactiontype_name)

    I want the result as follow:

    (Transactionid,Transaction name,Related TransactionType_name)

    PLZ suggest the Query.

    Reply
  • Nice expaination

    Reply

Leave a Reply

Menu