Lesson 2 of 10

Left joins

12 min
4 checks
3 hints
Go to task

Goal

LEFT JOIN and checking with IS NULL

Explanation

LEFT JOIN keeps every row of the left table, the one named after FROM, whether or not it has a match in the right table. When there's no match, all of the right table's columns come back as NULL.

That lets you answer a question INNER JOIN can't: who has not ordered anything? You join customers to orders, then keep only the rows whose right side came back empty: WHERE orders.id IS NULL.

Don't write = NULL. NULL means a value is missing, not a value in its own right, so comparing it with anything is never true, and the query comes back empty without a single error message to warn you.

Your task

Show the name and city of the customers who haven't ordered anything yet.

Checks

4 checks

Columns are name and city
Every customer without orders is listed
No customer with an order is listed
Each customer appears once

Tables in this lesson

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Layan',  'Riyadh'),
  (2, 'Khalid', 'Jeddah'),
  (3, 'Reem',   'Dammam'),
  (4, 'Fahd',   'Riyadh');

INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 120, '2026-09-01'),
  (1,  80, '2026-09-12'),
  (2, 300, '2026-09-03'),
  (3,  45, '2026-09-20'),
  (2,  60, '2026-09-25');

INSERT INTO customers (id, name, city) VALUES (5, 'Sara', 'Jeddah');

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  id         INTEGER PRIMARY KEY,
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty        INTEGER NOT NULL
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Notebook',    15),
  (2, 'Pen',          5),
  (3, 'Bag',         90),
  (4, 'Calculator',  60),
  (5, 'Lamp',       100);

INSERT INTO order_items (order_id, product_id, qty) VALUES
  (1, 3, 1), (1, 1, 2),
  (2, 4, 1), (2, 2, 4),
  (3, 3, 2), (3, 4, 2),
  (4, 1, 3),
  (5, 1, 2), (5, 2, 6);

Left joins

Explanation

Your task

Checks

Hints