Full outer, self and cross joins

Three joins for special jobs, reconciling two lists, matching a table to itself and building every combination.

INNER JOIN and LEFT JOIN (lessons 14 and 15) handle most work. These three cover the rest. The sample users table now has a manager_id column, showing who each rep reports to.

FULL OUTER JOIN: keep everything from both sides

A LEFT JOIN keeps every row from the left table. A FULL OUTER JOIN keeps every row from both tables, with NULLs wherever one side has no match.

Its main job is reconciliation: comparing two lists and finding what’s missing from each. Which accounts have deals but no contacts, and which have contacts but no deals?

WITH deal_counts AS (
    SELECT account_id, COUNT(*) AS deals
    FROM opportunities
    GROUP BY account_id
),
contact_counts AS (
    SELECT account_id, COUNT(*) AS contacts
    FROM contacts
    GROUP BY account_id
)
SELECT COALESCE(d.account_id, c.account_id) AS account_id,
       d.deals,
       c.contacts
FROM deal_counts AS d
FULL OUTER JOIN contact_counts AS c ON c.account_id = d.account_id
WHERE d.account_id IS NULL
   OR c.account_id IS NULL;

Accounts 10 and 11 have deals but nobody to call. Account 14 has contacts but no deals. The WHERE keeps only the mismatches, the rows that need attention.

COALESCE(d.account_id, c.account_id) matters: whichever side is missing has a NULL ID, so take the one that’s there. In real work, this is how you compare the CRM against billing, or last month’s export against this month’s.

Self join: a table joined to itself

Each rep’s manager_id points at another row in the same users table. To show the manager’s name, join users to itself, using a different alias for each copy:

SELECT r.name AS rep,
       m.name AS manager
FROM users AS r                                   -- r: the rep
JOIN users AS m ON m.user_id = r.manager_id;      -- m: the same table, as the manager

There’s nothing special about it: it’s an ordinary join where both tables happen to be the same. The aliases do all the work, and without them the database can’t tell which copy you mean.

Notice who’s missing: Chloe Park leads the team, so her manager_id is NULL and the inner join drops her. That’s the same choice as lesson 15: use a LEFT JOIN to keep her.

CROSS JOIN: every combination

A CROSS JOIN has no ON. It pairs every row on the left with every row on the right:

SELECT u.name, s.stage
FROM users AS u
CROSS JOIN (SELECT DISTINCT stage
            FROM opportunities
            WHERE closed_at IS NULL) AS s;

5 reps times 4 open stages is 20 rows. Its main use is building a complete grid, so that combinations with no data still show up as zeros:

SELECT u.name,
       s.stage,
       COUNT(o.opportunity_id) AS open_deals
FROM users AS u
CROSS JOIN (SELECT DISTINCT stage
            FROM opportunities
            WHERE closed_at IS NULL) AS s
LEFT JOIN opportunities AS o
       ON o.owner_id = u.user_id
      AND o.stage = s.stage
      AND o.closed_at IS NULL
GROUP BY u.name, s.stage;

Ana Ruiz has no Proposal-stage deals, and she still gets a row showing 0. Without the cross join, that row wouldn’t exist and her line on a chart would have a gap. Lesson 12 warned that groups only exist if rows exist. A cross join is how you make them exist.

Careful: the size multiplies. 1,000 customers crossed with 1,000 products is a million rows. An accidental cross join, from a missing ON condition, is a classic way to bring a query to a halt.

Common mistakes

Dialect notes

Snowflake notes

Snowflake supports all three. For walking a whole chain of managers, not just one level up, Snowflake has CONNECT BY as well as recursive CTEs, which a later advanced lesson covers. It also has ASOF JOIN, which matches each row to the nearest earlier row in another table by time, for things like “the price in effect when this deal closed”.

More in the field guide: FULL OUTER JOIN, Self join and CROSS JOIN.

Try it

List every rep with the name of their manager, A to Z by rep. The team lead has no manager, and she still needs to appear.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson