SQL INTERSECT & EXCEPT

Lesson Objectives

By the end of this lesson, you will:

Learn how to use INTERSECT to find common records between queries
Understand how EXCEPT removes values from one query that exist in another
Write SQL queries to analyze Google Ads campaign data using set operations

🎬 Scenario: Analyzing Advertisers and Their Campaigns

Your manager at Google Ads, Jordan, wants to compare different sets of advertisers and campaigns.

“Can you find advertisers who have run both Search and Display ads? Also, can we check which advertisers signed up but never launched a campaign?”

Your task is to use INTERSECT to find common advertisers and EXCEPT to identify advertisers without campaigns.

1. Understanding INTERSECT & EXCEPT

Operator	What It Does	Removes Duplicates?	Use Case
INTERSECT	Returns rows that exist in both queries	✅ Yes	Finding common records
EXCEPT	Returns rows from the first query that don’t exist in the second	✅ Yes	Finding differences between two datasets

Syntax for INTERSECT

1SELECT column1 FROM table1  
2INTERSECT  
3SELECT column1 FROM table2;

Returns only rows that appear in both queries.

Syntax for EXCEPT

1SELECT column1 FROM table1  
2EXCEPT  
3SELECT column1 FROM table2;

Returns only rows from the first query that are NOT in the second query.

2. Using INTERSECT to Find Common Advertisers

Jordan wants to find advertisers who have run both Search and Display campaigns.

Example Query: Finding Advertisers Running Both Search and Display Ads

1SELECT user_id FROM google_ads_campaigns WHERE ad_type = 'Search'  
2INTERSECT  
3SELECT user_id FROM google_ads_campaigns WHERE ad_type = 'Display';

user_id
2
4

What’s Happening?

The first query retrieves advertisers with Search ads.
The second query retrieves advertisers with Display ads.
INTERSECT returns only advertisers who appear in both queries.

3. Using EXCEPT to Find Advertisers Without Campaigns

Jordan also wants to see which advertisers have signed up but never launched a campaign.

Example Query: Finding Advertisers Without Campaigns

1SELECT user_id FROM google_ads_users  
2EXCEPT  
3SELECT DISTINCT user_id FROM google_ads_campaigns;

user_id
3

What’s Happening?

The first query retrieves all registered advertisers.
The second query retrieves advertisers who have launched at least one campaign.
EXCEPT removes advertisers who have campaigns, leaving only those without campaigns.

4. INTERSECT vs. EXCEPT Performance

Scenario	Best Choice	Why?
Finding users present in both datasets	`INTERSECT`	Ensures only common results are kept
Identifying missing or excluded data	`EXCEPT`	Finds records in one dataset but not in another
Comparing lists of customers, transactions, or activity logs	Both	Depends on whether you want common or missing records

Tip:

Since INTERSECT and EXCEPT remove duplicates, they can be slower on large datasets.
Use UNION ALL or filtering (WHERE NOT IN) if performance is a concern.

✍️ SQL Exercises

Exercise 1: Finding Advertisers Running Both Search and Display Ads

Jordan wants to list advertisers who have run both Search and Display campaigns.

Filter condition:

Retrieve user IDs of advertisers who have both Search and Display ads.
Ensure no duplicates appear.
Sort by user_id in ascending order.

user_id
2
4

Write an SQL query to return the requested data.

Upgrade to Pro

Exercise 2: Finding Advertisers Without Campaigns

Jordan also wants to see which advertisers signed up but never launched a campaign.

Filter condition:

Retrieve user IDs of advertisers who haven’t launched a campaign.

user_id
3

Write an SQL query to return the requested data.

Upgrade to Pro