SQL SERVER – 2005 Explanation and Example – SELF JOIN

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.

Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer the sample database for table structure.

USE AdventureWorks;
GO
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID = pv2.VendorID
ORDER BY pv1.ProductID

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL – Example courtesy

About these ads

50 thoughts on “SQL SERVER – 2005 Explanation and Example – SELF JOIN

  1. didnt understand please give an easier example i am just learning SQL please forward me 1 if possible all help will be appriciated

  2. With your example, I have the following problem:

    List pairs of vendor names who have the same ProductID. But if I use Self Join like the below script, the result will be redundant (2 pairs are the same except their orders). What should I do to get the exact pairs I need? Thank you.

    USE AdventureWorks;
    GO
    SELECT DISTINCT pv1.VendorName, pv2.VendorName
    FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID = pv2.ProductID
    AND pv1.VendorID != pv2.VendorID

    (The result will be:
    Vendor1 Vendor2
    Vendor2 Vendor1
    Vendor3 Vendor4
    Vendor4 Vendor3

    )

  3. I think that the example needs to be corrected in the AND part of the code. ProductID needs to be the same, but the VendorID must be different to get products supplied by multiple vendors!

    AND pv1.VendorID pv2.VendorID

  4. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 2 Journey to SQL Authority with Pinal Dave

  5. I think above example needs to be modified to get the correct result,
    i have used cte(common table expression) for that:

    with vendorcount(productidcount) as
    (
    select productid from purchasing.productvendor
    group by productid having count(vendorid)>1
    )
    select productidcount,vendorid from vendorcount inner join Purchasing.ProductVendor
    on productidcount=productid

    any suggestions would be appreciated

  6. I think the query should be modified as below:

    SELECT DISTINCT pv1.ProductID, pv1.VendorID
    FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID = pv2.ProductID
    AND pv1.VendorID pv2.VendorID
    ORDER BY pv1.ProductID

  7. Using less than in the join will eliminate the duplicate results:

    USE AdventureWorks;
    GO
    SELECT DISTINCT pv1.ProductID, pv1.VendorID
    FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID < pv2.ProductID
    AND pv1.VendorID < pv2.VendorID
    ORDER BY pv1.ProductID1

    Image the results set as a square grid where every row from pv1 is join with every row from pv2. Now, using a less than in the join will only join row from pv2 which have an ID greater than the ID in pv1, eliminating duplicates:

           pv2
    
         0 0 0 0 0 0
         x 0 0 0 0 0
    p   x x 0 0 0 0
    v   x x x 0 0 0
    1   x x x x 0 0
         x x x x x 0
    
  8. I have one observation regarding the example you gave:

    USE AdventureWorks;
    GO
    SELECT DISTINCT pv1.ProductID, pv1.VendorID
    FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID = pv2.ProductID
    AND pv1.VendorID = pv2.VendorID
    ORDER BY pv1.ProductID

    You said it will “find products that are supplied by more than one vendor”. This query returns all products from the table, not just the ones supplied by 2 or more vendors

    Maybe you meant something more like this ( instead of =)

    USE AdventureWorks;
    GO
    SELECT DISTINCT pv1.ProductID, pv1.VendorID
    FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID = pv2.ProductID
    AND pv1.VendorID pv2.VendorID
    ORDER BY pv1.ProductID

    or even

    USE AdventureWorks;
    GO
    SELECT DISTINCT pv1.ProductID
    FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID = pv2.ProductID
    AND pv1.VendorID pv2.VendorID
    ORDER BY pv1.ProductID

  9. When we select multiple columns with Distinct as Clause
    it does not gives distinct values Please see below Eg.

    Employee is the Table with values

    Santosh Patil 19000
    Shilpa Shinde 18000
    Ajit 20000
    Pramod 15000
    Pravin 21000
    Prakash 15000
    Ajit 35000

    Query is

    select distinct ename,salary from employee

    O/P show is

    Ajit 20000
    Ajit 35000
    Prakash 15000
    Pramod 15000
    Pravin 21000
    Santosh Patil 19000
    Shilpa Shinde 18000

    Here there is Duplication of Name Ajit.

    Plz Help me in sorting out this Problem.

    Awaiting for Your Favourable reply.

    Regards.

  10. I’d like to make a qury to give me back the confliction beween starttime and endtime and the period between them,

    i want to insert a new field to Lecture table so that it must be not conflicted with the time of other lecture for the same teacher .

    thank for help.

  11. hi Pinal Dave,

    i have a doubt in self join,

    i have written query like this.

    SELECT DISTINCT R.NDC,R1.NDC,
    R.NPT_PRICEX as LastPrice,
    R1.NPT_PRICEX as NewPrice,
    from RNP2_NDC_PRICE R
    join
    RNP2_NDC_PRICE R1
    on R1.NDC=R.NDC
    and R1.IsActive=’TRUE’
    and R.IsActive=’FALSE’

    iam getting duplicate records through this query,because
    the table having more than one non active records with same NDC.

    please help me to avoid duplicate records.

  12. Result iam getting like this.

    00002323560 00002323560 189.00000 213.88000
    00002323560 00002323560 200.34000 213.88000
    00002323701 00002323701 374.39999 399.70001
    00002323704 00002323704 3533.33008 3997.00000
    00002323704 00002323704 3744.00000 3997.00000
    00002323730 00002323730 106.00000 119.91000
    00002323730 00002323730 112.32000 119.91000
    00002323733 00002323733 353.32999 399.70001
    00002323733 00002323733 374.39999 399.70001
    00002323734 00002323734 112.32000 119.91000

  13. Pingback: SQL SERVER – The Self Join – Inner Join and Outer Join Journey to SQL Authority with Pinal Dave

  14. i think u dont need self join to answer the query
    “find products that are supplied by more than one vendor”

    here is my solution

    SELECT ProductID, count(VendorID) as “Num OF Vendors”
    FROM Purchasing.ProductVendor pv1
    GROUP BY ProductID
    HAVING count(VendorID) > 1
    ORDER BY ProductID

    thank u in advance

  15. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31 Journey to SQLAuthority

  16. i want to display data on text box and few of label from two tables emp_master and formula besis of selection of emp_no in combo box, i write down follwing code..bt no data show on textbox and label. any body help …….plz…….

    Dim con As New SqlConnection(“Data Source=MICRODAT-39F82C;Initial Catalog=master;Integrated Security=SSPI”)

    Dim Da As New SqlDataAdapter
    Dim Da1 As New SqlDataAdapter
    Dim Ds As New DataSet
    Dim Ds1 As New DataSet
    Dim Dt As New DataTable
    Dim Dt1 As New DataTable
    con.Open()

    Try

    Da = New SqlDataAdapter(“Select f.Basic_sal,f.d_a,f.p_p,f.c_a,f.h_r_a,f.medical,f.o_a,f.o_t,f.p_f,f.o_p_f,f.loan,f.ad_vance,f.s_s_s,f.elec_charge,f.p_tax,f.i_tax,f.e_s_i,f.gross_sal,f.net_sal,f.date,e.emp_name,e.d_o_b,e.sex,e.d_o_j,e.gard_name,e.present_add from farmula f INNER JOIN emp_master e ON f.emp_no=’” & ComboBox1.SelectedValue.ToString & “‘where e.emp_no=f.emp_no “, con)

    Da.Fill(Ds)
    Catch ex As Exception
    MessageBox.Show(ex.Message)

    TxtBx_EBsic.Text = Ds.Tables(0).Rows(0).Item(“f.Basic_sal”).ToString
    TxtBx_DA.Text = Ds.Tables(0).Rows(0).Item(“f.d_a”).ToString
    TxtBx_PP.Text = Ds.Tables(0).Rows(0).Item(“f.p_p”).ToString
    TxtBx_HRA.Text = Ds.Tables(0).Rows(0).Item(“f.h_r_a”).ToString
    TxtBx_Medi.Text = Ds.Tables(0).Rows(0).Item(“f.medical”).ToString
    TxtBx_CA.Text = Ds.Tables(0).Rows(0).Item(“f.c_a”).ToString
    TxtBx_OA.Text = Ds.Tables(0).Rows(0).Item(“f.o_a”).ToString
    TxtBx_OT.Text = Ds.Tables(0).Rows(0).Item(“f.o_t”).ToString
    TxtBx_Grssal.Text = Ds.Tables(0).Rows(0).Item(“f.gross_sal”).ToString
    TxtBx_pf.Text = Ds.Tables(0).Rows(0).Item(“f.p_f”).ToString
    TxtBx_Opf.Text = Ds.Tables(0).Rows(0).Item(“f.o_p_f”).ToString
    TxtBx_ptax.Text = Ds.Tables(0).Rows(0).Item(“f.p_tax”).ToString
    TxtBx_esi.Text = Ds.Tables(0).Rows(0).Item(“f.e_s_i”).ToString
    TxtBx_BLne.Text = Ds.Tables(0).Rows(0).Item(“f.loan”).ToString
    TxtBx_adv.Text = Ds.Tables(0).Rows(0).Item(“f.ad_vance”).ToString
    TxtBx_elec.Text = Ds.Tables(0).Rows(0).Item(“f.elec_charge”).ToString
    TxtBx_sss.Text = Ds.Tables(0).Rows(0).Item(“f.s_s_s”).ToString
    TxtBx_IT.Text = Ds.Tables(0).Rows(0).Item(“f.i_tax”).ToString
    TxtBx_netpay.Text = Ds.Tables(0).Rows(0).Item(“f.net_sal”).ToString
    txtbx_dt.Text = Ds.Tables(0).Rows(0).Item(“f.date”).ToString
    Label56.Text = Ds.Tables(0).Rows(0).Item(“e.emp_name”).ToString
    Label9.Text = Ds.Tables(0).Rows(0).Item(“e.sex”).ToString
    Label13.Text = Ds.Tables(0).Rows(0).Item(“e.d_o_b”).ToString
    Label57.Text = Ds.Tables(0).Rows(0).Item(“e.present_add”).ToString
    Label1.Text = Ds.Tables(0).Rows(0).Item(“e.gard_name”).ToString
    Label15.Text = Ds.Tables(0).Rows(0).Item(“e.d_o_j”).ToString

    End Try
    con.Close()

  17. A self join is an example of an inner join, the code example even says inner join. I would not classify this as a ‘type of join’ in its own right.

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s