LEFT JOINJoins
Buy Me A Coffee

Returns all rows from the left table and matching rows from the right; NULLs fill unmatched right-side columns.

Returns all rows from the left table and matched rows from the right; non-matching right rows produce NULLs. Essential for finding records with or without a related match (e.g., orders with no invoice). NULLs in the result can be misleading -- distinguish between no match and legitimate missing value.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).
PostgreSQL ✓ Supported all OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).
SQL Server ✓ Supported all OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).
Oracle ✓ Supported all OUTER keyword is optional. Legacy syntax: WHERE a.id = b.a_id(+)
SQLite ✓ Supported all OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).

Details

Use LEFT JOIN to keep all records from the left table even when no match exists on the right.

Standard Syntax

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

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).

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

PostgreSQL

OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).

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

SQL Server

OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).

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

Oracle

OUTER keyword is optional. Legacy syntax: WHERE a.id = b.a_id(+)

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

SQLite

OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).

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