SQL JOIN Types

A join matches rows from two tables using an ON condition, usually a key in one table equals a key in the other. The join type decides what happens to rows that have no match.

select o.order_id, c.first_name, c.last_name
from   orders    as o
join   customers as c on c.customer_id = o.customer_id

The four common joins

JOIN on its own means INNER JOIN. The word OUTER is optional: LEFT JOIN and LEFT OUTER JOIN are identical.

Finding rows with no match (anti-join)

A LEFT JOIN plus an IS NULL filter is the standard way to find rows that have no partner, such as products that were never ordered.

select p.product_name
from   products    as p
left join order_items as oi on oi.product_id = p.product_id
where  oi.product_id is null

That IS NULL test is the one right-table condition that belongs in WHERE. Any other filter on the right table, such as o.status = 'shipped', throws the unmatched rows away and quietly turns the LEFT JOIN into an inner join. Put it in ON instead: see why a LEFT JOIN turns into an INNER JOIN.

NULL join keys

A NULL key matches nothing, in any join, not even another NULL. ON keeps a pair only when its condition is true, and NULL = NULL is unknown. In Website Analytics, 14 of the 30 sessions have no campaign_id, and an inner join to campaigns drops every one of them:

select (select count(*) from sessions) as sessions,
       (select count(*)
        from sessions as s
        join campaigns as c on c.campaign_id = s.campaign_id) as after_inner_join

That returns 30 and 16, with no error. When those rows belong in the result, use a LEFT JOIN; see why an INNER JOIN drops rows with NULL keys.

Sometimes two NULL keys should match. Group by a nullable column and the NULLs form a group of their own; join the totals back on =, and that group can't find its rows:

with by_campaign as (
  select campaign_id, count(*) as campaign_sessions
  from sessions
  group by campaign_id
)
select count(*) as joined_rows
from sessions as s
join by_campaign as b on b.campaign_id = s.campaign_id

Only 16 rows come back. IS NOT DISTINCT FROM compares like =, except that two NULLs count as equal:

with by_campaign as (
  select campaign_id, count(*) as campaign_sessions
  from sessions
  group by campaign_id
)
select count(*) as joined_rows
from sessions as s
join by_campaign as b on b.campaign_id is not distinct from s.campaign_id

All 30 rows join, the 14 unattributed sessions to their own group. DuckDB, PostgreSQL, and SQL Server 2022 spell it IS NOT DISTINCT FROM; MySQL uses <=>. Whatever the join, check which key columns can be NULL before you trust its row count. The NULL handling reference collects the rest of the rules.