Dana never paid, and finance never noticed

See the problem

Finance keeps a report of how much each order has been paid, so they can chase what’s owed. This query makes it:

The payments report’s query

SELECT o.order_id, o.total, p.amount AS paid
FROM orders o
JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
ORDER BY o.order_id;

For weeks it listed every order that wasn’t cancelled, each one paid in full. Then, on 21 October, Dana ordered a desk lamp, order 115, and didn’t pay.

A week later, nobody had followed it up. Order 115 wasn’t on the report at all, and no error said why.

Define the task

List every order that isn’t cancelled: order_id, total, and paid, the sum of its payments, or 0 if there are none yet. By order_id.

• A payment has an order_id and an amount. An order can have no payment yet, or several: some customers pay in parts.
• Leave out cancelled orders.

Pause and think

⏸ Pause and think

JOIN pairs each order with each payment that has the same order_id. Order 115 has no payment. What can it be paired with?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

An inner JOIN keeps a row only when it finds a partner. Order 115 has none, so it’s gone.

Hint 2 of 3

LEFT JOIN keeps every row of the left table, orders, and fills the payment’s columns with NULL when there’s no partner.

Hint 3 of 3

An order paid in two parts pairs with both payments, so it’s on two rows. GROUP BY o.order_id, o.total with sum(p.amount) folds them into one.

Understand the idea

A join pairs rows. FROM orders o JOIN payments p ON p.order_id = o.order_id makes one row for each order and payment whose order_ids match. ON says what counts as a match.

An inner JOIN keeps only rows with a partner. An order with no payment has no pair, so it’s gone. Plain JOIN means INNER JOIN.

LEFT JOIN keeps every row of the left table. The left table is the one before LEFT JOIN. A row with no partner stays once, with NULL in every column from the right table. Here that’s order 115, with p.amount NULL.

A row with many partners is repeated. If Hugo pays for order 114 in two parts, it pairs with both payments, so it’s on two rows, and its total shows twice. sum(o.total) after this join would count it twice.

Watch the report run on five orders: Okay’s inner JOIN first, then LEFT JOIN. Then type your own payments, with an order paid in three parts, or none paid at all:

The payments report, step by step

⤢ Full view

1. FROM orders
2. LEFT JOIN payments
3. WHERE status <> 'cancelled'
4. GROUP BY order_id, total
5. SELECT …, coalesce(sum(amount), 0)
6. ORDER BY order_id

payments

order_id	amount
112	9.99
113	49.98
114	100.00
114	42.99

orders

order_id	status	total
107	cancelled	136.00
112	delivered	9.99
113	placed	49.98
114	placed	142.99
115	placed	24.99

Step 1 of 7. FROM orders reads 5 orders. Beside them is the payments table, with 4 payments. Question: Order 114 has two payments. After the join, how many rows is order 114 on?

What happens next? Order 114 has two payments. After the join, how many rows is order 114 on?

123Just show me

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT o.order_id, o.total, coalesce(sum(p.amount), 0) AS paid
2FROM orders o
3LEFT JOIN payments p ON p.order_id = o.order_id
4WHERE o.status <> 'cancelled'
5GROUP BY o.order_id, o.total
6ORDER BY o.order_id;

Try your own input

Payments: order_id, amount112, 9.99 113, 49.98 114, 100.00 114, 42.99Show it

Find rows with no partner with IS NULL. FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL finds Dana, the one customer with no orders. Test a column that’s never NULL in a real row, like the right table’s key.

Filter the right table in ON, not WHERE. After a LEFT JOIN, a row with no partner has NULL in the right table’s columns. So WHERE p.method = 'card' drops it, and the LEFT JOIN acts like an inner one. To keep every order and match only card payments, write LEFT JOIN payments p ON p.order_id = o.order_id AND p.method = 'card'. A filter on the left table, like o.status <> 'cancelled', is fine in WHERE.

