Paradime | INTERSECT / EXCEPT (MINUS)

SQL Keywords

INTERSECT / EXCEPT (MINUS)

Feb 23, 2026

min read

Category: Set Operations

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

INTERSECT returns rows that exist in both queries (set intersection). EXCEPT returns rows from the first query that don't exist in the second (set difference). BigQuery uses EXCEPT, while Snowflake and Databricks support both EXCEPT and MINUS (synonyms). Essential for comparing datasets and finding differences.

Syntax

INTERSECT:

SELECT columns FROM table1
INTERSECT
SELECT columns FROM table2;

-- With DISTINCT (default)
SELECT columns FROM table1
INTERSECT DISTINCT
SELECT columns FROM table2;

-- Keep duplicates (Snowflake)
SELECT columns FROM table1
INTERSECT ALL
SELECT columns FROM

SELECT columns FROM table1
INTERSECT
SELECT columns FROM table2;

-- With DISTINCT (default)
SELECT columns FROM table1
INTERSECT DISTINCT
SELECT columns FROM table2;

-- Keep duplicates (Snowflake)
SELECT columns FROM table1
INTERSECT ALL
SELECT columns FROM

SELECT columns FROM table1
INTERSECT
SELECT columns FROM table2;

-- With DISTINCT (default)
SELECT columns FROM table1
INTERSECT DISTINCT
SELECT columns FROM table2;

-- Keep duplicates (Snowflake)
SELECT columns FROM table1
INTERSECT ALL
SELECT columns FROM

EXCEPT/MINUS:

-- BigQuery, Snowflake, Databricks
SELECT columns FROM table1
EXCEPT
SELECT columns FROM table2;

-- Snowflake, Databricks (MINUS is synonym for EXCEPT)
SELECT columns FROM table1
MINUS
SELECT columns FROM

-- BigQuery, Snowflake, Databricks
SELECT columns FROM table1
EXCEPT
SELECT columns FROM table2;

-- Snowflake, Databricks (MINUS is synonym for EXCEPT)
SELECT columns FROM table1
MINUS
SELECT columns FROM

-- BigQuery, Snowflake, Databricks
SELECT columns FROM table1
EXCEPT
SELECT columns FROM table2;

-- Snowflake, Databricks (MINUS is synonym for EXCEPT)
SELECT columns FROM table1
MINUS
SELECT columns FROM

Platform-Specific Notes

Snowflake:

Supports INTERSECT and EXCEPT
MINUS is synonym for EXCEPT
INTERSECT ALL and EXCEPT ALL supported
Removes duplicates by default (DISTINCT)

BigQuery:

Supports INTERSECT and EXCEPT
EXCEPT DISTINCT (default)
EXCEPT ALL for keeping duplicates
No MINUS keyword

Databricks:

Supports INTERSECT and EXCEPT
MINUS is synonym for EXCEPT
Standard Spark SQL behavior
Removes duplicates by default

Example 1: INTERSECT - Find Common Rows

All Platforms:

-- Find customers who are both in 2023 and 2024
SELECT customer_id, customer_name
FROM customers_2023
INTERSECT
SELECT customer_id, customer_name
FROM

-- Find customers who are both in 2023 and 2024
SELECT customer_id, customer_name
FROM customers_2023
INTERSECT
SELECT customer_id, customer_name
FROM

-- Find customers who are both in 2023 and 2024
SELECT customer_id, customer_name
FROM customers_2023
INTERSECT
SELECT customer_id, customer_name
FROM

Sample Data:

customers_2023:

customer_id	customer_name
1	John Smith
2	Maria Garcia
3	Sarah Johnson
4	Bob Wilson

customers_2024:

customer_id	customer_name
2	Maria Garcia
3	Sarah Johnson
5	Alice Brown
6	Carlos Ruiz

Result (customers in both years):

customer_id	customer_name
2	Maria Garcia
3	Sarah Johnson

Example 2: EXCEPT - Find Differences

All Platforms:

-- Find customers who were active in 2023 but not in 2024
SELECT customer_id, customer_name
FROM customers_2023
EXCEPT
SELECT customer_id, customer_name
FROM customers_2024;

-- Find customers who are new in 2024
SELECT customer_id, customer_name
FROM customers_2024
EXCEPT
SELECT customer_id, customer_name
FROM

-- Find customers who were active in 2023 but not in 2024
SELECT customer_id, customer_name
FROM customers_2023
EXCEPT
SELECT customer_id, customer_name
FROM customers_2024;

