The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

The IN and BETWEEN Operators

Next ►
►

There are two ways of writing the same filter: one you understand at a glance and one you have to decipher. IN and BETWEEN exist so you can choose the first. IN replaces a chain of ORs with a list; BETWEEN replaces two comparisons with a range. Neither of them adds any expressive power —everything they do could be written without them— but both cut down the noise and, with it, the chance of getting things wrong.

That said, this lesson carries two warnings worth more than the syntax. The first is the most expensive mistake in all of SQL: NOT IN with a list containing a NULL doesn't return "the remaining rows", it returns zero rows, with no error and no warning. The second is subtler but just as frequent: BETWEEN includes both endpoints, and that turns any date range over a column with a time component into a silent source of lost data.

Contents

  1. IN: the readable alternative to a chain of ORs
  2. NOT IN and its treacherous behaviour with NULL
  3. IN with numbers, text and dates
  4. BETWEEN: syntactic sugar for a closed range
  5. NOT BETWEEN
  6. The BETWEEN trap with dates and times
  7. BETWEEN with text and collation
  8. BETWEEN SYMMETRIC
  9. IN, OR and BETWEEN: performance and readability
  10. Common Mistakes and Tips
  11. Exercises
  12. Conclusion

  1. IN: the readable alternative to a chain of ORs

In 02-03 you wrote this to find the orders that hadn't yet shipped:

SELECT id, customer_id, order_date, status
FROM orders
WHERE status = 'paid'
   OR status = 'pending'
ORDER BY id;

With two values it's readable. With five it stops being so, and on top of that you have to remember the parentheses as soon as an AND shows up (02-03's precedence trap). IN solves both things:

SELECT id,
       customer_id,
       order_date,
       status,
       payment_method
FROM orders
WHERE status IN ('pending', 'paid', 'shipped')
ORDER BY id;
id customer_id order_date status payment_method
16 4 2025-12-19 shipped card
17 7 2026-01-13 shipped paypal
18 5 2026-01-27 paid transfer
19 6 2026-02-09 paid card
20 9 2026-02-21 pending cash_on_delivery

5 rows: GreenStore's "live" orders, the ones that still have work pending behind them. The 14 delivered ones and the cancelled one are left out.

The equivalence, demonstrated

x IN (a, b, c) is exactly x = a OR x = b OR x = c. It isn't an approximation: it's the operator's definition in the standard, and PostgreSQL rewrites it internally that way.

-- These two queries are the same
WHERE status IN ('pending', 'paid', 'shipped')
WHERE status = 'pending' OR status = 'paid' OR status = 'shipped'

Three properties worth bearing in mind come out of that equivalence:

Property Consequence
The order of the list doesn't matter IN ('a','b') and IN ('b','a') are identical
Repeated values do no harm IN ('a','a','b') gives the same as IN ('a','b')
IN () with an empty list isn't valid PostgreSQL raises a syntax error. Careful when generating the list from code

That last point is a classic in applications: if you build the query by concatenating the ids the user selected and the user selects none, you generate IN () and the query blows up. The usual solution is not to generate the condition at all when the list is empty, or to use IN (NULL)… which, as you'll see in the next section, has surprises of its own.

A second example: products from several categories

SELECT id,
       name,
       category_id,
       price
FROM products
WHERE category_id IN (1, 2, 4)
ORDER BY category_id, id;
id name category_id price
1 Extra virgin olive oil 500 ml 1 12.50
2 Organic brown rice 1 kg 1 3.90
3 Raw orange blossom honey 500 g 1 9.75
4 Spelt pasta 500 g 1 2.80
5 Organic crushed tomato 400 g 1 1.95
6 Aloe vera face cream 50 ml 2 18.90
7 Rosemary solid shampoo 80 g 2 8.40
8 Almond body oil 200 ml 2 14.25
9 Calendula lip balm 15 ml 2 4.60
14 Organic chamomile tea 20 bags 4 3.25
15 Ceremonial matcha green tea 30 g 4 22.00
16 Ginger kombucha 750 ml 4 4.95
17 Cold-pressed orange juice 1 L 4 5.40

