Level 9 · Ad spend next to site traffic

So far you have used inner joins (only matches) and left joins (everything on the left, matches on the right). Two more join types complete the set.

RIGHT JOIN and FULL OUTER JOIN

• A RIGHT JOIN B keeps every row of B. It is the same as B LEFT JOIN A, and most analysts rewrite it that way because reading left to right is easier.
• A FULL OUTER JOIN B keeps every row of both: matched pairs, plus rows of A with NULLs for B, plus rows of B with NULLs for A.

The 2025 summaries look like this when joined in full:

channel	spend	sessions	conversions
paid	340,778.33	3,585	55
social	131,540.77	2,563	19
email	38,322.22	1,946	187
organic	NULL	5,540	100
direct	NULL	2,591	139

The inner join in the starter returns only the first three rows. Organic search brought the most sessions of any source and costs nothing in ad spend, which is exactly what the marketing head wants to see.

The starter uses subqueries in FROM (derived tables) so each side is already one row per channel before the join. That avoids the fan-out you met earlier: joining raw daily spend rows to raw session rows would multiply both.

Why not just a LEFT JOIN?

Today every spend channel also has sessions, so w LEFT JOIN s would give the same five rows. The full join is still the safer choice for a reconciliation: if marketing starts paying for a channel that is not tracked as a session source, a left join from sessions would hide that spend, and a FULL OUTER JOIN shows it.

Common mistakes

• Taking the key from one side only. Use COALESCE(left_key, right_key).
• Filtering a FULL OUTER JOIN in WHERE on one side’s columns, for example WHERE s.spend > 0. That removes the NULL rows and turns it back into a narrower join.

In SQL Server

FULL OUTER JOIN and RIGHT JOIN are written the same way. ISNULL(s.channel, w.source) works like the two-argument COALESCE. Booleans differ: SQL Server has no TRUE, so the flag is a bit and the condition is CASE WHEN converted = 1 THEN 1 END.