PRO SQL Modules
SQL Basics1h 20m • 4 lessons
Multiple Tables1h 40m • 5 lessons
Query Restructuring40m • 2 lessons
Data Transformation1h 40m • 5 lessons
Analytical SQL2h • 6 lessons
FREE
FULL OUTER JOIN
Instructor
Dan Lee
Lesson Objectives
By the end of this lesson, you will:
- Learn how to use FULL OUTER JOIN to combine data from two tables
- Understand how to handle missing values from unmatched records
- Write SQL queries to analyze Google Ads campaign data, ensuring no advertisers or campaigns are excluded
🎬 Scenario: Getting a Complete View of Advertisers and Campaigns
Your manager at Google Ads, Jordan, wants to see all advertisers and campaigns, even if some advertisers haven’t launched a campaign or some campaigns aren’t linked to active users.
Can you generate a report that includes all advertisers and all campaigns, even if they aren’t linked? I don’t want to miss any data.
Your task is to use FULL OUTER JOIN to include all advertisers and all campaigns, even if they don’t match.
Table: google_ads_campaigns
| campaign_id | user_id | campaign_name | ad_type | start_date | end_date | budget | impressions | clicks | conversions |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | “Growth Hackers Special” | “Search” | “2024-01-10T00:00:00” | “2024-02-10T00:00:00” | 5000 | 500000 | 12000 | 800 |
| 2 | 2 | “BigTech Awareness” | “Display” | “2024-01-05T00:00:00” | “2024-02-20T00:00:00” | 20000 | 2000000 | 50000 | 3000 |
| 3 | 3 | “Ecom Holiday Sales” | “Shopping” | “2023-11-15T00:00:00” | “2024-01-15T00:00:00” | 10000 | 750000 | 22000 | 1500 |
| 4 | 4 | “Fashion Hub Summer” | “Video” | “2024-02-01T00:00:00” | “2024-03-01T00:00:00” | 15000 | 900000 | 18000 | 1200 |
| 5 | 5 | “HealthyLife Brand Push” | “Search” | “2024-01-20T00:00:00” | “2024-03-10T00:00:00” | 8000 | 600000 | 15000 | 900 |
Table: google_ads_users
| user_id | name | company | country | signup_date | ad_spend_total | is_active | |
|---|---|---|---|---|---|---|---|
| 1 | “Alice Johnson” | “alice@startupx.com” | “StartupX” | “USA” | “2021-06-15T00:00:00” | 50000 | true |
| 2 | “Bob Smith” | “bob@bigtech.com” | “BigTech Corp” | “Canada” | “2020-03-20T00:00:00” | 200000 | true |
| 3 | “Charlie Lee” | “charlie@ecomstore.com” | “EcomStore” | “UK” | “2019-11-01T00:00:00” | 75000 | false |
| 4 | “David Kim” | “david@fashionhub.com” | “FashionHub” | “Germany” | “2022-05-30T00:00:00” | 120000 | true |
| 5 | “Emma Garcia” | “emma@healthylife.com” | “HealthyLife” | “Mexico” | “2023-02-12T00:00:00” | 30000 | true |
1. Understanding FULL OUTER JOIN
A FULL OUTER JOIN returns all records from both tables, including:
- Matching records where the user_id exists in both tables.
- Unmatched advertisers without campaigns (campaign columns will be NULL).
- Unmatched campaigns without advertisers (user columns will be NULL).
| Join Type | Keeps All Rows From | Matches Where Possible | Unmatched Rows Appear As |
|---|---|---|---|
| FULL OUTER JOIN | Both tables | Yes | NULL for missing matches |
Syntax
1SELECT table1.column1, table2.column2
2FROM table1
3FULL OUTER JOIN table2
4ON table1.common_key = table2.common_key;- Ensures no data is lost, even if there are no matches.
2. Using FULL OUTER JOIN to Include All Advertisers & Campaigns
We want to list all advertisers and campaigns, even if they don’t have matches.
Example Query: Combining All Advertisers and Campaigns
1SELECT u.user_id, u.name, u.company, c.campaign_name, c.budget
2FROM google_ads_users u
3FULL OUTER JOIN google_ads_campaigns c
4ON u.user_id = c.user_id;| user_id | name | company | campaign_name | budget |
|---|---|---|---|---|
| 1 | “Alice Johnson” | “StartupX” | “Growth Hackers Special” | 5000 |
| 2 | “Bob Smith” | “BigTech Corp” | “BigTech Awareness” | 20000 |
| 3 | “Charlie Lee” | “EcomStore” | “Ecom Holiday Sales” | 10000 |
| 4 | “David Kim” | “FashionHub” | “Fashion Hub Summer” | 15000 |
| 5 | “Emma Garcia” | “HealthyLife” | “HealthyLife Brand Push” | 8000 |
What’s Happening?
- FULL OUTER JOIN keeps all users and campaigns, even if they don’t have matches.
- If a campaign isn’t linked to a user, the user columns appear as NULL.
- If an advertiser doesn’t have a campaign, the campaign columns appear as NULL.
✍️ SQL Exercises
Exercise 1: Listing All Advertisers and Campaigns
Jordan needs a complete list of advertisers and campaigns, even if they don’t match.
Filter condition:
- Retrieve user name, company, and campaign name.
- Ensure all users and all campaigns appear, even if they don’t match.
- Sort by company in ascending order.
| name | company | campaign_name |
|---|---|---|
| Alice Johnson | StartupX | Growth Hackers Special |
| Bob Smith | BigTech Corp | BigTech Awareness |
| Charlie Lee | EcomStore | Ecom Holiday Sales |
| David Kim | FashionHub | Fashion Hub Summer |
| Emma Garcia | HealthyLife | HealthyLife Brand Push |
| NULL | NULL | Unassigned Campaign |
Write an SQL query to return the requested data.
👉 Found this lesson helpful?