13 rows: 5 from Food, 4 from Natural cosmetics and 4 from Drinks.

Note: IN also accepts a subquery instead of a literal list —WHERE category_id IN (SELECT id FROM categories WHERE ...)— and that's in fact its most powerful form. But subqueries are module 7: here we stay with explicit lists, and in 07-01 we'll pick IN back up in its full scope.

  1. NOT IN and its treacherous behaviour with NULL

NOT IN is the negation, and its equivalence is just as mechanical:

x NOT IN (a, b)   ≡   NOT (x = a OR x = b)   ≡   x <> a AND x <> b

Look closely at that last form, because the problem lives in it.

The normal case

SELECT id,
       customer_id,
       order_date,
       status
FROM orders
WHERE status NOT IN ('delivered', 'cancelled')
ORDER BY id;
id customer_id order_date status
16 4 2025-12-19 shipped
17 7 2026-01-13 shipped
18 5 2026-01-27 paid
19 6 2026-02-09 paid
20 9 2026-02-21 pending

5 rows. It works perfectly. And it works because status is NOT NULL and because the list contains no NULL.

The case that ruins reports

Now the same idea over orders.employee_id, which does allow nulls. We want the orders that neither Óscar (4) nor Laia (5) handled:

SELECT id, customer_id, employee_id, status
FROM orders
WHERE employee_id NOT IN (4, 5)
ORDER BY id;
id customer_id employee_id status
14 12 6 delivered
20 9 6 pending

2 rows. The ten web orders with a null employee_id have disappeared: it's the same <> symptom you saw in 02-03, no more and no less.

But now comes the serious case. Imagine the list is generated by your application from an earlier query, and that one of the values it brings back is NULL:

-- ⚠️ INCORRECT: the list contains a NULL
SELECT id, customer_id, employee_id, status
FROM orders
WHERE employee_id NOT IN (4, 5, NULL)
ORDER BY id;
(0 rows)

Zero rows. Not two, not eighteen: zero. No error, no warning, nothing. And it isn't a rare case: it's what happens every time somebody writes WHERE id NOT IN (SELECT nullable_column FROM another_table), which is an extremely common construction.

Why it happens

Expand the equivalence:

employee_id NOT IN (4, 5, NULL)
≡ employee_id <> 4 AND employee_id <> 5 AND employee_id <> NULL

The third factor, employee_id <> NULL, is never true. It gives UNKNOWN for any value of employee_id, even for order 14 whose employee is number 6. And in SQL's logic:

Row <> 4 <> 5 <> NULL AND of the three Does it pass the WHERE?
Order 14 (employee_id = 6) true true UNKNOWN UNKNOWN No
Order 2 (employee_id = 4) false true UNKNOWN false No
Order 1 (employee_id = NULL) UNKNOWN UNKNOWN UNKNOWN UNKNOWN No

