SQL FULL OUTER JOIN: Syntax, Examples & Practice

Take two lists from the Playground database: customers who have placed an order (buyers) and customers who have written a review (reviewers). Some people are on both lists, some on only one. A FULL OUTER JOIN lines the lists up and keeps everybody:

WITH buyers AS (
  SELECT DISTINCT user_id FROM orders
), reviewers AS (
  SELECT DISTINCT user_id FROM reviews
)
SELECT b.user_id AS buyer_id,
       r.user_id AS reviewer_id
FROM buyers b
FULL OUTER JOIN reviewers r
  ON r.user_id = b.user_id
ORDER BY COALESCE(b.user_id, r.user_id)
LIMIT 8;

▶ Run this query in the SQL Playground

Read the two columns together. Customers 5 to 8 appear in both: they ordered and reviewed. Customers 1, 2 and 4 have a NULL on the right: they ordered but never reviewed. Customer 3 has a NULL on the left: a review but no order. No one is dropped from either list.

The two WITH blocks are CTEs, named subqueries that act as the two tables. An INNER JOIN would keep only customers 5 to 8.

SELECT columns
FROM table_a
FULL OUTER JOIN table_b
  ON table_a.key = table_b.key;

Database support

A FULL OUTER JOIN is the result of a LEFT JOIN plus the right-table rows that the LEFT JOIN left out. Counting the four joins on the same two lists shows how they relate:

WITH buyers AS (
  SELECT DISTINCT user_id FROM orders
), reviewers AS (
  SELECT DISTINCT user_id FROM reviews
)
SELECT 'INNER JOIN' AS join_type, COUNT(*) AS rows_returned
FROM buyers b INNER JOIN reviewers r ON r.user_id = b.user_id
UNION ALL
SELECT 'LEFT JOIN', COUNT(*)
FROM buyers b LEFT JOIN reviewers r ON r.user_id = b.user_id
UNION ALL
SELECT 'RIGHT JOIN', COUNT(*)
FROM buyers b RIGHT JOIN reviewers r ON r.user_id = b.user_id
UNION ALL
SELECT 'FULL OUTER JOIN', COUNT(*)
FROM buyers b FULL OUTER JOIN reviewers r ON r.user_id = b.user_id;

▶ Run this query in the SQL Playground

The arithmetic always holds: LEFT + RIGHT − INNER = FULL, here 114 + 104 − 84 = 134.

Use a LEFT JOIN when one table is the master list and the other adds optional detail. Use FULL OUTER JOIN when neither table is the master: two lists that should agree, or two reports to merge. The SQL JOINs overview shows all six join types together.

FULL OUTER JOIN vs UNION

A UNION of the two lists would give the same 134 customers, but as a single column with no way to see who is on which list.

The most common real use: reconciling two sets of data.

After a FULL OUTER JOIN, a NULL key on either side marks a row that exists in only one table. Keep just those rows and you have every mismatch, from both directions, in one query:

WITH buyers AS (
  SELECT DISTINCT user_id FROM orders
), reviewers AS (
  SELECT DISTINCT user_id FROM reviews
)
SELECT COALESCE(b.user_id, r.user_id) AS user_id,
       CASE WHEN b.user_id IS NULL THEN 'reviewed but never ordered'
            ELSE 'ordered but never reviewed' END AS issue
FROM buyers b
FULL OUTER JOIN reviewers r
  ON r.user_id = b.user_id
WHERE b.user_id IS NULL
   OR r.user_id IS NULL
ORDER BY user_id
LIMIT 6;

▶ Run this query in the SQL Playground

COALESCE picks whichever id is not NULL, so there is one id column whatever side the row came from. Grouping the same join gives a summary of all three situations:

WITH buyers AS (
  SELECT DISTINCT user_id FROM orders
), reviewers AS (
  SELECT DISTINCT user_id FROM reviews
)
SELECT CASE WHEN b.user_id IS NULL THEN 'reviewed only'
            WHEN r.user_id IS NULL THEN 'ordered only'
            ELSE 'ordered and reviewed' END AS segment,
       COUNT(*) AS customers
FROM buyers b
FULL OUTER JOIN reviewers r
  ON r.user_id = b.user_id
GROUP BY segment
ORDER BY segment;

▶ Run this query in the SQL Playground

The same shape reconciles any two sources: a payments file against the orders table, last month's customer list against this month's, a staging table against production. Rows with a NULL on the left are new or unexpected; rows with a NULL on the right are missing.

FULL OUTER JOIN is also how two summaries are put side by side when either can be missing a row. Count orders per customer and reviews per customer separately, then join the two results. A customer with orders but no reviews, or the reverse, still gets a row:

WITH o AS (
  SELECT user_id, COUNT(*) AS orders FROM orders GROUP BY user_id
), r AS (
  SELECT user_id, COUNT(*) AS reviews FROM reviews GROUP BY user_id
)
SELECT COALESCE(o.user_id, r.user_id) AS user_id,
       COALESCE(o.orders, 0)  AS orders,
       COALESCE(r.reviews, 0) AS reviews
