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
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Not Supported | - | Not supported. Emulate with LEFT JOIN UNION RIGHT JOIN. |
| PostgreSQL | Native | all | OUTER keyword is optional. |
| SQL Server | Native | all | OUTER keyword is optional. |
| Oracle | Native | all | OUTER keyword is optional. |
| SQLite | Native | 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: Native in all listed versions
SQL Server: Native in all listed versions
Oracle: Native in all listed versions
SQLite: Native since 3.39.0
Per-Database Syntax & Notes
MySQL Alternative syntax
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 Native syntax
OUTER keyword is optional.
SELECT a.col, b.col FROM a FULL OUTER JOIN b ON a.id = b.a_id;
SQL Server Native syntax
OUTER keyword is optional.
SELECT a.col, b.col FROM a FULL OUTER JOIN b ON a.id = b.a_id;
Oracle Native syntax
OUTER keyword is optional.
SELECT a.col, b.col FROM a FULL OUTER JOIN b ON a.id = b.a_id;
SQLite Native syntax
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;