SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN

INTERSECT operator in SQL Server 2005 is used to retrieve the common records from both the left and the right query of the Intersect Operator. INTERSECT operator returns almost same results as INNER JOIN clause many times.

When using INTERSECT operator the number and the order of the columns must be the same in all queries as well data type must be compatible.

Let us see understand how INTERSECT and INNER JOIN are related.We will be using AdventureWorks database to demonstrate our example.

Example 1: Simple Example of INTERSECT

SELECT *
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (1,2,3)
INTERSECT
SELECT
*
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (3,2,5)

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN IntersectWithWhere

Solarwinds

Explanation:
The ResultSet  shows  the  EmployeeID  which  are  common in both the Queries, i.e  2 and 3.

Example 2:  Using simple INTERSECTbetween two tables.

SELECT VendorID,ModifiedDate
FROM Purchasing.VendorContact
INTERSECT
SELECT
VendorID,ModifiedDate
FROM Purchasing.VendorAddress

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN SimpleIntersect

Explanation:

The Resultset shows the records that are common in both the tables. It shows 104 common records between the tables.

Example 3:  Using INNER JOIN.

SELECT va.VendorID,va.ModifiedDate
FROM Purchasing.VendorContact vc
INNER JOIN Purchasing.VendorAddress va ON vc.VendorID = va.VendorID
AND vc.ModifiedDate = va.ModifiedDate

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN IntersectWithoutDistinct

Exlanation :
The resultset displays all the records which are common to both the tables. Additionally in example above INNER JOIN retrieves all the records from the left table and all the records from the right table. Carefully observing we can notice many of the records as duplicate records. When INNER JOIN is used it gives us duplicate records, but that is not in the case of INTERSECT operator.

Example 4:  Using INNER JOIN with Distinct.

SELECT DISTINCT va.VendorID,va.ModifiedDate
FROM Purchasing.VendorContact vc
INNER JOIN Purchasing.VendorAddress va ON vc.VendorID = va.VendorID
AND vc.ModifiedDate = va.ModifiedDate

ResultSet:

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN IntersectWithDistinct

Explanation:
The resultset in this example does not contain any duplicate records as DISTINCT clause is used in SELECT statement. DISTINCT removes the duplicate rows and final result in this example is exactly same as example 2 described above. In this way, INNER JOIN can simulate with INTERSECT when used with DISTINCT.

Summary :

INNER JOIN can simulate with INTERSECT when used with DISTINCT.

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

Solarwinds
,
Previous Post
SQL SERVER – Effect of Order of Join In Query
Next Post
SQLAuthority News – SQLAuthority Site With New Banner

Related Posts

