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

« SQL SERVER – Effect of Order of Join In Query

SQLAuthority News – SQLAuthority Site With New Banner »

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

August 3, 2008 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 76 Comments

76 Responses

on August 3, 2008 at 10:21 am | Reply Eddie

So which do we use? :)
on August 4, 2008 at 11:16 am | Reply Jeetesh Garg

Hi Pinal,
When I m using Intersect keyword in SQL2000. It gives the Syntex error. Can u tell me the reason.
- on July 16, 2010 at 10:26 am | Reply raja
  
  hi,
  you are using sql server 2000, but the intersect key was interduced in sql server 2005, that why u got error
on August 4, 2008 at 11:33 am | Reply pinaldave

Jeetesh Garg,

As title suggests it only works with SQL Server 2005 and later editions.

Regards,
Pinal Dave
on August 4, 2008 at 11:55 am | Reply Kamran Shahid

Nice Explanation Pinal
on August 4, 2008 at 4:01 pm | Reply Jeetesh Garg

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.
- on November 28, 2009 at 5:20 pm | Reply sanjana
  
  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
on August 4, 2008 at 4:35 pm | Reply Vijay sharma

Hi Pinal,

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

Thanks,

Vijay
on August 4, 2008 at 4:54 pm | Reply pinaldave

Jeetesh Garg,

I tried your syntax and it works fine. It may be possible you are connected to SQL Server 2000 instead of SQL Server 2005.

Regards,
Pinal Dave
on August 4, 2008 at 5:16 pm | Reply Jeetesh garg

Hi pinal,

I only work on SQL SERVER 2005.

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

Thanks,
Jeetesh Garg.
on August 4, 2008 at 5:39 pm | Reply Roger Steva

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.
on August 4, 2008 at 5:57 pm | Reply Lekan

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
on August 4, 2008 at 6:50 pm | Reply Gabriel

Jeetesh Garg,

you sure that de compatibility level is 90
on August 4, 2008 at 8:16 pm | Reply Dan

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
on August 5, 2008 at 11:59 am | Reply Jeetesh Garg

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
on August 6, 2008 at 1:23 am | Reply Mukesh

Nice description Pinal,Thanks a lot .–Mukesh Sharma
on August 6, 2008 at 7:01 am | Reply SQL SERVER - Query to Find Column From All Tables of Database Journey to SQL Authority with Pinal Dave

[...] August 6, 2008 by pinaldave One question came up just a day ago while I was writing SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN. [...]
on August 6, 2008 at 2:48 pm | Reply Mike Borozdin

Good post! Well explained!
on August 7, 2008 at 4:09 am | Reply Nanda

It’s really good blog and like to read all articles in this blogs. Good post and well explained.
on August 9, 2008 at 11:46 am | Reply sravan

It’s really good
i have doubt that
INTERSECT and UNION ALL works same are not
- on November 28, 2009 at 5:34 pm | Reply sanjana
  
  Union containd all the rows in both tables
  where as intersect contains only the common fields in both tables
on August 9, 2008 at 12:05 pm | Reply sravan

ohh,i bliendly posted above query

actually which is efficent permormance wise

either INTERSECT or INNER JOIN
on August 17, 2008 at 7:44 pm | Reply karthi

Hi Pinal,

Your blog is ultimate.I’m new to SQL Server.

Can you please explain me about joins with exambles?
on August 18, 2008 at 8:16 pm | Reply Lakshmi

Good post. It shows the fine difference.
on August 20, 2008 at 1:16 pm | Reply lavi

what is joins,and type of join.
differenciate all the joins
- on March 19, 2010 at 1:46 pm | Reply Madhivanan
  
  SQL Server help file has informations with example
  Have a look at it
on September 18, 2008 at 5:20 pm | Reply harkishan

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?
on September 23, 2008 at 4:38 pm | Reply Dinesh

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?
on September 23, 2008 at 4:41 pm | Reply Dinesh

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?
on October 17, 2008 at 9:47 am | Reply Mitesh L Shah

INTERSECT what’s make differece with UNION
on October 17, 2008 at 2:09 pm | Reply LN

Very Nice…
on December 18, 2008 at 1:01 pm | Reply Saubhagya

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
- on March 29, 2010 at 4:58 pm | Reply Madhivanan
  
  Can you post the code that caused the error?
on January 19, 2009 at 3:01 pm | Reply Gaurav Garg

Hi,

Which is better in performance wise.

Thanks
Gaurav Garg
on June 13, 2009 at 12:24 pm | Reply Ken

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…)
on June 15, 2009 at 9:03 am | Reply Imran Mohammed

