A guest checked out, and nobody got a reminder

See the problem

Every Monday the shop emails a reminder to customers who have never checked out. This query picks them:

The reminder’s query

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM events
  WHERE event_type = 'checkout'
)
ORDER BY customer_id;

For weeks it found Chen and Dana, the two customers with no checkout. Then, on 21 October, a visitor checked out as a guest, without signing in. A guest’s event has no customer_id: it’s NULL.

That Monday the list was empty. Chen and Dana still had never checked out, but nobody got a reminder, and no error said why.

Define the task

List every customer who has never checked out: customer_id and name, by customer_id.

• A checkout is an event with event_type = 'checkout'.
• Guests’ checkouts have customer_id NULL. They don’t make any customer a buyer.

Pause and think

⏸ Pause and think

Chen is customer 3. The subquery’s list is now 1, 2, 5, 7, 6, 8 and NULL. Is 3 in that list? Is it surely not in it?

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

Hints open one at a time:

Hint 1 of 3

NULL means unknown. Is 3 equal to an unknown value? Nobody can say.

Hint 2 of 3

3 NOT IN (1, 2, NULL) checks 3 <> 1 AND 3 <> 2 AND 3 <> NULL. The last part is NULL, not true.

Hint 3 of 3

Keep the NULL out of the list: add AND customer_id IS NOT NULL to the subquery.

Understand the idea

NULL means unknown. It isn’t 0 or an empty text. A guest’s customer_id is NULL because we don’t know who they are.

A comparison with NULL gives NULL. Is an unknown value equal to 3? Unknown. So NULL = 3, NULL <> 3 and even NULL = NULL are all NULL, not true or false. SQL has three answers: true, false and NULL (unknown).

WHERE keeps a row only when its condition is true. False and NULL rows are both dropped. That’s why rows with NULL disappear without an error.

a	b	a AND b	a OR b	NOT a
true	NULL	NULL	true	false
false	NULL	false	NULL	true
NULL	NULL	NULL	NULL	NULL

AND is false if either side is false, whatever the other is. OR is true if either side is true. Otherwise, a NULL makes the answer NULL.

NOT IN with a NULL in its list is never true. 3 NOT IN (1, 2, NULL) means 3 <> 1 AND 3 <> 2 AND 3 <> NULL: true, true and NULL, which makes NULL. For a customer who is in the list, it’s false. So no row passes, and the reminder list is empty. IN doesn’t have this problem: 3 IN (3, NULL) is true.

Watch the reminder’s query build its list, then see what Okay’s list, with the NULL still in it, does to each customer. Then type your own checkouts, with more guests, or none:

The reminder list, step by step

⤢ Full view

1. FROM events
2. WHERE event_type = 'checkout'
3. AND customer_id IS NOT NULL
4. FROM customers
5. WHERE customer_id NOT IN (…)
6. SELECT customer_id, name
7. ORDER BY customer_id

checkouts

customer_id
1
2
2
5
7
6
8
NULL

Step 1 of 6. The subquery runs first. It reads the 8 checkouts: one is a guest’s, whose customer_id is NULL.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id, name
2FROM customers
3WHERE customer_id NOT IN (
4  SELECT customer_id
5  FROM events
6  WHERE event_type = 'checkout'
7    AND customer_id IS NOT NULL
8)
9ORDER BY customer_id;

Try your own input

Checkouts: customer_id, or NULL for a guest1 2 2 5 7 6 8 NULLShow it

Test for NULL with IS NULL and IS NOT NULL. manager_id = NULL is always NULL, so WHERE manager_id = NULL finds no rows, with no error. WHERE manager_id IS NULL finds Meera, who has no manager.

IS DISTINCT FROM is <> that treats NULL as a value. NULL IS DISTINCT FROM 2 is true, and NULL IS DISTINCT FROM NULL is false. It never gives NULL.