-- Find customers who are new in 2024
SELECT customer_id, customer_name
FROM customers_2024
EXCEPT
SELECT customer_id, customer_name
FROM

-- Find customers who were active in 2023 but not in 2024
SELECT customer_id, customer_name
FROM customers_2023
EXCEPT
SELECT customer_id, customer_name
FROM customers_2024;

-- Find customers who are new in 2024
SELECT customer_id, customer_name
FROM customers_2024
EXCEPT
SELECT customer_id, customer_name
FROM

Result (in 2023 but not 2024):

customer_id	customer_name
1	John Smith
4	Bob Wilson

Result (new in 2024):

customer_id	customer_name
5	Alice Brown
6	Carlos Ruiz

Example 3: Data Quality - Find Orphaned Records

All Platforms:

-- Find orders without corresponding customers (orphaned orders)
SELECT DISTINCT customer_id
FROM orders
EXCEPT
SELECT customer_id
FROM customers;

-- Find products never ordered
SELECT product_id, product_name
FROM products
EXCEPT
SELECT DISTINCT product_id, product_name
FROM order_items oi
JOIN products p ON

-- Find orders without corresponding customers (orphaned orders)
SELECT DISTINCT customer_id
FROM orders
EXCEPT
SELECT customer_id
FROM customers;

-- Find products never ordered
SELECT product_id, product_name
FROM products
EXCEPT
SELECT DISTINCT product_id, product_name
FROM order_items oi
JOIN products p ON

-- Find orders without corresponding customers (orphaned orders)
SELECT DISTINCT customer_id
FROM orders
EXCEPT
SELECT customer_id
FROM customers;

-- Find products never ordered
SELECT product_id, product_name
FROM products
EXCEPT
SELECT DISTINCT product_id, product_name
FROM order_items oi
JOIN products p ON

Sample Data:

orders:

order_id	customer_id
101	1
102	2
103	99

customers:

customer_id	name
1	John
2	Maria

Result (orphaned customer_ids):

customer_id
99

Example 4: INTERSECT vs INNER JOIN

INTERSECT approach:

SELECT customer_id, customer_name
FROM customers_2023
INTERSECT
SELECT customer_id, customer_name
FROM

SELECT customer_id, customer_name
FROM customers_2023
INTERSECT
SELECT customer_id, customer_name
FROM

SELECT customer_id, customer_name
FROM customers_2023
INTERSECT
SELECT customer_id, customer_name
FROM

Equivalent INNER JOIN:

SELECT DISTINCT 
    c1.customer_id, 
    c1.customer_name
FROM customers_2023 c1
INNER JOIN customers_2024 c2 
    ON c1.customer_id = c2.customer_id 
    AND

SELECT DISTINCT 
    c1.customer_id, 
    c1.customer_name
FROM customers_2023 c1
INNER JOIN customers_2024 c2 
    ON c1.customer_id = c2.customer_id 
    AND

SELECT DISTINCT 
    c1.customer_id, 
    c1.customer_name
FROM customers_2023 c1
INNER JOIN customers_2024 c2 
    ON c1.customer_id = c2.customer_id 
    AND

Both produce same result, but INTERSECT is cleaner for exact row matching

Example 5: EXCEPT vs LEFT JOIN

EXCEPT approach:

SELECT customer_id, customer_name
FROM customers_2023
EXCEPT
SELECT customer_id, customer_name
FROM

SELECT customer_id, customer_name
FROM customers_2023
EXCEPT
SELECT customer_id, customer_name
FROM

SELECT customer_id, customer_name
FROM customers_2023
EXCEPT
SELECT customer_id, customer_name
FROM

Equivalent LEFT JOIN with NULL check:

SELECT DISTINCT
    c1.customer_id,
    c1.customer_name
FROM customers_2023 c1
LEFT JOIN customers_2024 c2
    ON c1.customer_id = c2.customer_id
    AND c1.customer_name = c2.customer_name
WHERE c2.customer_id IS NULL

SELECT DISTINCT
    c1.customer_id,
    c1.customer_name
FROM customers_2023 c1
LEFT JOIN customers_2024 c2
    ON c1.customer_id = c2.customer_id
    AND c1.customer_name = c2.customer_name
WHERE c2.customer_id IS NULL

SELECT DISTINCT
    c1.customer_id,
    c1.customer_name
