SQL FULL OUTER JOIN: MySQL Workaround & Examples

By Sharon Ben-Moshe · August 26, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Learn SQL FULL OUTER JOIN results, syntax across databases, NULL handling, and a MySQL workaround using LEFT JOIN, RIGHT JOIN, and UNION ALL.

Quick answer

A FULL OUTER JOIN returns matching rows plus unmatched rows from both inputs, using NULL for the missing side. PostgreSQL, SQL Server, SQLite, and BigQuery support FULL OUTER JOIN; MySQL needs a UNION ALL workaround that adds right-only rows after a LEFT JOIN.

FAQs

What is a FULL OUTER JOIN?

A FULL OUTER JOIN returns every matching row plus unmatched rows from both input tables. Columns from the missing side are NULL for an unmatched row, so the result is the union of matched, left-only, and right-only rows.

Does MySQL support FULL OUTER JOIN?

MySQL’s documented JOIN syntax does not provide a FULL OUTER JOIN keyword. A common equivalent is a LEFT JOIN combined with right-only rows from a RIGHT JOIN using UNION ALL, with a non-null left key used to identify those unmatched rows.

Why use UNION ALL in a MySQL full join workaround?

The first LEFT JOIN branch contains all matches and left-only rows. The second branch is filtered to right-only rows, so UNION ALL combines two non-overlapping sets without the extra duplicate-elimination work performed by UNION.

Can a WHERE clause break a FULL OUTER JOIN?

Yes. A WHERE predicate on a column from one joined table can discard rows where that table is NULL, defeating the outer join. Put matching conditions in ON, and write any NULL-aware post-join filter deliberately.

SQL Dialects

SQL COALESCE vs ISNULL: Key Differences

By Sharon Ben-Moshe · Aug 26, 2026

Compare SQL COALESCE and ISNULL: argument count, portability, return types, nullability, evaluation behavior, and the MySQL naming trap.

Read post

SQL Dialects

SQL ROW_NUMBER(): How to Number Rows

By Sharon Ben-Moshe · Aug 26, 2026

Learn SQL ROW_NUMBER with practical examples for numbering rows, latest-row-per-group queries, tie-breaking, and when RANK is the better choice.

Read post

SQL Dialects

SQL Date Difference: DATEDIFF by Database

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

FULL OUTER JOIN returns matches, left-only rows, and right-only rows in one result.

Use a non-null key to label which side is missing; do not filter the right table in WHERE and accidentally remove left-only rows.

MySQL’s JOIN syntax does not include FULL OUTER JOIN, so use a LEFT JOIN plus right-only rows with UNION ALL.

What does FULL OUTER JOIN return?

PostgreSQL, SQL Server, SQLite, and BigQuery document FULL join forms in their query syntax. BigQuery’s JOIN type reference describes a full outer join as returning all rows from both sides, with NULLs for unmatched columns.

Return matched and unmatched customer/order rows

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

How do you tell which side did not match?

Test a non-null key from each input. In a well-designed table, a primary key is non-null, so a NULL key from one side of the joined result identifies the unmatched side without relying on a nullable descriptive field.

Label the result of a full outer join

SELECT
  c.customer_id,
  o.order_id,
  CASE
    WHEN c.customer_id IS NULL THEN 'order without customer'
    WHEN o.order_id IS NULL THEN 'customer without order'
    ELSE 'matched'
  END AS match_status
FROM customers AS c
FULL OUTER JOIN orders AS o
  ON o.customer_id = c.customer_id;

What is the MySQL FULL OUTER JOIN workaround?

MySQL’s documented JOIN syntax supports LEFT and RIGHT outer joins but not FULL OUTER JOIN. Combine all rows from a LEFT JOIN with only the right-side rows that had no left match.

Emulate a full outer join in MySQL

SELECT
  c.customer_id,
  c.customer_name,
  o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id

UNION ALL

SELECT
  c.customer_id,
  c.customer_name,
  o.order_id
FROM customers AS c
RIGHT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

The WHERE condition belongs only in the second branch, where it keeps right-only rows. The first LEFT JOIN branch already includes every match and every left-only row.

Which databases support FULL OUTER JOIN?

PostgreSQL supports full joins in its SELECT syntax, SQLite lists FULL OUTER JOIN in its SELECT grammar, and BigQuery supports FULL [OUTER] JOIN. Confirm the server version and exact dialect when a query must run across multiple engines.

For the simpler choice between matched rows only and preserving every left row, see SQL INNER JOIN vs LEFT JOIN.

Format multi-join queries with sqlfmt before review.

Quick answer

FAQs

What is a FULL OUTER JOIN?

Does MySQL support FULL OUTER JOIN?

Why use UNION ALL in a MySQL full join workaround?

Can a WHERE clause break a FULL OUTER JOIN?

Keep reading

SQL COALESCE vs ISNULL: Key Differences

SQL ROW_NUMBER(): How to Number Rows

SQL Date Difference: DATEDIFF by Database

Key takeaways

What does FULL OUTER JOIN return?

How do you tell which side did not match?

What is the MySQL FULL OUTER JOIN workaround?

Which databases support FULL OUTER JOIN?