FROM o
FULL OUTER JOIN r
  ON r.user_id = o.user_id
ORDER BY user_id
LIMIT 8;

▶ Run this query in the SQL Playground

To add names, join the result to users on the combined key:

WITH buyers AS (
  SELECT DISTINCT user_id FROM orders
), reviewers AS (
  SELECT DISTINCT user_id FROM reviews
)
SELECT u.id, u.first_name,
       CASE WHEN b.user_id IS NOT NULL THEN 'yes' ELSE 'no' END AS has_ordered,
       CASE WHEN r.user_id IS NOT NULL THEN 'yes' ELSE 'no' END AS has_reviewed
FROM buyers b
FULL OUTER JOIN reviewers r
  ON r.user_id = b.user_id
JOIN users u
  ON u.id = COALESCE(b.user_id, r.user_id)
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

MySQL and MariaDB do not have FULL OUTER JOIN. The standard workaround is a LEFT JOIN and a RIGHT JOIN combined with UNION, which removes the matched rows that both halves return:

SELECT a.id, a.name, b.id, b.value
FROM a
LEFT JOIN b ON b.a_id = a.id
UNION
SELECT a.id, a.name, b.id, b.value
FROM a
RIGHT JOIN b ON b.a_id = a.id;

Running that pattern on the buyers and reviewers lists returns the same number of rows as the real FULL OUTER JOIN:

WITH buyers AS (
  SELECT DISTINCT user_id FROM orders
), reviewers AS (
  SELECT DISTINCT user_id FROM reviews
)
SELECT COUNT(*) AS rows_from_union
FROM (
  SELECT b.user_id AS buyer_id, r.user_id AS reviewer_id
  FROM buyers b LEFT JOIN reviewers r ON r.user_id = b.user_id
  UNION
  SELECT b.user_id, r.user_id
  FROM buyers b RIGHT JOIN reviewers r ON r.user_id = b.user_id
) AS emulated;

▶ Run this query in the SQL Playground

UNION also removes genuine duplicate rows. If the tables can contain identical rows that must be kept, use this version instead: all the LEFT JOIN rows, plus only the right-table rows that had no match.

SELECT a.id, a.name, b.id, b.value
FROM a
LEFT JOIN b ON b.a_id = a.id
UNION ALL
SELECT a.id, a.name, b.id, b.value
FROM a
RIGHT JOIN b ON b.a_id = a.id
WHERE a.id IS NULL;

A WHERE condition on a column of either table is not true for the rows where that table is NULL, so it removes them. Filtering on one side quietly turns the FULL OUTER JOIN into a LEFT, RIGHT or INNER JOIN:

To filter and still keep both sides complete, filter each table before the join, in its own subquery or CTE. That is what the buyers and reviewers CTEs do. To restrict buyers to 2024 without losing reviewers, put the date condition inside the CTE:

WITH buyers AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2024-01-01'
), reviewers AS (
  SELECT DISTINCT user_id FROM reviews
  WHERE reviewed_at >= '2024-01-01'
)
SELECT CASE WHEN b.user_id IS NULL THEN 'reviewed only'
            WHEN r.user_id IS NULL THEN 'ordered only'
            ELSE 'ordered and reviewed' END AS segment,
       COUNT(*) AS customers
FROM buyers b
FULL OUTER JOIN reviewers r
  ON r.user_id = b.user_id
GROUP BY segment
ORDER BY segment;

▶ Run this query in the SQL Playground

What these have in common is that neither side is the reference. If one side is known to be complete, a LEFT JOIN from it is simpler and says so.

7 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Build two lists, products that have been ordered and products that have been reviewed, and FULL OUTER JOIN them. Show the first ten rows by product id. Table: order_items, reviews

   Show solution

   SQLite · runs in the Playground

   WITH ordered AS (
     SELECT DISTINCT product_id FROM order_items
   ), reviewed AS (
     SELECT DISTINCT product_id FROM reviews
   )
   SELECT o.product_id AS ordered_id,
          r.product_id AS reviewed_id
   FROM ordered o
   FULL OUTER JOIN reviewed r
     ON r.product_id = o.product_id
   ORDER BY COALESCE(o.product_id, r.product_id)
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   A NULL in reviewed_id means the product was ordered but never reviewed, and the reverse for ordered_id.

2. 2Medium Count the products that were ordered only, reviewed only, or both. Table: order_items, reviews

   Show solution

   SQLite · runs in the Playground

   WITH ordered AS (
     SELECT DISTINCT product_id FROM order_items
   ), reviewed AS (
     SELECT DISTINCT product_id FROM reviews
   )
   SELECT CASE WHEN o.product_id IS NULL THEN 'reviewed only'
               WHEN r.product_id IS NULL THEN 'ordered only'
               ELSE 'ordered and reviewed' END AS segment,
          COUNT(*) AS products
   FROM ordered o
   FULL OUTER JOIN reviewed r
     ON r.product_id = o.product_id
   GROUP BY segment
   ORDER BY segment;

   ▶ Run this query in the SQL Playground

   The side that is NULL identifies the segment. The result has 3 segments.

