SQL FULL OUTER JOIN

A LEFT JOIN (Lesson 5.3) keeps every row of one table. That's enough when only one side can have rows without a partner. This lesson switches to Website Analytics, a small dataset of visits to a SaaS website, where both sides can. Each session has a campaign_id, but 14 of the 30 sessions came from search or a typed-in address, so theirs is NULL. And one of the five campaigns, Retargeting, brought in no sessions at all.

Each LEFT JOIN loses a side

Count sessions per campaign starting from campaigns:

select c.campaign_name, count(s.session_id) as sessions
from campaigns as c
left join sessions as s on s.campaign_id = c.campaign_id
group by c.campaign_name
order by c.campaign_name

Retargeting is there with 0, but the counts add up to 16. The 14 sessions with no campaign have nothing in campaigns to match, so they never appear. Turn it around and start from sessions:

select c.campaign_name, count(s.session_id) as sessions
from sessions as s
left join campaigns as c on c.campaign_id = s.campaign_id
group by c.campaign_name
order by c.campaign_name

Now the 14 are counted, under a NULL campaign name, and Retargeting has gone. No session points at it, so starting from sessions never reaches it.

FULL JOIN keeps both

FULL JOIN (also written FULL OUTER JOIN) returns the matched pairs, plus every unmatched row from either table, with NULL in the other table's columns. That makes it the join for reconciling two lists: filter for a NULL on either side and you get everything that didn't match.

select s.session_id, s.channel, c.campaign_name
from sessions as s
full join campaigns as c on c.campaign_id = s.campaign_id
where s.session_id is null
   or c.campaign_id is null
order by s.session_id

That's 15 rows: the 14 sessions with no campaign, then Retargeting with an empty session_id. Test the join key for NULL, or another column that can never be empty in a real row, such as s.session_id. A column that can be empty on its own would mix up "no match" with "no value".

Counting works as in Lesson 5.3. Count a column from the side that can be missing, count(s.session_id). count(*) counts Retargeting's one unmatched row and reports a session it never had.

RIGHT JOIN is a LEFT JOIN turned around

RIGHT JOIN keeps every row of the table after the keyword instead of before it:

select c.campaign_name, s.session_id
from campaigns as c
right join sessions as s on s.campaign_id = c.campaign_id
order by s.session_id
limit 5

campaigns right join sessions returns the same rows as sessions left join campaigns. Most people write the LEFT JOIN and put the table they want to keep first, so the query reads in the order it works. Expect to meet RIGHT JOIN in other people's queries more than to write it.

PostgreSQL, SQL Server, and Oracle support both joins. SQLite added them in version 3.39 (2022). MySQL has RIGHT JOIN but no FULL JOIN; there you combine a left join and a right join with UNION. To print a label such as "(no campaign)" instead of NULL, use COALESCE, in Lesson 6.3.