@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.
on July 24, 2009 at 10:08 pm | Reply SQL SERVER – Get Common Records From Two Tables Without Using Join Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. IN… [...]
on December 17, 2009 at 9:32 pm | Reply Serguei Razykov

Hi Pinal,

Is there any difference in performance between the two?
on December 18, 2009 at 5:44 pm | Reply Pinal Dave

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
- on December 18, 2009 at 11:46 pm | Reply Imran Mohammed
  
  @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.
  - on December 18, 2009 at 11:54 pm | Reply pinaldave
    
    Your understanding is very correct Imran.
on December 30, 2009 at 4:04 pm | Reply Murali

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
on December 30, 2009 at 10:27 pm | Reply Pinal Dave

Hi Murali,

LIMIT is not a keyword in SQL Server. You can use ROW_NUMBER() to perform paging.

Regards,
Pinal Dave
on December 31, 2009 at 11:38 am | Reply Murali

Hi Panel,

Is there a way to get records from a specific position as we have with LIMIT in My SQL.

Thanks & Regards,
Murali krishna
on December 31, 2009 at 12:25 pm | Reply Murali

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
- on March 19, 2010 at 1:50 pm | Reply Madhivanan
  
  You can use TOP operator
  If you want pagination, make use of row_number() function
  See point 4
  http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
on January 17, 2010 at 5:38 pm | Reply asy

Hi,

Very good example…………
on March 4, 2010 at 11:53 am | Reply pramod

good article
on May 10, 2010 at 4:07 pm | Reply andy

very usefull .. thx sir dave
on June 28, 2010 at 3:26 pm | Reply Ganeshkumar

good example…………
on July 23, 2010 at 9:10 pm | Reply harekrushna

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
on July 26, 2010 at 1:17 pm | Reply Ashish

select name , ’000′, id from ‘your table name’
on August 4, 2010 at 6:00 pm | Reply rahul Bhargava

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
on September 1, 2010 at 7:34 pm | Reply Vibha

Hi Pinal,

Please tell me, in comparision between Intersect and Inner join with distinct who takes less time in execution?
on September 27, 2010 at 9:30 pm | Reply Harry Schueler

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’
on November 16, 2010 at 3:25 pm | Reply Pankaj

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

regards
pankaj
- on November 17, 2010 at 5:03 pm | Reply madhivanan
  
  No. It will work for non numeric columns too. Did you get any errors?
on December 20, 2010 at 12:55 pm | Reply Jaishi Ram

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.
on January 19, 2011 at 12:18 pm | Reply Sabahuddin

SELECT * FROM dbo.vwNewBatchTime WHERE SessionStartMonth >=’Jan’ AND SessionStartYear>= ’2010′ AND SessionEndMonth <= 'Dec' and SessionEndYear <='2014' and IsActive = 'true'
on January 25, 2011 at 11:25 am | Reply Smita

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.
- on January 25, 2011 at 1:45 pm | Reply madhivanan
  
  If you use inner join the order of the tables does not matter
  - on January 26, 2011 at 1:51 pm | Reply Smita
    
    thanks Madhivanam…
    In case of outer join will order of tables matter…
    - on January 28, 2011 at 4:09 pm | Reply madhivanan
      
      Yes for outer joins it does matter
on June 16, 2011 at 10:44 am | Reply sourabh chati

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

thanks,

Regards,
Sourabh
on August 1, 2011 at 5:28 pm | Reply Vijayakumar.P

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?
on November 16, 2011 at 9:26 am | Reply Miguel

This time , I feel so good making this comment. Congratulations for all your samples and dedication.
on November 29, 2011 at 11:38 am | Reply rekha

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 .
on March 23, 2012 at 1:13 pm | Reply anudeep

Instead of Intersect We Can Use Union Also
on March 27, 2012 at 9:43 am | Reply Ajith

Really good work keep updating …..!
on March 27, 2012 at 9:47 am | Reply Ajith

hi help me out of this …..how can i use TO_CHAR, TO_DATE function’s in SQL
on August 8, 2012 at 3:59 pm | Reply kaushal dhora

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 :-)
on November 6, 2012 at 4:04 pm | Reply scoebidoe

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
- on February 8, 2013 at 6:19 am | Reply Cliff
  
  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
  - on April 22, 2013 at 1:41 pm | Reply scoebidoe
    
    Cliff,
    Thanks for your reply. I had to make some changes so I could use the join. But now it works.
    THx.
    
    Scoebidoe
on March 3, 2013 at 6:22 pm | Reply Betty Hadar

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
- on March 3, 2013 at 6:32 pm | Reply 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