SQL FULL OUTER JOIN

Keeps everything from BOTH sides — the union of what LEFT JOIN and RIGHT JOIN would each produce.

What & Why

FULL OUTER JOIN keeps every row from both tables — matched rows combine normally, and any row from either side with no match gets NULL filled in for the missing side. It's genuinely the union of a LEFT JOIN's result and a RIGHT JOIN's result.

See How It Works

BUSINESS QUESTION

Marketing reconciles filtered campaign and lead subsets so unmatched rows from both sides remain visible.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

Trace the relationship row by row1× speed

campaign_subset · marketing.campaigns

id	name
1	Spring Launch
2	Retention Webinar
4	Enterprise Search

campaign.id = lead.campaign_id

lead_subset · marketing.leads

id	campaign_id	source
301	1	google_ads
304	3	linkedin

Result set · 1 rows

campaign_id	name	lead_campaign_id
1	Spring Launch	1

Spring Launch and Lead 301 match across the two filtered internal-table subsets.

EXAMPLE QUERY

WITH campaign_subset AS (
  SELECT id, name
  FROM marketing.campaigns
  WHERE id IN (1, 2, 4)
),
lead_subset AS (
  SELECT id, campaign_id
  FROM marketing.leads
  WHERE id IN (301, 304)
)
SELECT
  c.id AS campaign_id,
  c.name,
  l.campaign_id AS lead_campaign_id
FROM campaign_subset c
FULL OUTER JOIN lead_subset l ON l.campaign_id = c.id
ORDER BY COALESCE(c.id, l.campaign_id), l.id;

Now You Try

Practice this concept

Marketing reconciles filtered campaign and lead subsets with unmatched rows on both sides.

Available schema

marketing

Prefix tables with marketing.table_name.

campaign_idnamelead_campaign_id

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

marketing.leads

Column	Type
id	integer
campaign_id	integer
email	text
created_at	timestamp with time zone
qualified_at	timestamp with time zone
converted_at	timestamp with time zone
lead_score	integer
source	text
country	text
archive_status	text

query.sql

Intermediate business practice