85 Comments. Leave new

  • hi pinal

    let i have table with columns as
    id name
    1 s
    2 c
    3 d
    i have to extract value as soo1,c002,d003
    just say me how can i find it

    regards
    harekrushna

    Reply
  • select name , ‘000’, id from ‘your table name’

    Reply
  • rahul Bhargava
    August 4, 2010 6:00 pm

    Hi Pinal,

    I have face one problem today when my client said me that replace ale in queries by Inner joins….I changed many of then in inner join but i stucked with some of few queries…My question is that is it that all the queries which are using ‘IN’ can be replaced by ‘Inner JOIN’ or not..
    Or any way to find that query can be repalce by innner join or not
    Thanks
    Rahul

    Reply
  • Hi Pinal,

    Please tell me, in comparision between Intersect and Inner join with distinct who takes less time in execution?

    Reply
  • I need to re-write the Statement below so that it will work in an old version of WIN-SQL that does not support INTERSECT statement and am not having any success, can you provide some help? Thanks Harry.

    select hrtemno, hrtdpcd, hrttodt, hrtgpno from hraact where hrttrtp IN (‘p’,’l’)
    intersect select hrtemno, hrtdpcd, hrttodt, hrtgpno from hraact where hrttrtp = ‘m’

    Reply
    • SELECT DISTINCT * FROM
      (
      select hrtemno, hrtdpcd, hrttodt, hrtgpno from hraact where hrttrtp IN (‘p’,’l’)
      ) as A
      INNER JOIN
      (
      select hrtemno, hrtdpcd, hrttodt, hrtgpno from hraact where hrttrtp = ‘m’
      ) as B
      ON
      A.hrtemno = B.hrtemno
      AND
      A.hrtdpcd = B.hrtdpcd
      AND
      A.hrttodt = B.hrttodt
      AND
      A.hrtgpno = B.hrtgpno

      Reply
  • Dear all,
    Is except and intesect work inly in case of numeric field?

    regards
    pankaj

    Reply
  • I have a col. name as ppp type-nchar
    data as
    2-2010
    3-2010
    4-2010
    so on

    1-2011
    2-2011
    I want to copy data from this table between 2-2010 to 3-2010.

    pl. suggest me query. Thanking u very much.

    Reply
  • SELECT * FROM dbo.vwNewBatchTime WHERE SessionStartMonth >=’Jan’ AND SessionStartYear>= ‘2010’ AND SessionEndMonth <= 'Dec' and SessionEndYear <='2014' and IsActive = 'true'

    Reply
  • I have three tables. Table1 has 10 rows, Table2 has 100 rows and Table3 has 1000 rows, so please suggest me that how to join these tables using inner join means which table we should put first and which on second no. and so on.

    Reply
  • sourabh chati
    June 16, 2011 10:44 am

    hi Pinal Sir,
    I want actual example of instead of trigger ,so please give me example with live example.

    thanks,

    Regards,
    Sourabh

    Reply
  • Vijayakumar.P
    August 1, 2011 5:28 pm

    SELECT EmpNo, EmpName,Salery
    FROM EmployeeRecord
    WHERE Salery > 1000
    intersect
    SELECT EmpNo, EmpName,Salery
    FROM EmployeeRecord
    WHERE Salery > 2000
    ORDER BY EmpName;

    select a.empno,a.empname,a.Salery
    from EmployeeRecord a
    inner join
    EmployeeRecord b on
    a.empno=b.empno and b.Salery > 2000
    ORDER BY a.EmpName;

    Is it correct?

    Reply
  • This time , I feel so good making this comment. Congratulations for all your samples and dedication.

    Reply
  • please display the table (with the records) while explaining the queries… because im learning SQL now. so, please shown the table with records. before writing the query .

    Reply
  • Instead of Intersect We Can Use Union Also

    Reply
  • Really good work keep updating …..!

    Reply
  • hi help me out of this …..how can i use TO_CHAR, TO_DATE function’s in SQL

    Reply
  • kaushal dhora
    August 8, 2012 3:59 pm

    Hello Every one its really nice solution but i m in different trouble Explain Below,
    I want the common records from the same table.

    I have two coloumn like bellow

    ID P.ID

    255 352
    255 7424
    255 7482
    254 352
    254 7424
    254 7482
    234 352
    234 7424
    234 7482

    So there are Duplicate IDs (First Coloumn) So I need the commont P.ID

    For this example result should be “352” Only
    And there are not fix records in ID It can be any thing.It comes dynamicly every time

    Please provide the solution Thank you so much in Advance :-)

    Reply
  • Hi Pinal,

    First of all, this blog (and many others of you on this site) are very clear and interesting. I’m also new to mssql and found many answers in your blogs.
    So, congratulations with your blogs.

    My question: can INTERSECT also be used with a double key?

    I.E. I have two tables.
    Table1 exists of 2 columns, each column is a foreign key. The combination of values per record is unique.
    Table 2 has the same lay-out.

    Table 1 (concrete example):
    Column1 Column2
    1 25
    1 2
    2 4
    2 2

    Table 2 (concrete example):
    Column1 Column2
    1 2
    2 2

    I’d like to able to select the records in table1 wich are identical in Table2.

    My not working solution:
    SELECT *
    FROM Table1
    WHERE EXISTS
    (select C1, C2 from Table1 INTERSECT select C1, C2 from Table2)

    Thanks.

    kind regards,
    Scoebidoe

    Reply
    • Scoebidoe,
      Just inner join join the tables – you’ll get the matching rows. Intersect (like Union) should be used as little as possible as the database has to sort the rows in both tables – the join will use the index and should be far more efficient.
      Cliff

      Reply
      • Cliff,
        Thanks for your reply. I had to make some changes so I could use the join. But now it works.
        THx.

        Scoebidoe

  • Hi Panel,
    I need to implement LIMIT command at SQL server.
    I decided to use the INTERSECT command.

    I need to display the fourth highest (balance) from bank table:

    (SELECT TOP 4 (balnace)
    FROM Deposit)
    INTERSECT
    (SELECT TOP 3 (balance)
    FROM Dposit)
    ORDER BY balance DESC

    the results I got are not common in both subquery

    from
    (SELECT TOP 4 (balnace)
    FROM Deposit ORDER BY balance DESC )

    I got (9000,20998,6789,5000)

    & from
    (SELECT TOP 3 (balance)
    FROM Dposi ORDER BY balance DESC)
    I got ((9000,20998,6789)

    but from
    (SELECT TOP 4 (balnace)
    FROM Deposit)
    INTERSECT
    (SELECT TOP 3 (balance)
    FROM Dposit)
    ORDER BY balance DESC

    I got (9000,20998,5000)

    Why?

    Thanks.

    kind regards,
    Betty Hadar

    Reply
    • Sorry, the command that equivalent to LIMIT is EXCEPT.

      But even in this case I do not get logical result.
      with EXCEPT command I got the value (6789).

      Thanks,
      Betty

      Reply
  • Hi Pinal,
    Thanks for this post. I have a question though. I understand the difference between INTERSECT and INNER JOIN but I dont understand the usablity difference for the two. When should INTERSECT be used and when will it be wise to use INNER JOIN.

    Please shed some light on this.

    Thanks,
    Rohit

    Reply

Leave a Reply

Menu