coalesce(x, y) gives the first value that isn’t NULL. NULL + 1 is NULL, and so is 'customer ' || NULL. Wrap the value, coalesce(customer_id, 0), when you need a real one.

Climb the ladder

All three keep the customers who aren’t in the list of checkouts. What changes is what happens to the guest’s NULL in that list.

Okay

NOT IN (SELECT customer_id ...)

The idea: Keep customers whose id isn’t in the list of checkouts.

It looks right because every checkout in the store’s data has a customer_id.

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM events
  WHERE event_type = 'checkout'
)
ORDER BY customer_id;

Gets right

Checkouts from signed-in customers only, like the store’s

Gets wrong

A guest’s checkout: the list has a NULL

Why

With a NULL in the list, NOT IN is false or NULL for every customer, never true.

Where it breaks: a guest checks out, and the list is empty. Okay passes 1 of the 3 checks below.

Better

SELECT coalesce(customer_id, 0)

The idea: Turn the guest’s NULL into 0, so the list has no NULL.

The gap in Okay: the NULL in the list.

Highlighted lines are new in this rung.

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT coalesce(customer_id, 0)
  FROM events
  WHERE event_type = 'checkout'
)
ORDER BY customer_id;

Gets right

Guests’ checkoutswas Checkouts from signed-in customers only, like the store’s

Gets wrong

A real customer whose id is 0

Why

0 stands in for unknown, but 0 can be a real customer_id, and then that customer counts as a buyer.

What changed from the previous rung

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  SELECT coalesce(customer_id, 0)
  FROM events
  WHERE event_type = 'checkout'
)
ORDER BY customer_id;

Where it breaks: Ivy’s account moves over from the old shop as customer 0, and a guest checks out. The list now holds 0, so Ivy, who never checked out, gets no reminder. Better passes 2 of 3.

Best

AND customer_id IS NOT NULL

The idea: Leave the guests out of the list.

The gap in Better: a made-up value that a real row can have.

Highlighted lines are new in this rung.

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM events
  WHERE event_type = 'checkout'
    AND customer_id IS NOT NULL
)
ORDER BY customer_id;

Gets right

Every checkwas Guests’ checkouts

Gets wrong

Nothing the checks try

Why

A guest’s checkout says nothing about any customer, so it doesn’t belong in the list.

What changed from the previous rung

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT coalesce(customer_id, 0)
  SELECT customer_id
  FROM events
  WHERE event_type = 'checkout'
    AND customer_id IS NOT NULL
)
ORDER BY customer_id;

When Best isn’t best: every NOT IN (subquery) needs that IS NOT NULL, and it’s easy to forget. NOT EXISTS can’t have the problem: WHERE NOT EXISTS (SELECT 1 FROM events e WHERE e.customer_id = c.customer_id AND e.event_type = 'checkout'), with FROM customers c. The subqueries lesson covers it, and the joins lesson shows the same list as a LEFT JOIN.

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 reminder list

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• events (event_id, customer_id, event_type, occurred_at)

-- Customers who have never checked out. A guest's checkout has customer_id NULL. SELECT customer_id, event_type FROM events WHERE event_type = 'checkout';

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: A guest checks out without signing in (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, NULL, 'checkout', '2026-10-21 10:00:00');

3. ○Not run yet: Ivy’s account moves over from the old shop as customer 0, and a guest checks out (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (0, 'Ivy Mensah', 'ivy@example.com', 'Accra', '2026-10-21');
   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, NULL, 'checkout', '2026-10-21 10:00:00');

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: NOT IN (subquery)	Lists with no NULL	A NULL in the list: no rows at all	The column is NOT NULL
Better: coalesce(customer_id, 0)	Guests’ checkouts	A real row with the made-up value	Never: a made-up value can clash with a real one
Best: AND customer_id IS NOT NULL	Every check	Nothing the checks try	A NOT IN list from a column that can be NULL

Common mistakes and edge cases

!NULL rows disappear without an error

