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://www.SQLAuthority.com)

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

28 Responses to “SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN”

on August 3, 2008 at 10:21 am Eddie

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

Hi Pinal,
When I m using Intersect keyword in SQL2000. It gives the Syntex error. Can u tell me the reason.
on August 4, 2008 at 11:33 am 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 Kamran Shahid

Nice Explanation Pinal
on August 4, 2008 at 4:01 pm 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 August 4, 2008 at 4:35 pm 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 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 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 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 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 Gabriel

Jeetesh Garg,

you sure that de compatibility level is 90
on August 4, 2008 at 8:16 pm 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 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 Mukesh

Nice description Pinal,Thanks a lot .–Mukesh Sharma
on August 6, 2008 at 7:01 am 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 Mike Borozdin

Good post! Well explained!
on August 7, 2008 at 4:09 am 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 sravan

It’s really good
i have doubt that
INTERSECT and UNION ALL works same are not
on August 9, 2008 at 12:05 pm 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 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 Lakshmi

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

what is joins,and type of join.
differenciate all the joins
on September 18, 2008 at 5:20 pm 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 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 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 Mitesh L Shah

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

Very Nice…
on December 18, 2008 at 1:01 pm 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