FULL OUTER JOIN

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	email	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

SQL

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

SQL

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.

Lesson Objectives

🎬 Scenario: Getting a Complete View of Advertisers and Campaigns#

1. Understanding FULL OUTER JOIN#