Learn/SQL/Joins & Data Modification

LEFT JOIN — Include Unmatched Rows

“Which customers never placed an order?” INNER JOIN cannot answer that — it drops users with no orders. LEFT JOIN keeps every row from the left table and uses NULL where there is no match.

Beginner~14 min read

Table Hierarchy & Schema

Database → tables
CREATE TABLE (reference)json
1CREATE TABLE users (
2  user_id   INTEGER PRIMARY KEY,
3  email     VARCHAR(255) NOT NULL UNIQUE,
4  is_active BOOLEAN NOT NULL DEFAULT TRUE
5);
6
7CREATE TABLE orders (
8  order_id     INTEGER PRIMARY KEY,
9  customer_id  INTEGER NOT NULL,
10  status       VARCHAR(20) NOT NULL DEFAULT 'pending',
11  total_amount DECIMAL(10, 2) NOT NULL,
12  FOREIGN KEY (customer_id) REFERENCES users (user_id)
13);

Entity-Relationship Diagram

Users and orders (1:N)

Optional relationship

The ER cardinality marker ||--o{ means a user may have zero or many orders — LEFT JOIN exposes the zero case.

PK/FK Join Flow

Bob has no orders — LEFT JOIN keeps him

Sample Data

usersjson
1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |
ordersjson
1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

[email protected] (user_id 2) has zero orders — the key scenario for LEFT JOIN.

Basic LEFT JOIN

All users with their orders (if any)json
1SELECT u.email, o.order_id, o.total_amount
2FROM users u
3LEFT JOIN orders o ON u.user_id = o.customer_id;
Expected outputjson
1| email | order_id | total_amount |
2|---|---|---|
3| [email protected] | 1001 | 49.99 |
4| [email protected] | 1002 | 12.50 |
5| [email protected] | NULL | NULL |
6| [email protected] | 1003 | 8.00 |

Users With No Orders

Find customers who never orderedjson
1SELECT u.email
2FROM users u
3LEFT JOIN orders o ON u.user_id = o.customer_id
4WHERE o.order_id IS NULL;
Expected outputjson
1| email |
2|---|
3| [email protected] |

INNER vs LEFT JOIN

Join typeKeepsUse when
INNER JOINOnly rows with matches in both tablesYou need related data only
LEFT JOINAll left rows + matches or NULLInclude rows with zero matches on the right

Constraints Reminder

ConstraintRole in LEFT JOIN
PRIMARY KEY (user_id / order_id)Uniquely identifies each row; join anchor
FOREIGN KEY (customer_id)Order must reference a valid users.user_id
NOT NULLRequired columns cannot be missing on INSERT/UPDATE
UNIQUE (email)No duplicate emails in users
DEFAULT (status, is_active)Value applied when column omitted on INSERT

Common Mistakes

  • Putting filter conditions on the right table in WHERE instead of ON — can turn LEFT JOIN into INNER behavior.
  • Using = NULL instead of IS NULL when finding unmatched rows.
  • Assuming NULL means zero — NULL means unknown/no match.

Practice Prompts

  1. List every user email and order status, including users with no orders.
  2. Which inactive user has never placed an order?
  3. Rewrite an INNER JOIN query as LEFT JOIN and compare the row counts.

Try These Tools

SQL Query Explainer

Step through LEFT JOIN logic

Open tool

SQL Data Generator

Generate tables with optional FK gaps

Open tool

Continue Learning

INNER JOIN

Matches only — compare with LEFT

Subqueries

Alternative: NOT IN subquery for no orders

Frequently Asked Questions

What does LEFT JOIN return?
All rows from the left table. Matching right-table columns appear; non-matches show NULL in right-table columns.
Why does Bob appear with NULL order columns?
Bob has no orders. LEFT JOIN still returns his user row and fills order columns with NULL instead of dropping him.
LEFT JOIN vs INNER JOIN?
INNER keeps only matches. LEFT keeps every left row — use it when zero related rows is meaningful (e.g. users who never ordered).
How do I find users with no orders?
LEFT JOIN orders, then WHERE o.order_id IS NULL. This filters to left rows with no right match.
Can I LEFT JOIN multiple tables?
Yes. Chain joins left to right: FROM users u LEFT JOIN orders o … LEFT JOIN … Each preserves all rows from the leftmost table in that chain.