SQL INTERSECT

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.

SQL INTERSECT

Example

#

List customers and suppliers that share the same first name.

SELECT FirstName 
  FROM Customer
INTERSECT
SELECT LEFT(ContactName, CHARINDEX(' ', ContactName) - 1) 
  FROM Supplier
Result:  8 records
FirstName
Antonio
Carlos
Giovanni
Marie
Michael
Peter
Sven
Yoshi

Syntax

INTERSECT syntax.

SELECT column-names
  FROM table-name1 
INTERSECT
SELECT column-names
  FROM table-name1 

More Examples

SELECT INTERSECT

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all customers from Portland, USA.
SELECT FirstName, LastName, 
       City, Country 
  FROM Customer    
 WHERE City = 'Portland'
INTERSECT   
SELECT FirstName, LastName, 
       City, Country 
  FROM Customer    
 WHERE Country = 'USA'
Result:  2 records.
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.


You may also like



Guides


vsn 3.1