INTERSECT: Finding Common Rows
What you'll learn: How to use INTERSECT to retrieve only the rows that appear in both of two query results.
The Core Idea
Imagine you have two separate lists of customers—one from your east coast stores and one from your west coast stores. You want to find customers who shop at both locations. That's exactly what INTERSECT does: it returns only the rows that appear in the results of both queries.
Think of it like a Venn diagram where you only keep the overlapping middle section.
How INTERSECT Works
INTERSECT takes two SELECT statements and returns rows that exist in both result sets. The syntax looks like this:
SELECT column1, column2
FROM table_a
WHERE condition_a
INTERSECT
SELECT column1, column2
FROM table_b
WHERE condition_b;
Important requirements:
- Both queries must return the same number of columns
- Corresponding columns must have compatible data types
- Column names from the first query are used in the result
Practical Example
-- Find products sold in both Q1 and Q2
SELECT product_id, product_name
FROM q1_sales
INTERSECT
SELECT product_id, product_name
FROM q2_sales;
This returns only products that appeared in sales during both quarters.
INTERSECT vs Other Approaches
Unlike UNION (which combines all rows) or UNION ALL (which includes duplicates), INTERSECT is highly selective—it's looking for exact matches across all columns in both result sets.
Key Takeaway: INTERSECT finds the overlap between two queries, returning only rows that appear in both result sets—perfect for identifying commonalities across different data sources or time periods.