3. 3Medium Compare customers who ordered in 2023 with customers who ordered in 2024. List the ones who ordered in exactly one of the two years, with the year. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH y2023 AS (
     SELECT DISTINCT user_id FROM orders
     WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
   ), y2024 AS (
     SELECT DISTINCT user_id FROM orders
     WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
   )
   SELECT COALESCE(a.user_id, b.user_id) AS user_id,
          CASE WHEN a.user_id IS NULL THEN '2024 only' ELSE '2023 only' END AS year
   FROM y2023 a
   FULL OUTER JOIN y2024 b
     ON b.user_id = a.user_id
   WHERE a.user_id IS NULL
      OR b.user_id IS NULL
   ORDER BY user_id;

   ▶ Run this query in the SQL Playground

   The WHERE keeps only rows with a NULL on one side: 69 customers ordered in one year but not the other.

4. 4Medium For each customer id, show the number of orders and the number of payments, with 0 where a customer has none of one kind. Show the first ten by id. Table: orders, payments

   Show solution

   SQLite · runs in the Playground

   WITH o AS (
     SELECT user_id, COUNT(*) AS orders FROM orders GROUP BY user_id
   ), p AS (
     SELECT user_id, COUNT(*) AS payments FROM payments GROUP BY user_id
   )
   SELECT COALESCE(o.user_id, p.user_id) AS user_id,
          COALESCE(o.orders, 0)   AS orders,
          COALESCE(p.payments, 0) AS payments
   FROM o
   FULL OUTER JOIN p
     ON p.user_id = o.user_id
   ORDER BY user_id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   Each table is reduced to one row per customer before the join, so nothing is multiplied.

5. 5Medium Write the FULL OUTER JOIN of buyers and reviewers the MySQL way, with LEFT JOIN, RIGHT JOIN and UNION. Show the first eight rows. Table: orders, reviews

   Show solution

   SQLite · runs in the Playground

   WITH buyers AS (
     SELECT DISTINCT user_id FROM orders
   ), reviewers AS (
     SELECT DISTINCT user_id FROM reviews
   )
   SELECT buyer_id, reviewer_id
   FROM (
     SELECT b.user_id AS buyer_id, r.user_id AS reviewer_id
     FROM buyers b
     LEFT JOIN reviewers r ON r.user_id = b.user_id
     UNION
     SELECT b.user_id, r.user_id
     FROM buyers b
     RIGHT JOIN reviewers r ON r.user_id = b.user_id
   ) AS full_join
   ORDER BY COALESCE(buyer_id, reviewer_id)
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   The LEFT JOIN supplies every buyer, the RIGHT JOIN every reviewer, and UNION removes the customers that both halves return. The outer query only sorts the combined rows.

6. 6Challenging List the customers who have reviewed a product but never placed an order, with their name and city. Table: orders, reviews, users

   Show solution

   SQLite · runs in the Playground

   WITH buyers AS (
     SELECT DISTINCT user_id FROM orders
   ), reviewers AS (
     SELECT DISTINCT user_id FROM reviews
   )
   SELECT u.id, u.first_name, u.last_name, u.city
   FROM buyers b
   FULL OUTER JOIN reviewers r
     ON r.user_id = b.user_id
   JOIN users u
     ON u.id = r.user_id
   WHERE b.user_id IS NULL
   ORDER BY u.id;

   ▶ Run this query in the SQL Playground

   b.user_id IS NULL keeps reviewer-only rows. 20 customers reviewed without ordering. A LEFT JOIN from reviewers would also work here, because only one side is needed.

7. 7Challenging For each month of 2024, show the number of orders placed and reviews written, in one row per month. Table: orders, reviews

   Show solution

   SQLite · runs in the Playground

   WITH o AS (
     SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
     FROM orders
     WHERE order_date >= '2024-01-01'
     GROUP BY month
   ), r AS (
     SELECT strftime('%Y-%m', reviewed_at) AS month, COUNT(*) AS reviews
     FROM reviews
     WHERE reviewed_at >= '2024-01-01'
     GROUP BY month
   )
   SELECT COALESCE(o.month, r.month) AS month,
          COALESCE(o.orders, 0)  AS orders,
          COALESCE(r.reviews, 0) AS reviews
   FROM o
   FULL OUTER JOIN r
     ON r.month = o.month
   ORDER BY month;

   ▶ Run this query in the SQL Playground

   Two monthly summaries merged on the month. FULL OUTER JOIN guarantees a month appears even if it has orders but no reviews, or the reverse. strftime is SQLite's date function.