LEFT JOIN

Joins
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

Filter by Database
SQL LEFT JOIN Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).
PostgreSQL Native all OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).
SQL Server Native all OUTER keyword is optional (LEFT JOIN = LEFT OUTER JOIN).
Oracle Native all OUTER keyword is optional. Legacy syntax: WHERE a.id = b.a_id(+)
SQLite Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;