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
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
Hi Pinal,
Please tell me, in comparision between Intersect and Inner join with distinct who takes less time in execution?
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’
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
Dear all,
Is except and intesect work inly in case of numeric field?
regards
pankaj
No. It will work for non numeric columns too. Did you get any errors?
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.
SELECT * FROM dbo.vwNewBatchTime WHERE SessionStartMonth >=’Jan’ AND SessionStartYear>= ‘2010’ AND SessionEndMonth <= 'Dec' and SessionEndYear <='2014' and IsActive = 'true'
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.
If you use inner join the order of the tables does not matter
thanks Madhivanam…
In case of outer join will order of tables matter…
Yes for outer joins it does matter
hi Pinal Sir,
I want actual example of instead of trigger ,so please give me example with live example.
thanks,
Regards,
Sourabh
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?
This time , I feel so good making this comment. Congratulations for all your samples and dedication.
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 .
Instead of Intersect We Can Use Union Also
Really good work keep updating …..!
hi help me out of this …..how can i use TO_CHAR, TO_DATE function’s in SQL
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 :-)
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
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
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
@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’.
Use “EXCEPT”
Hi Pinal, you have missed a key point which is the main difference between join and except .The JOIN treats two NULLS as two different values whereas the interesect treats two NULLs as a same value.