TRUE AND TRUE AND UNKNOWN gives UNKNOWN, and the WHERE only lets TRUE through (02-03's rule, section 1). No row can survive: it's mathematically impossible for the condition to be true while there's a NULL in the list.

flowchart TD
    A["WHERE x NOT IN (4, 5, NULL)"] --> B["x <> 4 AND x <> 5 AND x <> NULL"]
    B --> C["The third factor is ALWAYS UNKNOWN"]
    C --> D["TRUE AND TRUE AND UNKNOWN = UNKNOWN"]
    D --> E["❌ WHERE only lets TRUE through<br/>→ 0 rows, always"]

And why IN does work

The asymmetry is the most disconcerting part of the whole business. IN with the same list does return rows:

SELECT id, customer_id, employee_id
FROM orders
WHERE employee_id IN (4, 5, NULL)
ORDER BY id;
id customer_id employee_id
2 2 4
4 4 5
6 5 4
8 7 5
10 9 4
12 10 5
16 4 4
18 5 5

8 rows, exactly the same ones IN (4, 5) would give. The reason lies in OR's truth table: TRUE OR UNKNOWN is TRUE, while TRUE AND UNKNOWN is UNKNOWN. With IN (which is a chain of ORs) the NULL is harmless; with NOT IN (which is a chain of ANDs) it destroys everything.

Operator Expands to Effect of a NULL in the list
IN A chain of ORs None. TRUE OR UNKNOWN = TRUE
NOT IN A chain of ANDs Devastating. TRUE AND UNKNOWN = UNKNOWN → 0 rows

How to protect yourself

Four measures, from the simplest to the most robust:

Measure How
Exclude nulls from the list If the list comes from a query, add WHERE column IS NOT NULL
Use 03-03's anti-join LEFT JOIN ... WHERE right.id IS NULL is immune to nulls
Use NOT EXISTS Semantically correct in the face of nulls. Module 7
Declare the column NOT NULL The root-cause fix, if the model allows it (module 5)

The rule to burn into your memory: be suspicious of NOT IN whenever the list can contain a NULL. If you can't guarantee it, use NOT EXISTS or an anti-join. This mistake has reached production in every company in the world at least once, and its symptom —a report that suddenly turns up empty— is always blamed on the data first and on the query second.

Lesson 04-03 develops the three-valued logic underneath all this, with the complete truth tables.

  1. IN with numbers, text and dates

IN isn't limited to integers. It works with any comparable type, as long as every element of the list is of the same type as the expression on the left.

With text, the most frequent use after identifiers:

SELECT id,
       name,
       last_name,
       city,
       country
FROM customers
WHERE country IN ('Portugal', 'France')
ORDER BY country, id;
id name last_name city country
9 Camille Dubois Lyon France
10 Julien Moreau Paris France
7 Sofia Moreira Costa Lisbon Portugal
8 Tiago Almeida Nunes Porto Portugal

4 rows: the international customer base. Remember from 02-03 that text is case-sensitive: IN ('portugal') would give zero rows.

With dates, for specific days and not for ranges:

SELECT id,
       customer_id,
       order_date,
       status
FROM orders
WHERE order_date IN (DATE '2025-03-04', DATE '2025-12-02', DATE '2026-02-21')
ORDER BY id;
id customer_id order_date status
1 1 2025-03-04 delivered
15 1 2025-12-02 delivered
20 9 2026-02-21 pending

3 rows. The DATE prefix in front of the literal isn't compulsory (PostgreSQL converts the string from context), but it documents the type and avoids ambiguities. For consecutive days, IN is the wrong tool: that calls for a range, which is the lesson's second half.

With lists of mixed types, PostgreSQL tries to convert and sometimes fails:

SELECT id FROM products WHERE category_id IN (1, 'two');
ERROR:  invalid input syntax for type integer: "two"

An explicit error, which is the best thing that can happen.

  1. BETWEEN: syntactic sugar for a closed range

BETWEEN replaces two comparisons with one:

x BETWEEN a AND b   ≡   x >= a AND x <= b

Two immediate consequences of that equivalence:

  1. It includes both endpoints. It's a closed interval, [a, b].
  2. The order matters. BETWEEN 10 AND 5 doesn't raise an error: it returns zero rows, because it demands x >= 10 AND x <= 5, which is impossible.

Let's see it with a range chosen so that both endpoints exist in the data:

SELECT id,
       name,
       category_id,
       price
FROM products
WHERE price BETWEEN 3.50 AND 5.50
ORDER BY price, id;
id name category_id price
18 Bamboo toothbrush 5 3.50
2 Organic brown rice 1 kg 1 3.90
9 Calendula lip balm 15 ml 2 4.60
16 Ginger kombucha 750 ml 4 4.95
17 Cold-pressed orange juice 1 L 4 5.40
11 Loofah scrubber (pack of 3) 3 5.50

6 rows, and the two that matter are the first and the last: the bamboo toothbrush costs exactly €3.50 and the loofah scrubber exactly €5.50, and both appear. If BETWEEN were open, this query would return 4 rows.

The long form gives the same thing, letter for letter:

SELECT id, name, category_id, price
FROM products
WHERE price >= 3.50
  AND price <= 5.50
ORDER BY price, id;

The same 6 rows. BETWEEN is neither faster nor slower: PostgreSQL's planner expands it into the two comparisons before deciding the plan. The gain is in readability and in not repeating the column name, which avoids the classic slip of writing WHERE price >= 3.50 AND cost <= 5.50 by accident.

  1. NOT BETWEEN

x NOT BETWEEN a AND b   ≡   x < a OR x > b
SELECT id,
       name,
       price
FROM products
WHERE price NOT BETWEEN 3.50 AND 5.50
ORDER BY price, id;

It returns 14 rows, which together with the previous 6 add up to the 20 products. That they add up is the usual check —and here it works because price is NOT NULL. If it allowed nulls, those rows wouldn't appear on either side and the arithmetic wouldn't close; the mechanism is identical to that of <> and of NOT LIKE.

  1. The BETWEEN trap with dates and times

Here's the reason this course fixed the >= start AND < end convention back in 02-03 rather than BETWEEN.

In GreenStore every date column is a DATE: it stores a calendar day, with no time. With DATE, BETWEEN is perfectly safe:

SELECT id,
       customer_id,
       order_date,
       status,
       shipping_cost
FROM orders
WHERE order_date BETWEEN DATE '2025-06-01' AND DATE '2025-08-31'
ORDER BY order_date, id;
id customer_id order_date status shipping_cost
7 6 2025-06-11 delivered 6.50
8 7 2025-06-28 delivered 9.90
9 8 2025-07-15 delivered 9.90
10 9 2025-08-03 delivered 12.50

4 rows: the summer of 2025, the same orders you got in 02-03 with >= '2025-06-01' AND < '2025-09-01'.

What would happen if the column were a TIMESTAMP

Suppose tomorrow the team decides to store the order's time as well and order_date becomes a TIMESTAMP. A value like 2025-08-31 14:20:00 stops being "31 August" as far as the engine is concerned: it's an instant.

And BETWEEN '2025-06-01' AND '2025-08-31' becomes:

order_date >= 2025-06-01 00:00:00  AND  order_date <= 2025-08-31 00:00:00

Because the string '2025-08-31' converts to the instant midnight on 31 August. Result: every order from 31 August is lost except the ones placed at exactly 00:00:00. A whole day of revenue disappears from the report, every month, with nobody noticing until the quarter closes.

The four possible spellings and their verdicts:

Spelling With DATE With TIMESTAMP Verdict
BETWEEN '2025-06-01' AND '2025-08-31' ✅ Correct ❌ Loses the last day Fragile
>= '2025-06-01' AND <= '2025-08-31' ✅ Correct ❌ Identical problem Fragile
BETWEEN '2025-06-01' AND '2025-08-31 23:59:59' ✅ Correct ⚠️ Loses the day's final microseconds The classic bodge
>= '2025-06-01' AND < '2025-09-01' ✅ Correct ✅ Correct The course's

That third row deserves a comment, because it's the one you see most in real code. '2025-08-31 23:59:59' leaves out the interval between 23:59:59.000001 and 23:59:59.999999. With microsecond-precision TIMESTAMPs, that's almost a second of lost data per range. It looks negligible until you're counting a payment system's transactions.

The course's rule, now fully justified: date ranges are written >= start AND < end, with the upper bound exclusive and expressed as the first instant of the following period. It works with DATE, with TIMESTAMP and with TIMESTAMPTZ; it doesn't depend on the type's precision; and it doesn't force you to know whether the month has 28, 30 or 31 days.

So is BETWEEN no good for dates? It is, on two conditions: that the column is a DATE (not a TIMESTAMP) and that whoever reads the query a year from now still knows that. Since the second can't be guaranteed, the uniform convention comes cheaper. For numbers and for text, on the other hand, BETWEEN is the idiomatic spelling with no drawbacks at all.

  1. BETWEEN with text and collation

BETWEEN also works with strings, comparing them in the order the database's collation defines (you saw it in 02-05):

SELECT id,
       name,
       last_name
FROM customers
WHERE last_name BETWEEN 'A' AND 'C'
ORDER BY last_name, id;
id name last_name
8 Tiago Almeida Nunes
5 Ana Belmonte Roca
13 Núria Bosch Ferrer

3 rows. And here's a surprise that catches almost everybody: Inés Carrasco Vega doesn't appear, even though her surname begins with C.

The reason is that BETWEEN 'A' AND 'C' demands last_name <= 'C', and 'Carrasco Vega' is greater than a bare 'C': they share the first character and the first string carries on, so it comes later in alphabetical order. The upper bound 'C' would only include somebody whose surname was exactly "C".

For "every surname starting with A, B or C" there are two correct spellings:

-- Option 1: raise the upper bound to the next letter, exclusive
WHERE last_name >= 'A' AND last_name < 'D'

-- Option 2: use LIKE (04-01)
WHERE last_name LIKE 'A%' OR last_name LIKE 'B%' OR last_name LIKE 'C%'

The first returns 4 rows (the previous three plus Carrasco Vega) and it's the same >= start AND < end pattern as with dates. That's no coincidence: it's the robust way of expressing a range over any ordered type.

Two more warnings about text:

  • The result depends on the collation. In an English linguistic collation, 'á' sorts alongside 'a'; in the C collation (binary, by bytes), it comes after all of Z. The same query can return different sets on two servers.
  • It's still case-sensitive, with the same caveat as 04-01.

  1. BETWEEN SYMMETRIC

Since the order of the endpoints matters, BETWEEN 10 AND 5 returns zero rows. PostgreSQL offers a variant that reorders them automatically:

SELECT id, name, price
FROM products
WHERE price BETWEEN SYMMETRIC 5.50 AND 3.50
ORDER BY price, id;

It returns the same 6 rows from section 4. BETWEEN SYMMETRIC a AND b is equivalent to BETWEEN LEAST(a,b) AND GREATEST(a,b).

What's it for? Above all when the two endpoints are parameters and you don't control the order they arrive in: a form with two boxes, "price from" and "price to", that the user fills in the wrong way round. With SYMMETRIC the query returns something sensible instead of an empty result.

Dialect note: BETWEEN SYMMETRIC is standard SQL, but in practice only PostgreSQL implements it. MySQL, SQLite, SQL Server and Oracle don't recognise it. If you need portability, BETWEEN LEAST(:a, :b) AND GREATEST(:a, :b) does the same thing on nearly all of them.

  1. IN, OR and BETWEEN: performance and readability

The reasonable question is whether any of these spellings is faster. The short answer: between IN and OR, no; between lists and ranges, it depends on what you're expressing.

Spelling Readability Performance When to use it
x = a OR x = b Low from 3 values upwards Identical to IN Never, except with two values when you're already inside a larger condition
x IN (a, b, c) High Rewritten to = ANY(ARRAY[...]); with long lists PostgreSQL uses a hash table Discrete values that don't form a range
x >= a AND x <= b Medium (it repeats the column) Can use a B-tree index by range When you want to make explicit which endpoint is included
x BETWEEN a AND b High Identical to the previous one: the planner expands it Continuous ranges of numbers or text
x IN (1,2,3,4,5,…,100) Low It works, but it's a list where there should be a range ⚠️ A sign you meant BETWEEN 1 AND 100

Three practical criteria for deciding:

  1. Are the values consecutive? Then it's a range: BETWEEN. Writing category_id IN (1,2,3,4,5,6) over the six categories is worse than not filtering at all.
  2. Are the values arbitrary? Then it's a list: IN. status IN ('paid','pending') isn't a range of anything.
  3. Is the list enormous and does it come from another table? Then it's neither: it's a subquery or a JOIN (module 7). An IN with two thousand literals generated from code is a symptom of a missing JOIN.

On performance there's a nuance you'll see in module 8: PostgreSQL treats a short-list IN as a series of comparisons and, above a certain size, builds a hash table. An IN with thousands of elements can still be efficient, but the query's parsing time grows, and that query doesn't reuse the plan cache well because every call has a different list. It's another reason to prefer the JOIN when the list comes from data.

Common Mistakes and Tips

  • NOT IN with a NULL in the list. Zero rows, always, with no warning whatsoever. It's the most expensive mistake in this lesson and probably in the course.
  • Forgetting that NOT IN and NOT BETWEEN exclude the column's null rows. Just like <> in 02-03: check that the condition and its negation add up to the total.
  • Writing BETWEEN 10 AND 5. Zero rows, no error. The first endpoint must be the smaller one, or use BETWEEN SYMMETRIC.
  • Assuming BETWEEN excludes one endpoint. It includes both. If you wanted price >= 10 AND price < 20, BETWEEN 10 AND 20 is not equivalent.
  • Using BETWEEN over a TIMESTAMP column. You lose the last day. Use >= start AND < end.
  • Using '…23:59:59' as the upper bound. You lose almost a second per range, and with microsecond precision that's a real hole.
  • BETWEEN 'A' AND 'C' expecting every surname with a C. It only reaches the exact string "C". Use >= 'A' AND < 'D'.
  • Generating IN () with an empty list from code. A syntax error at runtime. Handle the case before concatenating.
  • Turning a range into a list. IN (1,2,3,…,100) is a badly written BETWEEN 1 AND 100.
  • Tip: when writing NOT IN, say out loud "can there be a null here?". If the answer isn't a flat no, change your strategy.
  • Tip: use BETWEEN for numbers and text, and >= … AND < … for dates. It's a simple, uniform rule that will never betray you.
  • Tip: put the IN list in alphabetical or numerical order even though it doesn't affect the result. Spotting a duplicated or missing value in a sorted list is trivial; in an unsorted one, it isn't.

Exercises

Exercise 1

Customer support needs two listings. Write them using IN (not OR):

  1. The orders paid by card or PayPal, showing id, customer_id, order_date, status, payment_method and shipping_cost. How many rows?
  2. The products from categories 3 (Sustainable home) and 5 (Personal hygiene) that are also active, showing id, name, category_id, price and stock.

Exercise 2

Management asks for the detail of the fourth quarter of 2025 (October, November and December).

  1. Write it with BETWEEN and with the course's convention, and check that they give the same thing.
  2. Explain what would happen with each of the two versions if order_date were a TIMESTAMP and there was an order recorded on 31 December 2025 at 18:40.

Exercise 3

A colleague shows you this query and tells you it "returns nothing and he doesn't understand why":

-- ⚠️ INCORRECT
SELECT id, name, last_name, referred_by_id
FROM customers
WHERE referred_by_id NOT IN (1, 2, NULL);
  1. Explain what he intended and what's actually happening.
  2. Fix it so that it returns "the customers referred by somebody who isn't Lucía (1) or Carlos (2)".
  3. Fix it so that it returns "the customers who were not referred by either Lucía or Carlos", including those who arrived on their own. State how many rows each version gives.

Solutions

Solution 1

1.

SELECT id,
       customer_id,
       order_date,
       status,
       payment_method,
       shipping_cost
FROM orders
WHERE payment_method IN ('card', 'paypal')
ORDER BY id;
id customer_id order_date status payment_method shipping_cost
1 1 2025-03-04 delivered card 4.95
3 3 2025-04-02 delivered card 4.95
4 4 2025-04-19 delivered paypal 4.95
5 1 2025-05-07 delivered card 0.00
6 5 2025-05-23 cancelled card 4.95
8 7 2025-06-28 delivered card 9.90
9 8 2025-07-15 delivered paypal 9.90
10 9 2025-08-03 delivered card 12.50
11 2 2025-09-09 delivered card 0.00
13 11 2025-10-22 delivered card 4.95
14 12 2025-11-14 delivered paypal 4.95
15 1 2025-12-02 delivered card 0.00
16 4 2025-12-19 shipped card 4.95
17 7 2026-01-13 shipped paypal 9.90
19 6 2026-02-09 paid card 4.95

15 rows: 11 by card and 4 by PayPal. The remaining 5 were paid by transfer (3) or cash on delivery (2).

2.

SELECT id,
       name,
       category_id,
       price,
       stock
FROM products
WHERE category_id IN (3, 5)
  AND active
ORDER BY category_id, id;
id name category_id price stock
10 Concentrated eco laundry detergent 1 L 3 11.20 70
11 Loofah scrubber (pack of 3) 3 5.50 110
12 Reusable cotton bags (pack of 5) 3 9.90 85
13 Soy wax candles (pack of 2) 3 13.75 0
18 Bamboo toothbrush 5 3.50 240
19 Natural stick deodorant 50 g 5 7.80 75

6 rows: the 4 from Sustainable home and the 2 from Personal hygiene, all active. Notice that product 13 shows up despite having stock 0: it's active, and active and stock are different things. It's the same nuance as exercise 1 in 02-03.

Solution 2

1. The two versions:

-- With BETWEEN (valid because order_date is a DATE)
SELECT id, customer_id, order_date, status, shipping_cost
FROM orders
WHERE order_date BETWEEN DATE '2025-10-01' AND DATE '2025-12-31'
ORDER BY order_date, id;
-- ✅ The course's convention
SELECT id, customer_id, order_date, status, shipping_cost
FROM orders
WHERE order_date >= DATE '2025-10-01'
  AND order_date <  DATE '2026-01-01'
ORDER BY order_date, id;

Both return the same thing:

id customer_id order_date status shipping_cost
12 10 2025-10-01 delivered 12.50
13 11 2025-10-22 delivered 4.95
14 12 2025-11-14 delivered 4.95
15 1 2025-12-02 delivered 0.00
16 4 2025-12-19 shipped 4.95

5 rows. Notice that order 12 is from exactly 1 October: it comes in through the lower endpoint, which both versions include.

2. With a TIMESTAMP and an order from 31 December at 18:40:

Version What it evaluates Does it include the 18:40 order?
BETWEEN '2025-10-01' AND '2025-12-31' <= 2025-12-31 00:00:00 No. It's lost
>= '2025-10-01' AND < '2026-01-01' < 2026-01-01 00:00:00 Yes

The first version would lose every order from 31 December after midnight, that is, practically all of them. And since the mistake gives no message, the quarter would close with a low figure nobody could explain. This is the exact reason for the course's convention: the second version doesn't depend on the column's type, and therefore doesn't break the day somebody changes that type.

Solution 3

1. What he intended and what's happening. He intended to exclude the customers referred by Lucía and by Carlos. What actually happens is that the list contains a NULL, so the condition expands to:

referred_by_id <> 1 AND referred_by_id <> 2 AND referred_by_id <> NULL

and that third factor is UNKNOWN for every row. TRUE AND TRUE AND UNKNOWN = UNKNOWN, which the WHERE discards. Result: 0 rows, guaranteed.

2. Referred by somebody who isn't Lucía or Carlos:

-- ✅ CORRECT
SELECT id,
       name,
       last_name,
       referred_by_id
FROM customers
WHERE referred_by_id NOT IN (1, 2)
ORDER BY id;
id name last_name referred_by_id
8 Tiago Almeida Nunes 7
10 Julien Moreau 9
11 Elena Navarro Puig 6
13 Núria Bosch Ferrer 5

4 rows. It's enough to take the NULL out of the list. The 7 customers with a null referred_by_id still don't appear, and that's correct for this question: somebody who wasn't referred by anyone wasn't referred by "somebody who isn't Lucía or Carlos".

3. Those not referred by either Lucía or Carlos, including those who arrived on their own:

-- ✅ CORRECT
SELECT id,
       name,
       last_name,
       referred_by_id
FROM customers
WHERE referred_by_id NOT IN (1, 2)
   OR referred_by_id IS NULL
ORDER BY id;
id name last_name referred_by_id
1 Lucía Martínez Soler (null)
4 Javier Ortega Ruiz (null)
6 Pau Llorens Vidal (null)
7 Sofia Moreira Costa (null)
8 Tiago Almeida Nunes 7
9 Camille Dubois (null)
10 Julien Moreau 9
11 Elena Navarro Puig 6
12 Diego Ramos Herrera (null)
13 Núria Bosch Ferrer 5
14 Hugo Iglesias Pardo (null)

11 rows. The check closes: 4 customers were referred by Lucía (2, 3, 15) or by Carlos (5) —four in total— and 15 − 4 = 11.

A summary of the three versions:

Version Rows Question it answers
NOT IN (1, 2, NULL) 0 None: it's broken
NOT IN (1, 2) 4 "Referred by somebody other than Lucía and Carlos"
NOT IN (1, 2) OR ... IS NULL 11 "Not referred by Lucía or by Carlos"

The last two are both legitimate and answer different questions. Choosing badly between them is an analysis error; the first, by contrast, is an SQL error. IS NULL, which has turned up here as a patch, is the full subject of the next lesson.

Conclusion

You now write list and range filters with the right spelling:

  • IN is a chain of ORs and NOT IN is a chain of ANDs. Everything else follows from that: the order doesn't matter, duplicates make no difference, and an empty list isn't valid syntax.
  • NOT IN with a NULL in the list returns exactly zero rows, because TRUE AND UNKNOWN is UNKNOWN and the WHERE only lets TRUE through. IN with the same NULL works without a problem, because TRUE OR UNKNOWN is TRUE. When in doubt: an anti-join or NOT EXISTS.
  • BETWEEN is >= a AND <= b: a closed interval, with both endpoints included, and sensitive to the order of the bounds. NOT BETWEEN is < a OR > b, with the same blindness to nulls.
  • With DATE columns, BETWEEN is safe; with TIMESTAMP it loses the last day. The convention >= start AND < end works with any type and it's the one the course uses.
  • With text, BETWEEN 'A' AND 'C' doesn't include "Carrasco Vega": a closed range over strings almost never means what it looks like. Use >= 'A' AND < 'D' or LIKE.
  • BETWEEN SYMMETRIC reorders the endpoints automatically and only exists in PostgreSQL.
  • Between IN and OR there's no performance difference; the choice between a list and a range should follow the nature of the data: discrete values → IN, consecutive values → BETWEEN, values that come from another table → a JOIN or a subquery (module 7).

In the next lesson, NULL values and IS NULL, the debt is finally settled. You've seen the same mechanism peeking out in 02-03's WHERE, in all of module 3's LEFT JOINs and in today's NOT IN, always with the same promise of "we'll explain it in 04-03". There come the complete truth tables of three-valued logic, IS NULL and IS NOT NULL, the IS DISTINCT FROM that treats null as just another value, and the standard's inconsistency by which two NULLs aren't equal in a WHERE but do group together in a GROUP BY.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project

The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved