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 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?
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?
INTERSECT what’s make differece with UNION
Very Nice…
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
Can you post the code that caused the error?
Hi,
Which is better in performance wise.
Thanks
Gaurav Garg
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…)
@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.
Hi Pinal,
Is there any difference in performance between the two?
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.
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
Hi Murali,
LIMIT is not a keyword in SQL Server. You can use ROW_NUMBER() to perform paging.
Regards,
Pinal Dave
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
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
You can use TOP operator
If you want pagination, make use of row_number() function
See point 4
Hi,
Very good example…………
good article
very usefull .. thx sir dave
good example…………
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
select name , ‘000’, id from ‘your table name’