SQL LEFT JOIN

What does SQL LEFT JOIN do?

LEFT JOIN returns all rows from the left table plus matching rows from the right table, filling non-matches with NULLs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LEFT JOIN: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, MariaDB, Snowflake, BigQuery, Redshift

SQL LEFT JOIN Full Explanation

LEFT JOIN is an outer-join operation that preserves every row in the left (first) table and pairs it with rows in the right (second) table that satisfy the join condition. When no matching row exists in the right table, the result set still includes the left-table row and populates the right-table columns with NULL. Because of this behavior, LEFT JOIN is ideal for finding unmatched records, optional relationships, or performing analytics that require complete left-side data. The keyword is interchangeable with LEFT OUTER JOIN in most dialects; OUTER is optional. Execution order: the database scans both tables, evaluates the ON predicate, produces matched rows, then adds NULL-extended rows for left-side records without a match. Performance hinges on proper indexing of join keys. Watch out for unintended row multiplication when the join condition is not selective or when one-to-many relationships are present.

SQL LEFT JOIN Syntax

SELECT <columns>
FROM left_table
LEFT [OUTER] JOIN right_table
  ON left_table.key = right_table.key;

SQL LEFT JOIN Parameters

left_table (table or subquery) - the row-preserved side of the join
right_table (table or subquery) - the nullable side of the join
ON condition (boolean expression) - defines how rows are matched

Example Queries Using SQL LEFT JOIN

-- 1. List all customers and any orders they might have
SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id;

-- 2. Detect products never ordered
SELECT p.product_id, p.product_name
FROM products AS p
LEFT JOIN order_items AS oi
  ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

-- 3. Aggregate with optional data
SELECT d.department, COUNT(e.employee_id) AS num_employees, AVG(s.amount) AS avg_bonus
FROM departments AS d
LEFT JOIN employees AS e ON d.department_id = e.department_id
LEFT JOIN bonuses  AS s ON e.employee_id   = s.employee_id
GROUP BY d.department;

Expected Output Using SQL LEFT JOIN

Every customer appears once; customers without orders show NULL in order columns.
Returns only products absent from order_items.
Produces one row per department with employee counts and average bonuses; departments with no staff show NULL avg_bonus.

Use Cases with SQL LEFT JOIN

Keep all master records while optionally attaching detail records
Identify missing or orphaned records (anti-joins via WHERE right.key IS NULL)
Build dimensional models where fact data may be absent
Generate reports that must list all entities regardless of activity

Common Mistakes with SQL LEFT JOIN

Forgetting the ON clause, leading to a Cartesian product (syntax error in most dialects)
Using WHERE filters on right-table columns that nullify the outer join effect (move filter into ON instead)
Expecting right-side NULLs to satisfy equality checks without IS NULL handling
Assuming LEFT JOIN is always faster than INNER JOIN; outer joins can be slower

First Introduced In

SQL-92

Frequently Asked Questions

What is the main purpose of a LEFT JOIN?

A LEFT JOIN ensures every row from the left table appears in the result, matching rows from the right table when possible and inserting NULLs when no match exists.

Can I replace LEFT JOIN with RIGHT JOIN by swapping table order?

Yes. LEFT JOIN A to B is functionally equivalent to RIGHT JOIN B to A, provided you also adjust selected columns and ON conditions.

Why do my LEFT JOIN results disappear after adding a WHERE clause?

Placing a filter on right-table columns in the WHERE clause effectively turns the query into an INNER JOIN. Move such filters into the ON clause or add an IS NULL check if you need to keep NULL-extended rows.

Does LEFT JOIN harm performance?

Outer joins can require extra work to produce NULL-extended rows, especially on large tables without indexes on join keys. Proper indexing and statistics generally keep performance acceptable.