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 (https://blog.sqlauthority.com)
85 Comments. Leave new
So which do we use? :)
Hi Pinal,
When I m using Intersect keyword in SQL2000. It gives the Syntex error. Can u tell me the reason.
hi,
you are using sql server 2000, but the intersect key was interduced in sql server 2005, that why u got error
Nice Explanation Pinal
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
Hi Pinal,
Can you tell me exact difference between Calculated Measures and Calculated Members With Example?
Thanks,
Vijay
Hi pinal,
I only work on SQL SERVER 2005.
can u send your screen shots so that i can check it.
Thanks,
Jeetesh Garg.
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.
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
Jeetesh Garg,
you sure that de compatibility level is 90
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
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
Nice description Pinal,Thanks a lot .–Mukesh Sharma
Good post! Well explained!
It’s really good blog and like to read all articles in this blogs. Good post and well explained.
It’s really good
i have doubt that
INTERSECT and UNION ALL works same are not
Union containd all the rows in both tables
where as intersect contains only the common fields in both tables
Sanjana,
Adding to your point, Union and Intersect both selects only distinct rows.
ohh,i bliendly posted above query
actually which is efficent permormance wise
either INTERSECT or INNER JOIN
Hi Pinal,
Your blog is ultimate.I’m new to SQL Server.
Can you please explain me about joins with exambles?
Good post. It shows the fine difference.
what is joins,and type of join.
differenciate all the joins
SQL Server help file has informations with example
Have a look at it
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?