INTERSECT combines the results of two SELECT queries.
INTERSECT only returns identical rows from the two queries.
The data type and order of the columns in the two queries must match.
List customers and suppliers that share the same first name.
SELECT FirstName
FROM Customer
INTERSECT
SELECT LEFT(ContactName, CHARINDEX(' ', ContactName) - 1)
FROM Supplier
FirstName |
---|
Antonio |
Carlos |
Giovanni |
Marie |
Michael |
Peter |
Sven |
Yoshi |
INTERSECT syntax.
SELECT column-names FROM table-name1 INTERSECT SELECT column-names FROM table-name1
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SELECT FirstName, LastName,
City, Country
FROM Customer
WHERE City = 'Portland'
INTERSECT
SELECT FirstName, LastName,
City, Country
FROM Customer
WHERE Country = 'USA'
FirstName | LastName | City | Country |
---|---|---|---|
Liz | Nixon | Portland | USA |
Fran | Wilson | Portland | USA |
The first query retrieves customers from Portland and the second customers from the USA. The result sets are combined and only identical rows are returned.
Clearly, the following query is more effective:
SELECT FirstName, LastName, City, Country FROM Customer WHERE City = 'Portland' AND Country = 'USA'
The difference is that AND works on columns and INTERSECT works on rows.