count(*) counts the row with no partner. After a LEFT JOIN, an order with no payment still has one row, so count(*) gives 1. count(p.payment_id) skips the NULL and gives 0.

Climb the ladder

All three pair orders with payments. What changes is what happens to an order with no payment, and to one with two.

Okay

JOIN payments

The idea: Pair each order with its payment.

It looks right because every order in the store’s data that isn’t cancelled has exactly one payment.

SELECT o.order_id, o.total, p.amount AS paid
FROM orders o
JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
ORDER BY o.order_id;

Gets right

Every order paid once, like the store’s

Gets wrong

An order with no payment yet

Why

An inner JOIN drops a row with no partner, so an unpaid order isn’t on the report.

Where it breaks: Dana orders and hasn’t paid, and order 115 isn’t on the report. Okay passes 1 of the 3 checks below.

Better

LEFT JOIN payments

The idea: Keep every order, with NULL when there’s no payment, and coalesce it to 0.

The gap in Okay: the inner JOIN dropped order 115.

Highlighted lines are new in this rung.

SELECT o.order_id, o.total, coalesce(p.amount, 0) AS paid
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
ORDER BY o.order_id;

Gets right

An order with no payment yetwas Every order paid once, like the store’s

Gets wrong

An order paid in parts

Why

A row with two partners is repeated, so the order shows up once per payment.

What changed from the previous rung

SELECT o.order_id, o.total, p.amount AS paid
SELECT o.order_id, o.total, coalesce(p.amount, 0) AS paid
FROM orders o
JOIN payments p ON p.order_id = o.order_id
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
ORDER BY o.order_id;

Where it breaks: Hugo pays for order 114 in two parts, 100.00 by card and then 42.99 by UPI. Order 114 is on two rows, each with part of the money. Better passes 2 of 3.

Best

GROUP BY with sum(p.amount)

The idea: One group per order, adding up its payments.

The gap in Better: one row per payment, not one per order.

Highlighted lines are new in this rung.

SELECT o.order_id, o.total, coalesce(sum(p.amount), 0) AS paid
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.order_id, o.total
ORDER BY o.order_id;

Gets right

Every checkwas An order with no payment yet

Gets wrong

Nothing the checks try

Why

GROUP BY folds the repeated rows back into one, and sum adds the payments.

What changed from the previous rung

SELECT o.order_id, o.total, coalesce(p.amount, 0) AS paid
SELECT o.order_id, o.total, coalesce(sum(p.amount), 0) AS paid
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.order_id, o.total
ORDER BY o.order_id;

When Best isn’t best: you may need to add up two tables linked to orders at once, like items and payments. A join repeats each item once per payment, and the sums grow. Sum each table on its own first, then join the sums. The subqueries lesson shows how.

Write it yourself

Now write it in the page. Press Run query to see your result and check it. Postgres runs in your browser, so nothing is sent anywhere. Each check changes a few rows first, then they’re put back.

Try Okay from the ladder first, and open a failed check to see what it changed.

The payments report

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)
• payments (payment_id, order_id, paid_on, method, amount)

-- Each order that isn't cancelled, with how much has been paid. Some orders have no payment yet. SELECT order_id, amount FROM payments;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Dana orders a desk lamp and hasn’t paid yet (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 4, '2026-10-21', 'placed', 24.99);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 3, 1, 24.99);

3. ○Not run yet: Hugo pays for order 114 in two parts: 100.00 by card, then 42.99 by UPI (row order matters)
   What this check changes

   UPDATE payments SET amount = 100.00 WHERE payment_id = 13;
   INSERT INTO payments (payment_id, order_id, paid_on, method, amount)
   VALUES (14, 114, '2026-10-21', 'upi', 42.99);

Each check runs on its own copy of the data, so your query can’t change the real tables.

Compare

