SqlDatabaseIntermediate

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right.

Review the syntaxStudy the examplesOpen the coding app
SELECT columns FROM left_table LEFT JOIN right_table ON left_table.id = right_table.fk_id;
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Returns all rows from the left table, plus matching rows from the right.

The LEFT JOIN (or LEFT OUTER JOIN) keyword returns all records from the left table (table1), and the matched records from the right table (table2). If there is no match, the result set will contain NULL for every column originating from the right table. This operation is essential for data integrity checks and identifying 'orphaned' records. From a performance standpoint, the database engine usually employs Nested Loop or Hash Join algorithms; thus, ensuring that join keys are indexed is critical to avoid full table scans. An important edge case is 'null-rejection': if a WHERE clause filters columns from the right table, it may inadvertently discard NULL rows, effectively converting the LEFT JOIN into an INNER JOIN. For reliable results when ordering or comparing date columns within joins, it is recommended to use ISO 8601 strings to ensure consistent collation across different database engines.

Quick reference

Syntax

SELECT columns FROM left_table LEFT JOIN right_table ON left_table.id = right_table.fk_id;

Inputs

Parameters

left_tableLeft side table (all rows preserved).
right_tableRight side table (matched rows included).
ON conditionJoin condition defining the relationship.

See it in practice

Examples

1

Basic Left Join with NULL Handling

SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
Output:
[{ "username": "Alice", "order_id": 101 }, { "username": "Bob", "order_id": null }]

Returns all users, including Bob who has no associated orders (resulting in a NULL d).

2

Finding Missing Related Records

SELECT users.username
FROM users
LEFT JOIN profiles ON users.id = profiles.user_id
WHERE profiles.user_id IS NULL;
Output:
[{ "username": "Charlie" }]

Uses a LEFT JOIN combined with a WHERE IS NULL check to identify users who do not have a profile record.

3

Multi-Table Left Join for Aggregation

SELECT p.name, COUNT(r.id) AS review_count
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.name;
Output:
[{ "name": "Laptop", "review_count": 5 }, { "name": "Broken Toaster", "review_count": 0 }]

Ensures products with zero reviews are still included in the count, whereas an INNER JOIN would omit them.

Debug faster

Common Errors

1

LogicError

Cause: Placing a condition for the right-side table in the WHERE clause instead of the ON clause.

Fix: Move the right-table filtering condition into the ON clause to prevent the JOIN from behaving like an INNER JOIN.

SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.status = 'active'; -- Error: Filters out rows where b is NULL
2

PerformanceError

Cause: Joining on columns that lack appropriate indexing, leading to O(N*M) complexity.

Fix: Create an index on the foreign key column of the right table.

SELECT * FROM large_logs LEFT JOIN users ON large_logs.email_string = users.email; -- Missing index on email

Runtime support

Compatibility

All SQL databasesSQL-92 (Standard)

Source: MDN Web Docs

Common questions

Frequently Asked Questions

Returns all rows from the left table, plus matching rows from the right.

able: Left side table (all rows preserved). able: Right side table (matched rows included). ON condition: Join condition defining the relationship.

LogicError: Move the right-table filtering condition into the ON clause to prevent the JOIN from behaving like an INNER JOIN. PerformanceError: Create an index on the foreign key column of the right table.