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:

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:



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:


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:

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 (http://blog.SQLAuthority.com)

About these ads

81 thoughts on “SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN

  1. Hi Pinal,

    I face a problem with INTERSECT/EXCEPT Kewords on SQL SERVER 2005.

    I create three tables

    create table TableA(col1 int)
    create table TableB(col1 int)
    create table TableC(col1 int)

    and after insertion data, use followinf queries to select data

    SELECT * FROM TableA EXCEPT SELECT * FROM TableB

    SELECT * FROM TableA INTERSECT SELECT * FROM TableB

    but it gives error which shown below:

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘EXCEPT’.
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘INTERSECT’.

    Resolve it.

    • Bankdet is table1
      ClientDet is table2
      InvoiceItems is Table3

      select BankDet.BankName, InvoiceItems.InvoiceNumber,ClientDet.ClientName,ClientDet.AddressLine1,ClientDet.AddressLine2,ClientDet.AddressLine3 from BankDet Inner Join (ClientDet Inner Join InvoiceItems on ClientDet.id = InvoiceItems.InvoiceID)on BankDet .id =InvoiceItems.InvoiceId

  2. Hi Pinal,

    Can you tell me exact difference between Calculated Measures and Calculated Members With Example?

    Thanks,

    Vijay

  3. Hi pinal,

    I only work on SQL SERVER 2005.

    can u send your screen shots so that i can check it.

    Thanks,
    Jeetesh Garg.

  4. Jeetesh,

    Your script works fine for me as well. Pinal has already posted screenshots for this example in post above.

    I think you are not using SQL Server 2k5.

  5. Hi Pinal,

    Thanks for the post, now I think I prefer to Intersect/Except command, i feel comfortable with them than inner join.

    Thanks,
    Lekan

  6. Jeetesh garg-

    Check your compatablilty level. If you are running SQL Server 2005 in level 70 or 80, you could have similar behavior to what you are describing.

    -Dan

  7. Hi Pinal/Dan/gabreil,

    I have checked it and the problem came due to not fully installed SQL SERVER 2005.

    So Thanks for your suggestion.

    Thanks,

    Jeetesh garg

  8. Pingback: SQL SERVER - Query to Find Column From All Tables of Database Journey to SQL Authority with Pinal Dave

  9. this is working fine. but if I put

    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

    thn it fetches all the records.

    please let me know what to do for this?

  10. So when an inner join can be applied only on a primary key column / Foreign column , the Intersect can pull out the exact matching record based on the all the columns of the result set.Is that right?

  11. Hi Pinal,
    I have a huge set of data that needs to be ported to another table. i have to port a single record at a time. Iam currently using a cursor to do the same. Can we go for OLE DB Command flow in SSIS? will it have advantage over cursor.
    Could you please clarify this?

  12. Hi Pinal,

    I am facing an issue with MS SQL 2000 inner joins. Though the systax is runnig fine in 2005 i.e, using “*” for outer joins. But same syntax raising error on 2000

    Kindly suggest

    Regards
    Saubhagya

  13. If I have two tables I wish to JOIN on the Primary Key Which are both each an Identity Column – the Key is unique so no duplicates exist this would suggest that intersect is only useful if you wsih to work with non unique columns , am I missing something ??

    (oops my email address wa wrong in the first post…)

  14. @Ken,

    INTERSECT Keyword displays all unique records between two tables for given filter criteria (if any provided).

    If you try to INTERSECT on a column of two different tables that has duplicates, you will see only unique records that exists in both tables.

    INTERSECT Will not display duplicates.

    ~ IM.

  15. Pingback: SQL SERVER – Get Common Records From Two Tables Without Using Join Journey to SQL Authority with Pinal Dave

  16. Intersect match two recordset by matching values of all columns while inner join match only the columns that are specifed in ON clause.
    There is no question to compare the performance of these two functionalities as they are for different purpose and should not be use as the alternate of each other.

    Regards,
    Pinal Dave

    • @Pinal.

      Please correct me if I have misunderstood your above statement.

      [quote]
      Intersect match two recordset by matching values of all columns while inner join match only the columns that are specifed in ON clause.
      [/quote]

      But INTERSECT only matches only those columns mentioned in select clause, just like INNER JOIN when used with AND clause.

      Example:

      — Usage of INTERSECT

      Select Cola , Colb
      from Table1
      INTERSECT
      Select Cola , Colb
      from Table2

      GO

      — Usage of JOIN with AND Clause.
      Select A.Cola , B.Colb
      from Table1 A
      join Table B ON A.cola = B.Cola
      AND A.Colb = B.Colb

      Thanks,
      Imran.

        • What about the performance differences when a join isn’t necessarily involved. Take as an (overly simplified) example:

          SELECT key
          FROM table
          WHERE condition A AND conditionB

          versus

          SELECT key
          FROM table
          WHERE conditionA

          INTERSECT

          SELECT key
          FROM table
          WHERE conditionB

  17. Hi Pinal,

    Is the LIMIT Keyword will work in Sql server 2005 & 2008? I think It will not work for 2005. If the LIMIT keywords works in 2005 or 2008, Can you me a suggestions with queries?

    Thanks & Regards,
    Murali Krishna

  18. Hi Pinal,

    Is there any query that can return selected rows from the result set in sql server like LIMIT in My SQL.

    Thanks & Regards,
    Murali

  19. 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

  20. 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

  21. 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’

  22. 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.

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

  24. 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.

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

    thanks,

    Regards,
    Sourabh

  26. 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?

  27. 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 .

  28. 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 :-)

  29. 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

    • 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

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

        Scoebidoe

  30. 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

    • 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

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

  32. 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

  33. @pinal
    Hi sir,
    i have similar type of problem am unable solve.the problem is tblA and tblB having ID column in similar and some other columns. In that i want to select the values which were not ‘Intersect or Matched’.

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