FROM customers_2023 c1
LEFT JOIN customers_2024 c2
    ON c1.customer_id = c2.customer_id
    AND c1.customer_name = c2.customer_name
WHERE c2.customer_id IS NULL

EXCEPT is simpler for row-level comparison

Example 6: INTERSECT/EXCEPT with Multiple Columns

All Platforms:

-- Find products with exact match (id, name, price)
SELECT product_id, product_name, price
FROM catalog_a
INTERSECT
SELECT product_id, product_name, price
FROM catalog_b;

-- Find price changes (same product, different price)
SELECT product_id, product_name, price
FROM catalog_current
EXCEPT
SELECT product_id, product_name, price
FROM

-- Find products with exact match (id, name, price)
SELECT product_id, product_name, price
FROM catalog_a
INTERSECT
SELECT product_id, product_name, price
FROM catalog_b;

-- Find price changes (same product, different price)
SELECT product_id, product_name, price
FROM catalog_current
EXCEPT
SELECT product_id, product_name, price
FROM

-- Find products with exact match (id, name, price)
SELECT product_id, product_name, price
FROM catalog_a
INTERSECT
SELECT product_id, product_name, price
FROM catalog_b;

-- Find price changes (same product, different price)
SELECT product_id, product_name, price
FROM catalog_current
EXCEPT
SELECT product_id, product_name, price
FROM

Sample Data:

catalog_current:

product_id	product_name	price
1	Laptop	999.99
2	Mouse	29.99
3	Keyboard	89.99

catalog_previous:

product_id	product_name	price
1	Laptop	1099.99
2	Mouse	29.99
3	Keyboard	89.99

Result (products with price changes):

product_id	product_name	price
1	Laptop	999.99

Example 7: ALL vs DISTINCT

Snowflake/BigQuery:

-- INTERSECT DISTINCT (default) - removes duplicates
SELECT category FROM products_warehouse_1
INTERSECT DISTINCT
SELECT category FROM products_warehouse_2;

-- INTERSECT ALL - keeps duplicates
SELECT category FROM products_warehouse_1
INTERSECT ALL
SELECT category FROM

-- INTERSECT DISTINCT (default) - removes duplicates
SELECT category FROM products_warehouse_1
INTERSECT DISTINCT
SELECT category FROM products_warehouse_2;

-- INTERSECT ALL - keeps duplicates
SELECT category FROM products_warehouse_1
INTERSECT ALL
SELECT category FROM

-- INTERSECT DISTINCT (default) - removes duplicates
SELECT category FROM products_warehouse_1
INTERSECT DISTINCT
SELECT category FROM products_warehouse_2;

-- INTERSECT ALL - keeps duplicates
SELECT category FROM products_warehouse_1
INTERSECT ALL
SELECT category FROM

Sample Data:

products_warehouse_1:

category
Electronics
Electronics
Furniture

products_warehouse_2:

category
Electronics
Electronics
Electronics
Furniture

Result (INTERSECT DISTINCT):

category
Electronics
Furniture

Result (INTERSECT ALL):

category
Electronics
Electronics
Furniture

Set Operations Comparison

Operation	Returns	Use Case
UNION	All rows from both (no duplicates)	Combine datasets
UNION ALL	All rows from both (with duplicates)	Combine datasets faster
INTERSECT	Rows in both queries	Find common records
EXCEPT	Rows in first, not in second	Find differences

Set Operations Venn Diagrams

A = {1, 2, 3, 4}
B = {3, 4, 5, 6}

A UNION B = {1, 2, 3, 4, 5, 6}      -- All elements
A INTERSECT B = {3, 4}               -- Common elements
A EXCEPT B = {1, 2}                  -- Only in A
B EXCEPT A = {5, 6}                  -- Only in B

A = {1, 2, 3, 4}
B = {3, 4, 5, 6}

A UNION B = {1, 2, 3, 4, 5, 6}      -- All elements
A INTERSECT B = {3, 4}               -- Common elements
A EXCEPT B = {1, 2}                  -- Only in A
B EXCEPT A = {5, 6}                  -- Only in B

A = {1, 2, 3, 4}
B = {3, 4, 5, 6}

A UNION B = {1, 2, 3, 4, 5, 6}      -- All elements
A INTERSECT B = {3, 4}               -- Common elements
A EXCEPT B = {1, 2}                  -- Only in A
B EXCEPT A = {5, 6}                  -- Only in B

Complex Example: Customer Segmentation

All Platforms:

