MySQL FULL OUTER JOIN: Emulate It with UNION

A FULL OUTER JOIN returns all rows from both tables: matches combined, and unmatched rows from either side with NULL on the other side. Standard SQL has it. MySQL does not. Writing FULL OUTER JOIN gives a syntax error.

query.sql

SQL

CREATE TABLE crm_contacts (email VARCHAR(30) PRIMARY KEY, name VARCHAR(20));
CREATE TABLE newsletter (email VARCHAR(30) PRIMARY KEY, subscribed_on DATE);
INSERT INTO crm_contacts VALUES ('asha@example.com', 'Asha'), ('ben@example.com', 'Ben'), ('chitra@example.com', 'Chitra');
INSERT INTO newsletter VALUES ('ben@example.com', '2026-02-01'), ('chitra@example.com', '2026-03-05'), ('dev@example.com', '2026-04-10');

SELECT c.email FROM crm_contacts c FULL OUTER JOIN newsletter n ON n.email = c.email;

Output

OUTPUT

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN newsletter n ON n.email = c.email'

Emulation 1: UNION of a LEFT and a RIGHT JOIN

Take everything from the left, plus everything from the right, and let UNION remove the duplicated matched rows:

query.sql

SQL

SELECT c.email AS crm_email, c.name, n.email AS newsletter_email, n.subscribed_on
FROM crm_contacts c
LEFT JOIN newsletter n ON n.email = c.email
UNION
SELECT c.email, c.name, n.email, n.subscribed_on
FROM crm_contacts c
RIGHT JOIN newsletter n ON n.email = c.email
ORDER BY COALESCE(crm_email, newsletter_email);

Output

OUTPUT

+--------------------+--------+--------------------+---------------+
| crm_email          | name   | newsletter_email   | subscribed_on |
+--------------------+--------+--------------------+---------------+
| asha@example.com   | Asha   | NULL               | NULL          |
| ben@example.com    | Ben    | ben@example.com    | 2026-02-01    |
| chitra@example.com | Chitra | chitra@example.com | 2026-03-05    |
| NULL               | NULL   | dev@example.com    | 2026-04-10    |
+--------------------+--------+--------------------+---------------+
4 rows in set

Asha is only in the CRM, Dev is only in the newsletter, and Ben and Chitra are in both.

Weakness: UNION removes duplicates by comparing whole rows. If your data can contain two identical rows legitimately, UNION removes one of them wrongly.

Emulation 2: LEFT JOIN plus an anti-join (safer)

UNION ALL keeps duplicates, so take the full left join, and then add only the right-side rows that had no match:

query.sql

SQL

SELECT c.email AS crm_email, c.name, n.email AS newsletter_email, n.subscribed_on
FROM crm_contacts c
LEFT JOIN newsletter n ON n.email = c.email
UNION ALL
SELECT NULL, NULL, n.email, n.subscribed_on
FROM newsletter n
WHERE NOT EXISTS (SELECT 1 FROM crm_contacts c WHERE c.email = n.email)
ORDER BY COALESCE(crm_email, newsletter_email);

Output

OUTPUT

+--------------------+--------+--------------------+---------------+
| crm_email          | name   | newsletter_email   | subscribed_on |
+--------------------+--------+--------------------+---------------+
| asha@example.com   | Asha   | NULL               | NULL          |
| ben@example.com    | Ben    | ben@example.com    | 2026-02-01    |
| chitra@example.com | Chitra | chitra@example.com | 2026-03-05    |
| NULL               | NULL   | dev@example.com    | 2026-04-10    |
+--------------------+--------+--------------------+---------------+
4 rows in set

The second query adds only the newsletter rows without a CRM partner (Dev). This version is exact even when rows repeat, and is usually the preferred form.

Seeing where each row came from

Add a label that tells which side each row is from:

query.sql

SQL

SELECT COALESCE(c.email, n.email) AS email,
       CASE WHEN c.email IS NULL THEN 'only in newsletter'
            WHEN n.email IS NULL THEN 'only in CRM'
            ELSE 'in both' END AS where_found
FROM crm_contacts c
LEFT JOIN newsletter n ON n.email = c.email
UNION ALL
SELECT n.email, 'only in newsletter'
FROM newsletter n
WHERE NOT EXISTS (SELECT 1 FROM crm_contacts c WHERE c.email = n.email)
ORDER BY email;

Output

OUTPUT

+--------------------+--------------------+
| email              | where_found        |
+--------------------+--------------------+
| asha@example.com   | only in CRM        |
| ben@example.com    | in both            |
| chitra@example.com | in both            |
| dev@example.com    | only in newsletter |
+--------------------+--------------------+
4 rows in set

This is the typical use of a full outer join: reconciling two lists. Which records exist in one system but not the other?

When you need it

Comparing two data sources (an import file against the current table, two systems after a migration).
Reports that must show a row for every key that appears in either table, such as monthly totals from two different tables.

Alternatives

Often a full outer join is not necessary. Build a list of all keys first (SELECT email FROM a UNION SELECT email FROM b), then LEFT JOIN both tables to that list. It is easy to read, and it extends to three or more tables:

query.sql

SQL

SELECT k.email, c.name, n.subscribed_on
FROM (SELECT email FROM crm_contacts UNION SELECT email FROM newsletter) AS k
LEFT JOIN crm_contacts c ON c.email = k.email
LEFT JOIN newsletter n ON n.email = k.email
ORDER BY k.email;

Output

OUTPUT

+--------------------+--------+---------------+
| email              | name   | subscribed_on |
+--------------------+--------+---------------+
| asha@example.com   | Asha   | NULL          |
| ben@example.com    | Ben    | 2026-02-01    |
| chitra@example.com | Chitra | 2026-03-05    |
| dev@example.com    | NULL   | 2026-04-10    |
+--------------------+--------+---------------+
4 rows in set

info

Other databases

PostgreSQL, SQL Server and Oracle support FULL OUTER JOIN directly. MySQL and MariaDB (before 10.x for some forms) do not. SQLite added it only recently. Keep the emulation handy if you port code.