ML Engineer MasterClass (April) | 6 seats left

INTERSECT & EXCEPT

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

OperatorWhat It DoesRemoves Duplicates?Use Case
INTERSECTReturns rows that exist in both queries✅ YesFinding common records
EXCEPTReturns rows from the first query that don’t exist in the second✅ YesFinding differences between two datasets

Syntax for INTERSECT

SQL
SELECT column1 FROM table1  
INTERSECT  
SELECT column1 FROM table2;
  • Returns only rows that appear in both queries.

Syntax for EXCEPT

SQL
SELECT column1 FROM table1  
EXCEPT  
SELECT 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

SQL
SELECT user_id FROM google_ads_campaigns WHERE ad_type = 'Search'  
INTERSECT  
SELECT 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

SQL
SELECT user_id FROM google_ads_users  
EXCEPT  
SELECT 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

ScenarioBest ChoiceWhy?
Finding users present in both datasetsINTERSECTEnsures only common results are kept
Identifying missing or excluded dataEXCEPTFinds records in one dataset but not in another
Comparing lists of customers, transactions, or activity logsBothDepends 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.

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.

Previous
SELF JOIN
Next
Subquery