Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL gives a syntax error on FULL OUTER JOIN

Error1064 syntax error near FULL OUTER JOIN

Short answer

The MySQL join grammar lists INNER, CROSS, LEFT and RIGHT joins, not FULL, so the parser stops at the word FULL. Write the same result as a LEFT JOIN, UNION, and a RIGHT JOIN. UNION removes duplicate rows. If you need to keep real duplicates, use UNION ALL with an anti-join.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find the word FULL in the failing query. The error points at it. Check the join type: only LEFT and RIGHT outer joins exist in MySQL.

    SELECT VERSION();
  2. 2Match what you see, then jump to its fix

    1. 1ERROR 1064 near 'FULL OUTER JOIN'FULL OUTER JOIN is not part of MySQL's join syntaxFix
    2. 2The rewrite runs but returns fewer rows than expectedUNION removes rows that really are duplicatesFix

What does the error look like?

  • 1064 syntax error near FULL OUTER JOIN

Cause 1 of 2

FULL OUTER JOIN is not part of MySQL's join syntax

1Check

The joined_table grammar in the manual shows table_reference {LEFT|RIGHT} [OUTER] JOIN. FULL is not in it.

-- fails in MySQL
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

2Fix

Combine a LEFT JOIN and a RIGHT JOIN with UNION. UNION removes duplicate rows, so each matched pair appears once.

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

3Verify

The statement runs and returns rows from a with no match, rows from b with no match, and matched rows.

-- count rows from each side if you need to cross-check

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

UNION removes rows that really are duplicates

1Check

By default a set operation removes duplicate rows. If your data legitimately contains identical rows, UNION merges them.

SELECT COUNT(*) FROM a;

2Fix

Use UNION ALL, and keep the second block to rows with no match on the left side, an anti-join like the one the manual shows for LEFT JOIN with IS NULL.

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

3Verify

Compare the total with a count of the matched rows plus both unmatched sets.

SELECT COUNT(*) FROM a LEFT JOIN b ON a.id = b.a_id;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Select explicit column lists instead of *, so both blocks of the UNION line up and names are stable.
  • Remember that column names in the result come from the first SELECT.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.