MySQL gives a syntax error on FULL OUTER JOIN
1064 syntax error near FULL OUTER JOINShort 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.
Fix it fast
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();2Match what you see, then jump to its fix
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-checkConfirmed by dev.mysql.com
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix