Joins Updated November 2026

LEFT JOIN

LEFT JOIN keeps every row from the left (first) table and adds columns from the right table where the ON condition matches, or NULL: SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id lists all customers, even those without orders. WHERE o.id IS NULL keeps only those.

Syntax

SELECT columns FROM left_table l LEFT [OUTER] JOIN right_table r ON l.key = r.key;

Examples

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Result: name | order_id Maria | 1 Maria | 7 Noor | NULL

Noor has no orders, so her row survives with a NULL order_id.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Result: Customers who never ordered

The anti-join pattern: keep only rows where the right side is NULL.

SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.name;

Result: Every department, with 0 for empty ones

COUNT(e.id) counts matches; COUNT(*) would give 1 for an empty department.

Common mistakes

Filtering the right table in WHERE (WHERE o.status = 'paid') removes the NULL rows and silently turns the LEFT JOIN into an INNER JOIN; put that condition in the ON clause instead.
Using COUNT(*) after a LEFT JOIN counts unmatched left rows as 1; count a right-table column to get 0.
Swapping the tables: the table written first is the one whose rows are all kept.

Practice LEFT JOIN

These drills check your answer on live data, free, no account needed.

Customers who never orderedReturn the names of customers who have no orders at all.
Everyone with a departmentReturn every employee's name and department name. Employees without a department must still appear, with NULL as the department name.
Products never soldReturn the names of products that appear on no order line.

Questions

What is the difference between LEFT JOIN and RIGHT JOIN?

RIGHT JOIN keeps all rows of the second table instead. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables, which is why most people only use LEFT JOIN. SQLite supports RIGHT JOIN since version 3.39.

Is LEFT OUTER JOIN the same as LEFT JOIN?

Yes. OUTER is optional and changes nothing.

How do I find rows in one table that are not in another?

LEFT JOIN and filter WHERE right.key IS NULL, or use NOT EXISTS (SELECT 1 FROM right WHERE right.key = left.key). Both are reliable; NOT IN is risky with NULLs.

INNER JOININNER JOIN returns rows that have a match in both tables. Syntax with ON, joining three tables, aliases, columns from both sides, and the common pitfalls.
FULL OUTER JOINFULL OUTER JOIN returns all rows from both tables, matched where possible and NULL elsewhere. Syntax, rows present on only one side, and the UNION workaround.
EXISTSEXISTS is true when the subquery returns at least one row. Use it for has-any checks and NOT EXISTS for rows with no match, the safe alternative to NOT IN.
IS NULLNULL means unknown, so = NULL never matches. Use IS NULL and IS NOT NULL to find or exclude missing values, and see how NULL behaves in counts and sorting.