Skip to content
Quantoros

What is a FULL OUTER JOIN?

SQLLevel: FoundationJOINs Basics

What is a FULL OUTER JOIN?

Hints
  • Write the join keys and relationship cardinality.
  • What happens to duplicates and unmatched rows? Check WHERE versus ON.

Answer

A FULL OUTER JOIN returns all rows from both tables. Where there's no match on either side, NULLs fill in the missing columns.

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

This gives you: matched pairs + customers without orders (right side NULLs) + orders without customers (left side NULLs).

Note: MySQL doesn't support FULL OUTER JOIN directly. You simulate it with a LEFT JOIN UNION ALL RIGHT JOIN with NULL filter.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions