FULL OUTER JOINJoins
Buy Me A Coffee

Returns all rows from both tables; NULLs fill columns where no match exists on either side.

Returns all rows from both tables, filling in NULLs where there is no match. Useful for finding gaps in both directions (e.g., customers who have never ordered and orders with missing customer records). Not supported natively by MySQL without workarounds.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — Not supported. Emulate with LEFT JOIN UNION RIGHT JOIN.
PostgreSQL ✓ Supported all OUTER keyword is optional.
SQL Server ✓ Supported all OUTER keyword is optional.
Oracle ✓ Supported all OUTER keyword is optional.
SQLite ✓ Supported 3.39.0 FULL OUTER JOIN added in SQLite 3.39.0 (July 2022). Emulate with UNION on older versions.

Details

Useful for finding rows that exist in one table but not the other. MySQL has no native support and requires a UNION workaround.

Standard Syntax

SELECT a.col, b.col FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.a_id;

Version Support

MySQL: Not supported PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since 3.39.0

Per-Database Syntax & Notes

MySQL

Not supported. Emulate with LEFT JOIN UNION RIGHT JOIN.

SELECT a.col, b.col FROM a LEFT JOIN b ON a.id = b.id UNION SELECT a.col, b.col FROM a RIGHT JOIN b ON a.id = b.id;

PostgreSQL

OUTER keyword is optional.

SELECT a.col, b.col FROM a FULL OUTER JOIN b ON a.id = b.a_id;

SQL Server

OUTER keyword is optional.

SELECT a.col, b.col FROM a FULL OUTER JOIN b ON a.id = b.a_id;

Oracle

OUTER keyword is optional.

SELECT a.col, b.col FROM a FULL OUTER JOIN b ON a.id = b.a_id;

SQLite

FULL OUTER JOIN added in SQLite 3.39.0 (July 2022). Emulate with UNION on older versions.

SELECT a.col, b.col FROM a FULL OUTER JOIN b ON a.id = b.a_id;