Dofactory.com
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

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



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.