What rows does a FULL OUTER JOIN return that an INNER JOIN of the same tables does not?
answer
- no source row is ever dropped
- unmatched rows survive on both sides
- the missing side is filled with NULLs
- inner-join rows plus both leftover sets
- LEFT plus RIGHT preservation in one join
basics
~20 sFULL OUTER JOIN returns every matched pair plus every unmatched row from both tables. An unmatched left row gets NULLs in all right-side columns, and an unmatched right row gets NULLs in all left-side columns.
solid answer
~40 sA `FULL OUTER JOIN` result is three buckets glued together. First, every pair of rows that satisfies the `ON` predicate — exactly what an `INNER JOIN` would give. Second, every left-table row that matched nothing, carried through with **NULL in every right-table column**. Third, every right-table row that matched nothing, carried through with **NULL in every left-table column**. That NULL-filling is called NULL-extension: the row is kept even though there is no partner to supply values. So a full outer join never drops a source row, which is why it is the join for reconciliation and diff-style work. `LEFT JOIN` preserves only the left side, `RIGHT JOIN` only the right, `INNER JOIN` neither; `FULL OUTER JOIN` preserves both. The word `OUTER` is optional — `FULL JOIN` means the same thing.
code
sql · 10 lines-- employees(id, name, dept_id): (1,'Ann',10), (2,'Bob',20), (3,'Dee',20), (4,'Eve',40)
-- departments(id, name): (10,'Sales'), (20,'Ops'), (30,'Legal')
SELECT e.name AS employee, d.name AS dept
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;
-- Ann | Sales
-- Bob | Ops
-- Dee | Ops
-- Eve | NULL <- employee whose dept_id matches nothing
-- NULL | Legal <- department with no employeesgo deeper
Be ready to name the three buckets — matched pairs, left-only rows, right-only rows — and to say that the missing side comes back as NULL, not as blanks or zeros.
Explain NULL-extension mechanically and do the row arithmetic out loud: L + R − M for distinct keys, and why the matched bucket multiplies when a key repeats on both sides.
Show where a full outer join is the correct instrument — reconciling two systems, merging partially overlapping measure sets — and what you do downstream about columns that may be NULL on either side.
Frame when reconciliation belongs in a single SQL statement at all versus a pipeline step, and what data-quality signals the three buckets give you about upstream systems.
## The shape of the result Every join starts from the same idea: pair rows from the left table with rows from the right table, and keep the pairs for which the `ON` predicate is true. The join *type* only decides what happens to rows that found no partner. - `INNER JOIN` — throw unmatched rows away, from both sides. - `LEFT [OUTER] JOIN` — keep unmatched left rows. - `RIGHT [OUTER] JOIN` — keep unmatched right rows. - `FULL [OUTER] JOIN` — keep unmatched rows from **both** sides. So a full outer join result is the union of three disjoint sets: 1. the inner-join rows (matched pairs), 2. the left-only rows, NULL-extended on the right, 3. the right-only rows, NULL-extended on the left. The keyword `OUTER` is noise: `FULL JOIN` and `FULL OUTER JOIN` are the same statement. Like every join type, `FULL OUTER JOIN` requires an `ON` predicate (or a `USING` list); it is not a Cartesian product. ## NULL-extension When a row has no partner, the engine still emits it, filling every column that would have come from the other table with NULL. Those NULLs are manufactured by the join — they are not values stored anywhere. This has three practical consequences: - Any column from the other side may be NULL, including columns declared `NOT NULL` in their own table. `NOT NULL` constrains the *table*, not the join output. - Expressions over those columns inherit the NULL: `e.salary * 1.1` is NULL for an unmatched row, and `d.name = 'Legal'` evaluates to unknown rather than true. - Neither key column is safe to select on its own — see the `COALESCE` pattern for building one unified key column. ## A worked example With `employees` holding Ann (dept 10), Bob (dept 20), Dee (dept 20) and Eve (dept 40), and `departments` holding 10 Sales, 20 Ops and 30 Legal: ```sql SELECT e.name, d.name AS dept FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.id; ``` yields five rows: Ann/Sales, Bob/Ops, Dee/Ops (the three matches), Eve/NULL (an employee pointing at a department that does not exist), and NULL/Legal (a department with nobody in it). The same query as an `INNER JOIN` returns three rows; as a `LEFT JOIN`, four; as a `RIGHT JOIN`, four. ## Counting rows For a one-to-one key relationship the arithmetic is easy: if L rows are on the left, R on the right, and M keys match, the result has M + (L − M) + (R − M) = L + R − M rows. Two extremes bracket it: if every row matches (M = L = R) you get L rows, exactly the inner join; if nothing matches you get L + R rows, every source row NULL-extended. So a full outer join always returns **at least** as many rows as the corresponding inner join, and — with distinct keys — at most L + R. When a key value appears several times on both sides, the matched bucket multiplies the way it does for any join: three left rows and two right rows sharing a key produce six output rows. Only the *unmatched* buckets are guaranteed one output row per source row. ## Where it is the right tool The characteristic use is comparing two sets that should agree: yesterday's export against today's, a source system against a warehouse copy, a ledger against a bank statement. Because no row is dropped, one pass tells you what exists only on the left, only on the right, and on both. A second family of uses is merging two partially overlapping attribute sets — for example, monthly revenue by region from one table and monthly cost by region from another, where a region may appear in only one of them. ## Portability `FULL OUTER JOIN` is standard SQL and is implemented by PostgreSQL, Oracle, SQL Server and DB2, among others. MySQL does not implement it, so on MySQL the pattern is written by hand as a `LEFT JOIN` combined with `UNION ALL` and an anti-joined branch for the right-only rows. SQLite gained `FULL OUTER JOIN` in version 3.39; older builds need the same rewrite. ## Common mistakes Confusing `FULL` with `CROSS` is the classic one — a cross join pairs everything with everything and takes no `ON` clause, while a full outer join pairs only what matches and then rescues the leftovers. The second is assuming the row count is always L + R; that only holds when nothing matches. The third is reading a NULL in the output as "stored NULL" when it may simply mean "no partner row".
- The left table has 100 rows, the right has 80, and 60 keys match one-to-one. How many rows come out?120. Sixty matched pairs, plus the 40 left rows with no partner (NULL-extended on the right), plus the 20 right rows with no partner (NULL-extended on the left). The general formula for distinct one-to-one keys is L + R − M.
- Does swapping the two tables around the FULL OUTER JOIN keyword change the result?No. The operation is symmetric: the same set of rows comes back, because both sides are preserved. Only the column order in `SELECT *` changes, and row order is undefined either way unless you add `ORDER BY`. That symmetry is exactly what `LEFT` and `RIGHT` joins lack.
- Can a column declared NOT NULL come back as NULL from a FULL OUTER JOIN?Yes. `NOT NULL` constrains what the table may store; it says nothing about join output. A row that had no partner is NULL-extended across every column of the other table, `NOT NULL` ones included, because the join invents those NULLs rather than reading them.
saying these in an interview costs you the question
- Says FULL OUTER JOIN produces a Cartesian product
- Thinks it returns only the rows that failed to match
- Expects zeros or empty strings instead of NULLs
- Assumes the row count is always left rows plus right rows
- Claims a NOT NULL column can never be NULL in the result