WHERE drops a row whose condition is NULL, just as it drops one that’s false. Any time a column can be NULL, ask what <>, NOT IN or NOT (...) does with it.

• = NULL finds nothing. Write IS NULL.
• x <> 2 drops the rows where x is NULL. If they should stay, write x IS DISTINCT FROM 2. Practice problem 2.
• OR x IS NULL needs brackets. department_id = 1 AND manager_id <> 2 OR manager_id IS NULL runs AND first, so every employee with no manager gets in, from any department. Write AND (manager_id <> 2 OR manager_id IS NULL).
• NULL sorts last going up, and first going down. Say where you want it: ORDER BY manager_id NULLS FIRST. Practice problem 2.
• Aggregates skip NULLs. count(customer_id) skips the visitor, count(*) doesn’t: 19 and 20 on the store’s events. And max() of no rows is NULL, so comparing with it gives NULL. Practice problem 3.
• GROUP BY and DISTINCT put all the NULLs together, as one group or one row, even though NULL = NULL isn’t true.

Say it in the interview

Questions to ask first

• Can this column be NULL? What does NULL mean here: unknown, not yet, or none?
• Should rows with NULL be in the result, or left out?
• If I compare two columns, can both be NULL, and should two NULLs match?

A 60-second walkthrough

“I want customers who aren’t in the list of checkouts. A guest’s checkout has customer_id NULL, and NOT IN with a NULL in its list is never true: 3 NOT IN (1, NULL) means 3 isn’t 1 and 3 isn’t NULL, and nobody can say whether 3 isn’t NULL. WHERE keeps only true rows, so the query would return nothing. So I filter the subquery with customer_id IS NOT NULL. I wouldn’t swap the NULL for 0, because 0 could be a real id. In production I’d often write NOT EXISTS, which doesn’t have the problem.”

Likely follow-ups

• Why does WHERE x = NULL return nothing? x = NULL is NULL for every row, and WHERE keeps only true. Use IS NULL.
• How do you find customers not referred by customer 2, when most weren’t referred at all? WHERE referred_by IS DISTINCT FROM 2, or referred_by <> 2 OR referred_by IS NULL in brackets.
• What’s the difference between count(*) and count(column)? count(*) counts rows; count(column) skips NULLs.

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. Who still needs a manager (easy)

HR’s to-do list: everyone who has no manager yet, apart from Meera, the CEO (employee 1), who never has one. Show employee_id and name, newest hire first, and by employee_id on the same day. On the store’s data, the list is empty.

Hint 1 of 3

manager_id = NULL is NULL for every row, so it keeps none.

Hint 2 of 3

Write manager_id IS NULL.

Hint 3 of 3

Two people hired on the same day tie on hired_on. Add employee_id to the ORDER BY.

Who still needs a manager

Ctrl + Enter runs

The tables

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

SELECT employee_id, name, manager_id, hired_on FROM employees;

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

1. ○Not run yet: The store’s data: everyone has a manager but Meera (row order matters)
2. ○Not run yet: Kai joins Sales before he has a manager (row order matters)
   What this check changes

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

