Lessons › SQL › Deep Dive: Advanced Joins & Subqueries

SQL lesson

INTERSECT and EXCEPT

INTERSECT returns only the rows that appear in both result sets, while EXCEPT returns rows from the first set that have no match in the second.

Example

Comparing Retail Customer Sets with INTERSECT and EXCEPT

SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM returns;

SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM returns

Result:

Step by step

1. After SELECT, list the column names you want, separated by commas.

   SELECT customer_id

2. FROM tells SQL which table to pull the data from.

   SELECT customer_id
   FROM orders

3. INTERSECT keeps only rows that appear in both result sets.

   SELECT customer_id
   FROM orders
   INTERSECT

Practice

Try each one in your head first, then open the answer.

SELECT * FROM customers EXCEPT SELECT * FROM reviews