Way	Gets right	Gets wrong	Use it when
Okay: JOIN payments	Orders paid exactly once	An order with no payment: it’s gone	Every row surely has a partner
Better: LEFT JOIN	An order with no payment: 0	An order paid in parts: one row per payment	A row has at most one partner
Best: LEFT JOIN with GROUP BY and sum	Every check	Nothing the checks try	A row can have no partner, one, or many

Common mistakes and edge cases

!A WHERE on the right table undoes LEFT JOIN

After a LEFT JOIN, a row with no partner has NULL in the right table’s columns. A condition like p.method = 'card' is NULL for it, so WHERE drops it. Put the condition in ON. Practice problem 3.

• count(*) after LEFT JOIN counts the row with no partner. Count a column from the right table, like count(e.employee_id). Practice problem 1.
• A row with many partners is repeated. Sums of the left table’s columns grow: after joining payments, sum(o.total) counts order 114 twice. Check how many rows the join gives.
• Find rows with no partner with LEFT JOIN ... WHERE right.key IS NULL. An inner JOIN can’t find them: it already dropped them. Practice problem 2.
• Name the table for every column both tables have. order_id is in both, so SELECT order_id stops with column reference "order_id" is ambiguous. Write o.order_id.
• RIGHT JOIN is LEFT JOIN the other way round. FROM payments p RIGHT JOIN orders o keeps every order too. Most people swap the tables and write LEFT JOIN, so the table they report on comes first.

Say it in the interview

Questions to ask first

• Can a row have no partner, like an order with no payment yet? Should it be in the result?
• Can a row have more than one partner, like an order paid in parts?
• What should a missing value show: NULL, 0, or nothing?

A 60-second walkthrough

“Orders are what I report on, so they go on the left. An order may have no payment yet, so I use LEFT JOIN to keep it; its amount comes back NULL. An order can also be paid in parts, which repeats it once per payment. So I GROUP BY o.order_id, o.total and add sum(p.amount). An order with no payment sums to NULL, so I wrap it in coalesce with 0. The cancelled filter is on orders, the left table, so it’s safe in WHERE. A filter on payments would go in ON.”

Likely follow-ups

• What’s the difference between JOIN and LEFT JOIN? JOIN keeps only rows with a partner. LEFT JOIN keeps every left row, with NULLs when there’s none.
• How do you find customers with no orders? LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL, or NOT EXISTS.
• Why did your totals double after a join? A row with two partners is repeated. Group back to one row per order, or sum each table before joining.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. Headcount for the staff page (easy)

The staff page lists each department with its headcount: name and headcount, biggest first, and by name when two are the same size. A department with nobody in it yet shows 0.

Hint 1 of 3

A department with nobody in it still gets one row from LEFT JOIN, with NULL for the employee.

Hint 2 of 3

count(*) counts that row. count(e.employee_id) skips the NULL.

Hint 3 of 3

Two departments the same size tie on headcount. Add d.name to the ORDER BY.

Headcount for the staff page

Ctrl + Enter runs

The tables

• departments (department_id, name)
• employees (employee_id, name, department_id, manager_id, salary, hired_on)

SELECT d.name, e.name AS employee FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Marketing opens, with nobody in it yet (row order matters)
   What this check changes

   INSERT INTO departments (department_id, name) VALUES (5, 'Marketing');

3. ○Not run yet: Kai joins Support, so it has as many people as Sales (row order matters)
   What this check changes

   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Tanaka', 2, 5, 4700.00, '2026-11-02');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

count(*)

The idea: Count each department’s rows.

Okay passes 1 of 3. When Marketing opens with nobody in it yet, Okay says it has 1 person.

SELECT d.name, count(*) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY headcount DESC;

Gets right

Departments that all have staff, as in the store

Gets wrong

A department with nobody yet

Why

LEFT JOIN gives an empty department one row, with NULL for the employee, and count(*) counts it.

Better

count(e.employee_id)

The idea: Count only real employees.

The gap in Okay: count(*) counted the NULL row.

Highlighted lines are new in this rung.

SELECT d.name, count(e.employee_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY headcount DESC;

Gets right

A department with nobody yet

Gets wrong

Two departments the same size

Why

ORDER BY headcount DESC leaves a tie in whatever order the groups came out.

What changed from the previous rung

SELECT d.name, count(*) AS headcount
SELECT d.name, count(e.employee_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY headcount DESC;

Where it breaks: Kai joins Support, so Support and Sales both have 3. Better lists Support, then Sales, the order the groups came out in. Better passes 2 of 3.

Best

ORDER BY headcount DESC, d.name

The idea: Break the tie by name.

The gap in Better: a tie on headcount.

Highlighted lines are new in this rung.

SELECT d.name, count(e.employee_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY headcount DESC, d.name;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

Department names are UNIQUE, so every row has one place.

What changed from the previous rung

SELECT d.name, count(e.employee_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.name
ORDER BY headcount DESC;
ORDER BY headcount DESC, d.name;

When Best isn’t best: sizes change every month, so the page reorders. If people look for their department in a fixed place, sort by d.department_id instead.

2. Products nobody has bought (medium)

The shop puts products nobody has bought on sale. List them: product_id and name, by product_id. A cancelled order isn’t a sale. On the store’s data, every product has sold, so the list is empty.

Hint 1 of 3

An inner JOIN drops a product with no order_items, so WHERE i.order_id IS NULL after it can never find one.

Hint 2 of 3

LEFT JOIN order_items, then keep the rows where i.order_id IS NULL.

Hint 3 of 3

A product whose only order was cancelled still has an order_items row. LEFT JOIN orders too, GROUP BY the product, and keep the groups where count(*) FILTER (WHERE o.status <> 'cancelled') is 0.

Products nobody has bought

Ctrl + Enter runs

The tables

• products (product_id, name, category, price)
• orders (order_id, customer_id, order_date, status, total)
• order_items (order_id, product_id, quantity, unit_price)

SELECT p.product_id, p.name, i.order_id FROM products p JOIN order_items i ON i.product_id = p.product_id ORDER BY p.product_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: every product has sold (row order matters)
2. ○Not run yet: The shop adds a phone stand (row order matters)
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Phone stand', 'electronics', 14.50);

3. ○Not run yet: The shop adds a phone stand, and Dana orders one, then cancels (row order matters)
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Phone stand', 'electronics', 14.50);
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 4, '2026-10-21', 'cancelled', 14.50);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 9, 1, 14.50);

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

JOIN, then WHERE i.order_id IS NULL

The idea: Look for the products whose order_id is missing.

Okay passes 1 of 3. When the shop adds a phone stand, the list is still empty.

SELECT p.product_id, p.name
FROM products p
JOIN order_items i ON i.product_id = p.product_id
WHERE i.order_id IS NULL
ORDER BY p.product_id;

Gets right

No unsold product, as in the store

Gets wrong

A product nobody has ordered

Why

The inner JOIN has already dropped every product with no order_items, so no row has a NULL order_id.

Better

LEFT JOIN, then WHERE i.order_id IS NULL

The idea: Keep every product, and pick the ones with no partner.

The gap in Okay: the inner JOIN.

Highlighted lines are new in this rung.

SELECT p.product_id, p.name
FROM products p
LEFT JOIN order_items i ON i.product_id = p.product_id
WHERE i.order_id IS NULL
ORDER BY p.product_id;

Gets right

A product nobody has ordered

Gets wrong

A product whose only order was cancelled

Why

A cancelled order keeps its order_items rows, so the product has a partner.

What changed from the previous rung

SELECT p.product_id, p.name
FROM products p
JOIN order_items i ON i.product_id = p.product_id
LEFT JOIN order_items i ON i.product_id = p.product_id
WHERE i.order_id IS NULL
ORDER BY p.product_id;

Where it breaks: Dana orders the new phone stand, then cancels. The phone stand has an order_items row, so Better leaves it out, but nobody bought it. Better passes 2 of 3.

Best

GROUP BY, HAVING count(*) FILTER (...) = 0

The idea: Count each product’s sales that weren’t cancelled, and keep the ones with none.

The gap in Better: a cancelled order counted as a sale.

Highlighted lines are new in this rung.

SELECT p.product_id, p.name
FROM products p
LEFT JOIN order_items i ON i.product_id = p.product_id
LEFT JOIN orders o ON o.order_id = i.order_id
GROUP BY p.product_id, p.name
HAVING count(*) FILTER (WHERE o.status <> 'cancelled') = 0
ORDER BY p.product_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

A product with no order_items, or only cancelled ones, counts 0 sales.

What changed from the previous rung

SELECT p.product_id, p.name
FROM products p
LEFT JOIN order_items i ON i.product_id = p.product_id
WHERE i.order_id IS NULL
LEFT JOIN orders o ON o.order_id = i.order_id
GROUP BY p.product_id, p.name
HAVING count(*) FILTER (WHERE o.status <> 'cancelled') = 0
ORDER BY p.product_id;

When Best isn’t best: NOT EXISTS with a subquery reads closer to the question, “no sale exists”, and stops at a product’s first sale. The subqueries lesson covers it.

3. The raise budget (medium)

This year, everyone paid under 7,000 gets a raise. For the budget, list every department with how many people that is, 0 for none: name and raises, by department_id.

Hint 1 of 3

A department with nobody in it has e.salary NULL, so WHERE e.salary < 7000 is NULL for it, and WHERE drops it.

Hint 2 of 3

Adding OR e.employee_id IS NULL keeps an empty department, but not one whose people all earn 7,000 or more.

Hint 3 of 3

Put the condition in ON: LEFT JOIN employees e ON e.department_id = d.department_id AND e.salary < 7000.

The raise budget

Ctrl + Enter runs

The tables

• departments (department_id, name)
• employees (employee_id, name, department_id, manager_id, salary, hired_on)

SELECT d.name, e.name AS employee, e.salary FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id ORDER BY d.department_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Marketing opens, with nobody in it yet (row order matters)
   What this check changes

   INSERT INTO departments (department_id, name) VALUES (5, 'Marketing');

3. ○Not run yet: Marketing opens, and Kai joins it on 8,000 (row order matters)
   What this check changes

   INSERT INTO departments (department_id, name) VALUES (5, 'Marketing');
   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Tanaka', 5, 1, 8000.00, '2026-11-02');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

WHERE e.salary < 7000

The idea: Join, then keep the people under 7,000.

Okay passes 1 of 3. When Marketing opens with nobody in it yet, it’s missing instead of 0.

SELECT d.name, count(e.employee_id) AS raises
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.salary < 7000
GROUP BY d.department_id, d.name
ORDER BY d.department_id;

Gets right

Departments that all have someone under 7,000, as in the store

Gets wrong

A department with nobody yet

Why

The empty department’s e.salary is NULL, so WHERE drops it, and the LEFT JOIN acts like an inner one.

Better

WHERE e.salary < 7000 OR e.employee_id IS NULL

The idea: Also keep the row of an empty department.

The gap in Okay: WHERE dropped the NULL row.

Highlighted lines are new in this rung.

SELECT d.name, count(e.employee_id) AS raises
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.salary < 7000 OR e.employee_id IS NULL
GROUP BY d.department_id, d.name
ORDER BY d.department_id;

Gets right

A department with nobody yet

Gets wrong

A department whose people all earn 7,000 or more

Why

Kai’s row fails the WHERE, and it was Marketing’s only row, so Marketing disappears.

What changed from the previous rung

SELECT d.name, count(e.employee_id) AS raises
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.salary < 7000
WHERE e.salary < 7000 OR e.employee_id IS NULL
GROUP BY d.department_id, d.name
ORDER BY d.department_id;

Where it breaks: Marketing opens, and Kai joins it on 8,000. Marketing should show 0, but it’s missing. Better passes 2 of 3.

Best

LEFT JOIN ... ON ... AND e.salary < 7000

The idea: Match only the people under 7,000, and keep every department.

The gap in Better: filtering after the join.

Highlighted lines are new in this rung.

SELECT d.name, count(e.employee_id) AS raises
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id AND e.salary < 7000
GROUP BY d.department_id, d.name
ORDER BY d.department_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

ON decides which employees are partners. A department with none keeps its one row, with NULL, and count(e.employee_id) gives 0.

What changed from the previous rung

SELECT d.name, count(e.employee_id) AS raises
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.salary < 7000 OR e.employee_id IS NULL
LEFT JOIN employees e ON e.department_id = d.department_id AND e.salary < 7000
GROUP BY d.department_id, d.name
ORDER BY d.department_id;

When Best isn’t best: a plain LEFT JOIN with count(*) FILTER (WHERE e.salary < 7000) gives the same answer. Then one query can count several bands, like under 7,000 and 7,000 or more.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputDana (customer 4) has no orders. What does this give?

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

   No rowsOne row: Dana Cohen, NULLOne row: Dana Cohen, 0Postgres stops with an error

   Check answer
2. Predict the outputOrder 107 was cancelled and has no payment. What does this give?

   SELECT count(*), count(p.payment_id)
   FROM orders o
   LEFT JOIN payments p ON p.order_id = o.order_id
   WHERE o.order_id = 107;

   0 and 01 and 11 and 00 and 1

   Check answer
3. Spot the bugThis should list every order, with its card payment, or NULL when there’s none. What happens?

   SELECT o.order_id, p.amount
   FROM orders o
   LEFT JOIN payments p ON p.order_id = o.order_id
   WHERE p.method = 'card'
   ORDER BY o.order_id;

   Every order is listedPostgres stops with an errorOrders with no card payment show NULLOnly orders with a card payment are listed

   Check answer
4. Predict the outputHugo pays for order 114, which totals 142.99, in two parts. What does this give?

   SELECT sum(o.total)
   FROM orders o
   JOIN payments p ON p.order_id = o.order_id
   WHERE o.order_id = 114;

   285.98142.99100.00242.99

   Check answer
5. Multiple choiceWhich finds the customers who have never ordered?

   FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULLFROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULLFROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.order_id IS NULLFROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id WHERE c.customer_id IS NULL

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1What does an inner JOIN do with a row that has no partner?Show answer
• Card 2What does LEFT JOIN do with a left row that has no partner?Show answer
• Card 3What happens to a row with two partners in a join?Show answer
• Card 4After a LEFT JOIN, where does a condition on the right table go?Show answer
• Card 5How do you find rows with no partner, like customers with no orders?Show answer
• Card 6After a LEFT JOIN, count(*) or count(column)?Show answer

Add 6 cards to my review deck

Cheat sheet

• JOIN (inner) keeps only rows with a partner. LEFT JOIN keeps every left row, with NULLs for the right table.
• A row with many partners is repeated: group back to one row, or sum before joining.
• Filter the right table in ON, and the left table in WHERE.
• After LEFT JOIN, count a right-table column, not *.
• No partner: LEFT JOIN ... WHERE right.key IS NULL.

Go deeper

• In the PostgreSQL docs: joined tables and the tutorial on joins between tables.
• Try your own payments: the payments report on the SQL visualizer, or any query of your own, step by step. “Show the steps” under each editor draws a join too, with the NULL cells marked.
• Next on the SQL roadmap: the whole map, from your first SELECT to window functions.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Say which rows an inner JOIN and a LEFT JOIN keep
• Spot a row repeated by a join, and fold it back with GROUP BY
• Find rows with no partner with LEFT JOIN and IS NULL
• Put a filter on the right table in ON, and count a right-table column after LEFT JOIN

✓Mark as completeI already know this

Status: Not started