3. ○Not run yet: Lea and Kai both join on 2 November, before they have a manager (row order matters)
   What this check changes

   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (12, 'Lea Fischer', 4, NULL, 5100.00, '2026-11-02'),
          (11, 'Kai Tanaka', 3, NULL, 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

WHERE manager_id = NULL

The idea: Keep the rows whose manager_id equals NULL.

Okay passes 1 of 3. When Kai joins Sales before he has a manager, the list is still empty.

SELECT employee_id, name
FROM employees
WHERE manager_id = NULL
  AND employee_id <> 1
ORDER BY hired_on DESC;

Gets right

Nobody without a manager, as in the store

Gets wrong

Anyone without a manager

Why

manager_id = NULL is NULL, never true, so WHERE keeps no row.

Better

WHERE manager_id IS NULL

The idea: Test for NULL with IS NULL.

The gap in Okay: = NULL can’t find NULL.

Highlighted lines are new in this rung.

SELECT employee_id, name
FROM employees
WHERE manager_id IS NULL
  AND employee_id <> 1
ORDER BY hired_on DESC;

Gets right

People without a manager

Gets wrong

Two hired on the same day

Why

ORDER BY hired_on DESC leaves a tie in whatever order Postgres finds them.

What changed from the previous rung

SELECT employee_id, name
FROM employees
WHERE manager_id = NULL
WHERE manager_id IS NULL
  AND employee_id <> 1
ORDER BY hired_on DESC;

Where it breaks: Lea and Kai both join on 2 November. Better lists Lea, then Kai, the order they were stored in. Better passes 2 of 3.

Best

ORDER BY hired_on DESC, employee_id

The idea: Break the tie by employee_id.

The gap in Better: a tie on hired_on.

Highlighted lines are new in this rung.

SELECT employee_id, name
FROM employees
WHERE manager_id IS NULL
  AND employee_id <> 1
ORDER BY hired_on DESC, employee_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

employee_id is different for every row, so every row has one place.

What changed from the previous rung

SELECT employee_id, name
FROM employees
WHERE manager_id IS NULL
  AND employee_id <> 1
ORDER BY hired_on DESC;
ORDER BY hired_on DESC, employee_id;

When Best isn’t best: employee_id <> 1 names the CEO by id. If the CEO changes, the query is wrong. A column that says who the CEO is, or a rule like “people who manage someone”, would last longer.

2. The Sales offsite (medium)

Priya (employee 7) runs the Sales offsite. Her team, the people she manages (manager_id 7), are at a trade fair, so they miss it. List everyone in Sales (department 3) who isn’t on her team, Priya included: employee_id and manager_id, by manager_id so each team sits together, with anyone who has no manager yet at the top, then by employee_id.

Hint 1 of 3

manager_id <> 7 is NULL for someone with no manager, so WHERE drops them. They aren’t on Priya’s team.

Hint 2 of 3

manager_id IS DISTINCT FROM 7 is true for NULL.

Hint 3 of 3

ORDER BY manager_id puts NULL last. Write NULLS FIRST.

The Sales offsite

Ctrl + Enter runs

The tables

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

SELECT employee_id, name, manager_id FROM employees WHERE department_id = 3;

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: Priya moves to run Operations, and Kai joins Sales before he has a manager (row order matters)
   What this check changes

   UPDATE employees SET department_id = 4 WHERE employee_id = 7;
   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Tanaka', 3, NULL, 4700.00, '2026-11-02');

3. ○Not run yet: Kai joins Sales before he has a manager (row order matters)
   What this check changes

   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Tanaka', 3, NULL, 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

AND manager_id <> 7

The idea: Keep Sales staff whose manager isn’t Priya.

Okay passes 1 of 3. When Priya moves to run Operations and Kai joins Sales with no manager yet, the list is empty instead of Kai.

SELECT employee_id, manager_id
FROM employees
WHERE department_id = 3
  AND manager_id <> 7
ORDER BY manager_id, employee_id;

Gets right

Sales staff who all have a manager, as in the store

Gets wrong

Someone with no manager yet

Why

NULL <> 7 is NULL, so WHERE drops them.

Better

AND manager_id IS DISTINCT FROM 7

The idea: Compare in a way that treats NULL as a value.

The gap in Okay: <> dropped the NULL.

Highlighted lines are new in this rung.

SELECT employee_id, manager_id
FROM employees
WHERE department_id = 3
  AND manager_id IS DISTINCT FROM 7
ORDER BY manager_id, employee_id;

Gets right

Someone with no manager yet

Gets wrong

Where they sort

Why

ORDER BY manager_id puts NULL last, and the list wants it first.

What changed from the previous rung

SELECT employee_id, manager_id
FROM employees
WHERE department_id = 3
  AND manager_id <> 7
  AND manager_id IS DISTINCT FROM 7
ORDER BY manager_id, employee_id;

Where it breaks: Kai joins Sales while Priya is still there. Better lists Priya, then Kai, and HR wants Kai at the top. Better passes 2 of 3.

Best

ORDER BY manager_id NULLS FIRST

The idea: Say where NULL sorts.

The gap in Better: NULL sorted last.

Highlighted lines are new in this rung.

SELECT employee_id, manager_id
FROM employees
WHERE department_id = 3
  AND manager_id IS DISTINCT FROM 7
ORDER BY manager_id NULLS FIRST, employee_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

NULLS FIRST puts the rows with no manager before every number.

What changed from the previous rung

SELECT employee_id, manager_id
FROM employees
WHERE department_id = 3
  AND manager_id IS DISTINCT FROM 7
ORDER BY manager_id, employee_id;
ORDER BY manager_id NULLS FIRST, employee_id;

When Best isn’t best: some databases have no IS DISTINCT FROM. Write (manager_id <> 7 OR manager_id IS NULL) there, in brackets.

3. Left in the cart (medium)

Signed-in customers who left something in their cart: their last add-to-cart came after their last checkout, or they never checked out at all. Show customer_id, by customer_id. On the store’s data, that’s Dana (4).

Hint 1 of 3

GROUP BY customer_id, then compare max(occurred_at) FILTER (WHERE event_type = 'add_to_cart') with the same for checkouts.

Hint 2 of 3

For someone who never checked out, the checkout max() is NULL, and comparing with NULL gives NULL. coalesce(..., '-infinity') makes it the earliest time there is.

Hint 3 of 3

Visitors’ events all go into one NULL group. Leave them out with WHERE customer_id IS NOT NULL.

Left in the cart

Ctrl + Enter runs

The tables

• events (event_id, customer_id, event_type, occurred_at)

SELECT customer_id, event_type, occurred_at FROM events ORDER BY customer_id, occurred_at;

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: Ben adds a lamp to his cart after his last checkout (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, 2, 'add_to_cart', '2026-10-14 22:05:00');

3. ○Not run yet: A guest adds something to their cart and leaves (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, NULL, 'add_to_cart', '2026-10-21 10:00:00');

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

Never checked out

The idea: Keep customers who added to cart and have no checkout at all.

Okay passes 1 of 3. When Ben adds a lamp to his cart after his last checkout, Okay leaves him out.

SELECT customer_id
FROM events
GROUP BY customer_id
HAVING count(*) FILTER (WHERE event_type = 'add_to_cart') > 0
   AND count(*) FILTER (WHERE event_type = 'checkout') = 0
ORDER BY customer_id;

Gets right

Customers who never checked out, like Dana

Gets wrong

A customer who adds more after a checkout

Why

Once a customer has one checkout, Okay never lists them again.

Better

Last add-to-cart after the last checkout

The idea: Compare the latest of each, with coalesce for no checkout.

The gap in Okay: it only looked for no checkout at all. Without the coalesce, Dana’s checkout max() is NULL, and the comparison drops her.

Highlighted lines are new in this rung.

SELECT customer_id
FROM events
GROUP BY customer_id
HAVING max(occurred_at) FILTER (WHERE event_type = 'add_to_cart')
     > coalesce(max(occurred_at) FILTER (WHERE event_type = 'checkout'), '-infinity')
ORDER BY customer_id;

Gets right

Customers who add more after a checkout

Gets wrong

A guest’s cart

Why

Visitors’ events make a NULL group, and it passes HAVING like a customer.

What changed from the previous rung

SELECT customer_id
FROM events
GROUP BY customer_id
HAVING count(*) FILTER (WHERE event_type = 'add_to_cart') > 0
   AND count(*) FILTER (WHERE event_type = 'checkout') = 0
HAVING max(occurred_at) FILTER (WHERE event_type = 'add_to_cart')
     > coalesce(max(occurred_at) FILTER (WHERE event_type = 'checkout'), '-infinity')
ORDER BY customer_id;

Where it breaks: a guest adds something to their cart and leaves. Better lists a NULL customer, and there’s no one to email. Better passes 2 of 3.

Best

WHERE customer_id IS NOT NULL

The idea: Drop the visitors’ events before grouping.

The gap in Better: the NULL group.

Highlighted lines are new in this rung.

SELECT customer_id
FROM events
WHERE customer_id IS NOT NULL
GROUP BY customer_id
HAVING max(occurred_at) FILTER (WHERE event_type = 'add_to_cart')
     > coalesce(max(occurred_at) FILTER (WHERE event_type = 'checkout'), '-infinity')
ORDER BY customer_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

With no visitors’ rows, there’s no NULL group.

What changed from the previous rung

SELECT customer_id
FROM events
WHERE customer_id IS NOT NULL
GROUP BY customer_id
HAVING max(occurred_at) FILTER (WHERE event_type = 'add_to_cart')
     > coalesce(max(occurred_at) FILTER (WHERE event_type = 'checkout'), '-infinity')
ORDER BY customer_id;

When Best isn’t best: a cart emptied by hand, or one that checked out only some items, needs the cart’s own table, not events.

Check yourself

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

1. Predict the outputWhat does this give?

   SELECT NULL = NULL;

   truefalseNULLAn error

   Check answer
2. Spot the bugThis should list the employees who have no manager, like Meera. What happens?

   SELECT name
   FROM employees
   WHERE manager_id = NULL;

   MeeraNo rowsEvery employeePostgres stops with an error

   Check answer
3. Predict the outputThe list of checkouts is 1, 2 and a guest’s NULL. How many customers does this give, out of the store’s 8?

   SELECT customer_id
   FROM customers
   WHERE customer_id NOT IN (1, 2, NULL);

   6820

   Check answer
4. Multiple choiceIn SQL, what does true OR NULL give?

   NULLtruefalseAn error

   Check answer
5. Multiple choiceMeera has no manager. Which WHERE lists every Engineering employee who isn’t on Tom’s team (manager_id 2), Meera too, and no one from another department?

   department_id = 1 AND manager_id <> 2department_id = 1 AND NOT (manager_id = 2)department_id = 1 AND manager_id <> 2 OR manager_id IS NULLdepartment_id = 1 AND manager_id IS DISTINCT FROM 2

   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 a comparison with NULL give, like NULL = 3 or NULL <> 3?Show answer
• Card 2Which rows does WHERE keep?Show answer
• Card 3Why does NOT IN return no rows when its list has a NULL?Show answer
• Card 4How do you test for NULL, and how do you compare when NULL should count as a value?Show answer
• Card 5What do AND and OR give with NULL?Show answer
• Card 6Where do NULLs sort in ORDER BY?Show answer

Add 6 cards to my review deck

Cheat sheet

• NULL means unknown. Any comparison with it gives NULL, and WHERE keeps only true.
• AND: false wins. OR: true wins. Otherwise NULL.
• IS NULL, never = NULL. IS DISTINCT FROM when NULL should count as different.
• NOT IN with a NULL in the list is never true: add IS NOT NULL to the subquery.
• coalesce(x, y) for a default. NULLS FIRST or NULLS LAST to say where NULL sorts.

Go deeper

• In the PostgreSQL docs: comparison operators, IS DISTINCT FROM among them, NOT IN with a subquery and coalesce.
• Try your own checkouts: the reminder list on the SQL visualizer, or any query of your own, step by step.
• 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 what a comparison with NULL gives, and which rows WHERE keeps
• Work out AND, OR and NOT when one side is NULL
• Spot NOT IN with a NULL in its list, = NULL, and <> dropping NULL rows
• Fix them with IS NULL, IS DISTINCT FROM, coalesce and NULLS FIRST

✓Mark as completeI already know this

Status: Not started