🔗 JoinsintermediateCommonly used

How to use LEFT JOIN in SQL

Return all rows from the left table, and matching rows from the right table (NULLs if no match).

Table schema used in examples— users and orders tables

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total DECIMAL);

Basic LEFT JOINAll databases

SELECT
  u.name,
  u.email,
  o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Returns ALL users. For users with no orders, o.total will be NULL.

When to use: When you need all records from the left table regardless of matches.

Find rows with NO match (anti-join)All databases

SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Returns only users who have NO orders. The WHERE o.id IS NULL filters to non-matching rows.

When to use: Finding users who have never ordered, products never purchased, etc.

LEFT JOIN with aggregationAll databases

SELECT
  u.name,
  COUNT(o.id)    AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Counts orders per user. Users with no orders get count=0 and total=0.

Use COUNT(o.id) not COUNT(*) — COUNT(*) counts the NULL row as 1 for users with no orders.

Common Mistakes

→Putting LEFT JOIN filter conditions in WHERE instead of ON — this converts LEFT JOIN to INNER JOIN
→Using COUNT(*) with LEFT JOIN — counts NULL rows. Use COUNT(right_table.id) instead.

Common Mistakes

Related Topics