// B3.1 · ~30 min · Beginner

Joins, GROUP BY and NULL: predicting what SQL returns

After this lesson you can predict how many rows a join returns, keep a LEFT JOIN from quietly becoming an inner join, and count and filter groups without NULL surprises.

Lesson 1 of 6 in B3 Joins and aggregates

Start of the moduleNext

You will be able to

Predict the row count of an INNER JOIN and a LEFT JOIN on small tables
Put a filter on the right-hand table in ON rather than WHERE when every left row must stay
Choose between COUNT(*) and COUNT(column), WHERE and HAVING, and = and IS NULL

Warm-up · Activity 1 of 7

Warm-up: in an orders table, the column customer_id holds the id of a row in the customers table. What is that column called?

-- customers                 -- orders
-- id | name | city           -- id  | customer_id | amount | status
--  1 | Ana  | Berlin         -- 101 |      1      |   50   | paid
--  2 | Ben  | Munich         -- 102 |      1      |   30   | refunded
--  3 | Cleo | NULL           -- 103 |      2      |   20   | paid
--  4 | Dev  | Berlin

A primary keyA viewAn indexA foreign key

Predict · Activity 2 of 7

Predict before you read on: how many rows does this query return?

-- customers                 -- orders
-- id | name | city           -- id  | customer_id | amount | status
--  1 | Ana  | Berlin         -- 101 |      1      |   50   | paid
--  2 | Ben  | Munich         -- 102 |      1      |   30   | refunded
--  3 | Cleo | NULL           -- 103 |      2      |   20   | paid
--  4 | Dev  | Berlin

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

31245

Practice · Activity 3 of 7
The report should list every customer, with their paid orders if they have any. Same tables. How many rows does this return, and why?
```
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
```
It is an error: you cannot filter the right table of a LEFT JOIN5: WHERE has no effect on a LEFT JOIN4: every customer, with paid orders where they exist2: WHERE runs after the join and drops the rows where o.status is NULL
Practice · Activity 4 of 7
Complete the query so it shows 0, not 1, for customers with no orders. Fill in the argument of COUNT.
```
SELECT c.name, COUNT(____) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
```
COUNT(Your answer) AS order_count
Practice · Activity 5 of 7
Put the clauses in the order PostgreSQL logically processes a SELECT. This order explains why WHERE cannot use COUNT(*) but HAVING can.
1. 1.GROUP BY: form groups and compute aggregates
2. 2.ORDER BY: sort the result
3. 3.SELECT: compute the output columns
4. 4.LIMIT: keep only the first rows
5. 5.FROM and JOIN: build the joined rows
6. 6.HAVING: drop groups that fail the condition
7. 7.WHERE: drop rows that fail the condition
Brain teaser · Activity 6 of 7
Brain teaser: you want every customer who is not in Berlin. Same customers table (Cleo’s city is NULL). Which names does this return?
```
SELECT name
FROM customers
WHERE city <> 'Berlin';
```
Nobody: the NULL makes the whole query NULLAna, Ben, Cleo and DevBen onlyBen and Cleo
Apply · Activity 7 of 7
Mini-task. Using the same customers and orders tables, write one query that lists every customer, including those with no orders, with the number of paid orders and the total paid amount. Customers with nothing paid must show 0 and 0, not NULL. Run it in the editor of the worked example, whose setup.sql creates the two tables; the expected result is Ana 1 50, Ben 1 20, Cleo 0 0, Dev 0 0.
Check your work against this list
- Uses customers LEFT JOIN orders, so all four customers appear
- The status = 'paid' condition is in ON, not in WHERE
- Counts a column of orders (such as o.id), not COUNT(*)
- Wraps SUM in COALESCE(…, 0) so an empty group shows 0
- Every non-aggregated column in SELECT is in GROUP BY

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

The same customers, joined two ways

setup.sql creates the two tables of this lesson; main.sql joins them twice and then looks for an unknown city. Compare the first two results row by row: the LEFT JOIN returns everything the inner join does, plus one NULL-filled row for each customer without an order. On your computer, run it with psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Inner join: only customers who have an order.
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

-- 2. Left join: every customer, NULL where there is no order.
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

-- 3. Customers whose city is unknown.
SELECT name FROM customers WHERE city IS NULL;

setup.sql

CREATE TABLE customers (
  id integer PRIMARY KEY,
  name text,
  city text
);
INSERT INTO customers VALUES
  (1, 'Ana', 'Berlin'),
  (2, 'Ben', 'Munich'),
  (3, 'Cleo', NULL),
  (4, 'Dev', 'Berlin');

CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer REFERENCES customers,
  amount integer,
  status text
);
INSERT INTO orders VALUES
  (101, 1, 50, 'paid'),
  (102, 1, 30, 'refunded'),
  (103, 2, 20, 'paid');

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 name | order_id | amount
------+----------+--------
 Ana  |      101 |     50
 Ana  |      102 |     30
 Ben  |      103 |     20
(3 rows)

 name | order_id | amount
------+----------+--------
 Ana  |      101 |     50
 Ana  |      102 |     30
 Ben  |      103 |     20
 Cleo |          |
 Dev  |          |
(5 rows)

 name
------
 Cleo
(1 row)

JOIN on its own means INNER JOIN: Cleo and Dev have no order, so they are missing from the first result.
Ana has two orders, so she appears twice in both results: a join returns one row per matching pair.
In the LEFT JOIN, Cleo and Dev each appear once, with the columns of orders empty: psql prints NULL as nothing.
The third query uses IS NULL; city = NULL would return no row at all.

Change it and run it

Your code (main.sql)

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 3

Zero, not one

setup.sql has created customers and orders. The query should show every customer with the number of orders they placed, but it reports 1 for Cleo and Dev, who have none. Change what COUNT counts so that they show 0. Keep the LEFT JOIN, the GROUP BY and the ORDER BY.

Your code (main.sql)

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints

Hint 1
After the LEFT JOIN, Cleo still has one row, with NULL in every column of orders. COUNT(*) counts that row.
Hint 2
COUNT(column) counts only the rows where that column is not NULL.
Hint 3
Count a column of orders that every real order has, such as its primary key: COUNT(o.id).

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Cleo and Dev have no orders, yet this reports 1 for each.
SELECT c.name, COUNT(*) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

test.sql

-- test: Every customer is listed, with 0 for those without orders
-- output:
--  name | order_count
-- ------+-------------
--  Ana  |           2
--  Ben  |           1
--  Cleo |           0
--  Dev  |           0
-- (4 rows)

setup.sql

CREATE TABLE customers (
  id integer PRIMARY KEY,
  name text,
  city text
);
INSERT INTO customers VALUES
  (1, 'Ana', 'Berlin'),
  (2, 'Ben', 'Munich'),
  (3, 'Cleo', NULL),
  (4, 'Dev', 'Berlin');

CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer REFERENCES customers,
  amount integer,
  status text
);
INSERT INTO orders VALUES
  (101, 1, 50, 'paid'),
  (102, 1, 30, 'refunded'),
  (103, 2, 20, 'paid');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 3

Not in Berlin, unknown included

List the name and city of every customer who is not known to live in Berlin, ordered by name. A customer whose city is NULL belongs in the list too: we do not know that they live in Berlin. The starter forgets them.

Your code (main.sql)

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints

Hint 1
For Cleo, NULL <> 'Berlin' is NULL, not true, so WHERE drops her row.
Hint 2
Either add the NULL case yourself: city <> 'Berlin' OR city IS NULL.
Hint 3
Or use the comparison that treats NULL as a value: city IS DISTINCT FROM 'Berlin'.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT name, city
FROM customers
WHERE city IS DISTINCT FROM 'Berlin'
ORDER BY name;

Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

SELECT name, city
FROM customers
WHERE city <> 'Berlin'
ORDER BY name;

test.sql

-- test: Ben (Munich) and Cleo (city unknown) are listed, ordered by name
-- output:
--  name |  city
-- ------+--------
--  Ben  | Munich
--  Cleo |
-- (2 rows)

setup.sql

CREATE TABLE customers (
  id integer PRIMARY KEY,
  name text,
  city text
);
INSERT INTO customers VALUES
  (1, 'Ana', 'Berlin'),
  (2, 'Ben', 'Munich'),
  (3, 'Cleo', NULL),
  (4, 'Dev', 'Berlin');

CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer REFERENCES customers,
  amount integer,
  status text
);
INSERT INTO orders VALUES
  (101, 1, 50, 'paid'),
  (102, 1, 30, 'refunded'),
  (103, 2, 20, 'paid');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 3 of 3

Customers who spent at least 50

The query adds up the amount of each customer’s orders. Keep only the customers whose total is at least 50. The test is about groups, so it cannot go in WHERE.

Your code (main.sql)

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints

Hint 1
WHERE runs before the groups exist, so it cannot see SUM.
Hint 2
HAVING comes after GROUP BY and filters whole groups.
Hint 3
Add HAVING SUM(o.amount) >= 50 between GROUP BY and ORDER BY. An alias from SELECT such as total cannot be used there.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(o.amount) >= 50
ORDER BY c.name;

Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Keep only the customers whose total is at least 50.
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

test.sql

-- test: Only Ana, whose orders add up to 80, is listed
-- output:
--  name | total
-- ------+-------
--  Ana  |    80
-- (1 row)

setup.sql

CREATE TABLE customers (
  id integer PRIMARY KEY,
  name text,
  city text
);
INSERT INTO customers VALUES
  (1, 'Ana', 'Berlin'),
  (2, 'Ben', 'Munich'),
  (3, 'Cleo', NULL),
  (4, 'Dev', 'Berlin');

CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer REFERENCES customers,
  amount integer,
  status text
);
INSERT INTO orders VALUES
  (101, 1, 50, 'paid'),
  (102, 1, 30, 'refunded'),
  (103, 2, 20, 'paid');

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

A column name both tables have

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

What psql prints

ERROR:  column reference "id" is ambiguous

Why, and the fix

customers and orders both have a column id, and PostgreSQL will not guess which one you mean. Prefix every such column with its table or alias: SELECT o.id, c.name. Many developers prefix every column in a join, so the query stays clear when a table gains a column later.

A column that is neither grouped nor aggregated

SELECT c.name, c.city, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

What psql prints

ERROR:  column "c.city" must appear in the GROUP BY clause or be used in an aggregate function

Why, and the fix

After GROUP BY, each result row stands for a whole group, so every column in SELECT must either be in GROUP BY or sit inside an aggregate such as COUNT. Add the column to GROUP BY (GROUP BY c.name, c.city), or group by the key, c.id, which decides the rest.

An aggregate in WHERE

SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE SUM(o.amount) >= 50
GROUP BY c.name;

What psql prints

ERROR:  aggregate functions are not allowed in WHERE

Why, and the fix

WHERE picks single rows before any group is formed, so no sum exists yet. A test on a group’s total belongs in HAVING, after GROUP BY: HAVING SUM(o.amount) >= 50.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Key ideas

LEFT JOIN keeps every left row, until WHERE removes it

An inner join returns only rows that match. A LEFT JOIN first does the inner join, then adds each unmatched left row once, with NULL in every right-hand column. The PostgreSQL docs put it plainly: a condition in ON is applied before the join, a condition in WHERE after it. That does not matter for inner joins, but for outer joins it does: WHERE o.status = 'paid' throws away the NULL rows the LEFT JOIN just added. Put right-table filters in ON.

Groups: which rows go in, which groups come out

The logical order is FROM (with joins), WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. WHERE picks the input rows before any aggregate is computed, so it cannot use COUNT or SUM; HAVING filters whole groups afterwards. COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL. After a LEFT JOIN, an unmatched customer is still one row, so COUNT(*) says 1 where COUNT(o.id) correctly says 0. Other aggregates, such as SUM, return NULL for no rows: wrap them in COALESCE(…, 0).

NULL is not a value you can compare with =

NULL means “unknown”, so city = NULL yields NULL, not true, and WHERE keeps only rows where the condition is true. The same catch hides in city <> 'Berlin': a row whose city is NULL fails that test too. Use IS NULL and IS NOT NULL to test for NULL, and IS DISTINCT FROM when you want a comparison that treats NULL as an ordinary, comparable value.

Sources

Last reviewed September 30, 2026

Joins, GROUP BY and NULL: predicting what SQL returns

Warm-up: in an orders table, the column customer_id holds the id of a row in the customers table. What is that column called?

Predict before you read on: how many rows does this query return?

The report should list every customer, with their paid orders if they have any. Same tables. How many rows does this return, and why?

Complete the query so it shows 0, not 1, for customers with no orders. Fill in the argument of COUNT.

Put the clauses in the order PostgreSQL logically processes a SELECT. This order explains why WHERE cannot use COUNT(*) but HAVING can.

Brain teaser: you want every customer who is not in Berlin. Same customers table (Cleo’s city is NULL). Which names does this return?

Build it yourself

The same customers, joined two ways

Exercises

Zero, not one

Not in Berlin, unknown included

Customers who spent at least 50

Common mistakes

A column name both tables have

A column that is neither grouped nor aggregated

An aggregate in WHERE

Exit ticket

Key ideas

LEFT JOIN keeps every left row, until WHERE removes it

Groups: which rows go in, which groups come out

NULL is not a value you can compare with =

Sources