-- High-value customers who purchased in both Q1 and Q2
WITH q1_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
    AND total > 1000
),
q2_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30'
    AND total > 1000
),
loyal_customers AS (
    SELECT customer_id FROM q1_customers
    INTERSECT
    SELECT customer_id FROM q2_customers
),
churned_customers AS (
    SELECT customer_id FROM q1_customers
    EXCEPT
    SELECT customer_id FROM q2_customers
)
SELECT 
    c.customer_id,
    c.customer_name,
    CASE 
        WHEN c.customer_id IN (SELECT customer_id FROM loyal_customers) THEN 'Loyal'
        WHEN c.customer_id IN (SELECT customer_id FROM churned_customers) THEN 'At Risk'
        ELSE 'New'
    END as segment
FROM

-- High-value customers who purchased in both Q1 and Q2
WITH q1_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
    AND total > 1000
),
q2_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30'
    AND total > 1000
),
loyal_customers AS (
    SELECT customer_id FROM q1_customers
    INTERSECT
    SELECT customer_id FROM q2_customers
),
churned_customers AS (
    SELECT customer_id FROM q1_customers
    EXCEPT
    SELECT customer_id FROM q2_customers
)
SELECT 
    c.customer_id,
    c.customer_name,
    CASE 
        WHEN c.customer_id IN (SELECT customer_id FROM loyal_customers) THEN 'Loyal'
        WHEN c.customer_id IN (SELECT customer_id FROM churned_customers) THEN 'At Risk'
        ELSE 'New'
    END as segment
FROM

-- High-value customers who purchased in both Q1 and Q2
WITH q1_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
    AND total > 1000
),
q2_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30'
    AND total > 1000
),
loyal_customers AS (
    SELECT customer_id FROM q1_customers
    INTERSECT
    SELECT customer_id FROM q2_customers
),
churned_customers AS (
    SELECT customer_id FROM q1_customers
    EXCEPT
    SELECT customer_id FROM q2_customers
)
SELECT 
    c.customer_id,
    c.customer_name,
    CASE 
        WHEN c.customer_id IN (SELECT customer_id FROM loyal_customers) THEN 'Loyal'
        WHEN c.customer_id IN (SELECT customer_id FROM churned_customers) THEN 'At Risk'
        ELSE 'New'
    END as segment
FROM

Performance Considerations

Aspect	Performance	Notes
INTERSECT vs JOIN	Similar	INTERSECT may be clearer
EXCEPT vs LEFT JOIN	Similar	EXCEPT simpler for row comparison
DISTINCT overhead	Slower	Requires sorting/deduplication
ALL variant	Faster	No deduplication needed
Large datasets	Expensive	Consider indexes on compared columns

Common Use Cases

INTERSECT:

Find common customers: Between time periods or segments
Validate data sync: Ensure records exist in multiple systems
Compare catalogs: Products in both inventories
Audit compliance: Records meeting multiple criteria

EXCEPT:

Find missing records: Data in one system but not another
Identify churned customers: Active before but not now
Data quality checks: Find orphaned records
Change detection: New or removed items
A/B test exclusions: Participants in A but not B

Best Practices

Column alignment: Ensure same number and types of columns
Order matters for EXCEPT: A EXCEPT B ≠ B EXCEPT A
Use DISTINCT wisely: Default removes duplicates, use ALL if needed
Index comparison columns: Improves performance
Consider alternatives: JOIN may be clearer for complex logic
Test with small datasets: Verify logic before running on production
Document intent: Explain why set operation is chosen
Handle NULLs: NULL = NULL is true for set operations

NULL Handling

Important: Unlike comparisons, set operations treat NULL as equal to NULL

-- These NULLs are considered equal in set operations
SELECT NULL as value
INTERSECT
SELECT NULL as value;
-- Returns: NULL (one row)

-- But in WHERE clause, NULL != NULL
WHERE NULL = NULL  -- Returns FALSE

-- These NULLs are considered equal in set operations
SELECT NULL as value
INTERSECT
SELECT NULL as value;
-- Returns: NULL (one row)

-- But in WHERE clause, NULL != NULL
WHERE NULL = NULL  -- Returns FALSE

-- These NULLs are considered equal in set operations
SELECT NULL as value
INTERSECT
SELECT NULL as value;
-- Returns: NULL (one row)

-- But in WHERE clause, NULL != NULL
WHERE NULL = NULL  -- Returns FALSE

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources