All lessons

Module 2: Picking rows and columns · Lesson 2.3 · about 30 min

AND, OR, IN, BETWEEN and LIKE

Combine conditions with AND and OR, test against a list with IN, a range with BETWEEN and a pattern with LIKE, and avoid the AND-before-OR trap that quietly inflates numbers.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 2 · about 30 minutes · read first: 2.2. You’ll be able to combine several conditions safely, test against lists, ranges and text patterns, and explain why brackets matter around OR.

AND, OR, IN, BETWEEN and LIKE build bigger tests out of small ones, and SQL does AND before OR, so brackets decide what you really asked.

In everyday terms: “I’ll have tea or coffee and a biscuit.” Does the biscuit come only with the coffee, or with either drink? Spoken English is vague; SQL is not, and it always reads it as “tea, or (coffee and a biscuit)” unless you add brackets.

1. Why this matters

Real filters are rarely one test: “completed online orders in Black Friday week, in Alberta or BC”. A single missing bracket around an OR can double a number without any error message, and these mistakes are among the hardest to spot in a finished report. The operators here are short, but precision with them is what makes your numbers trusted.

2. AND: both must be true

AND keeps a row only if the conditions on both sides are true.

-- try
SELECT order_id, order_date, store_id
FROM orders
WHERE channel = 'online' AND status = 'cancelled'

29 online orders were cancelled. Each extra AND can only keep the same rows or fewer.

3. OR: either will do

OR keeps a row if at least one side is true. Run this, then press Show how it runs and watch which mini orders survive the WHERE step:

-- try
SELECT order_id, item, amount
FROM mini_orders
WHERE item = 'Kettle' OR amount < 10

Three rows pass: the two Kettles (by the first test) and the Bagels (by the second).

NOT flips a test: WHERE NOT status = 'completed' is the same as WHERE status <> 'completed'.

4. The AND-before-OR trap

Question: how many 2025 orders did store 4 (Ottawa) and store 8 (Winnipeg) take together?

-- try
SELECT COUNT(*) AS orders
FROM orders
WHERE store_id = 4 OR store_id = 8 AND order_date LIKE '2025%'

That says 455, and it is wrong. SQL works out AND before OR, the way maths does × before +, so it read the query as “store 4 (any year) OR (store 8 in 2025)”. Store 4’s 2024 orders sneaked in. Add brackets:

-- try
SELECT COUNT(*) AS orders
FROM orders
WHERE (store_id = 4 OR store_id = 8) AND order_date LIKE '2025%'
  1. The rule: whenever a WHERE mixes AND with OR, put brackets around the OR.
p AND q
Both tests must be true. Can only keep the same rows or fewer.
p OR q
At least one test must be true.
NOT p
Flips the test.
Precedence
The order SQL works things out: NOT, then AND, then OR (like × before +).
x IN (a, b)
Short for x = a OR x = b, with no bracket trap.
x BETWEEN a AND b
x >= a AND x <= b: both ends included.
LIKE 'B%'
A text pattern: % is any run of characters, _ exactly one.
Physical meaning
Each row gets the whole combined test; AND keeps the overlap of the two sets of rows, OR keeps both sets together. A missing bracket silently changes which rows are in the overlap.

“SQL reads my conditions left to right, so the order I type them in is the order they are joined.” SQL always does AND before OR. store_id = 4 OR store_id = 8 AND year = 2025 means “store 4 in any year, or store 8 in 2025”. Whenever AND and OR meet in one WHERE, put brackets around the OR (or use IN).

Watch the bracket trap happen, one step at a time, on four orders:

The orders.
orderstoreyear
A42024
B42025
C82024
D82025
Without brackets SQL groups the AND first: store = 4 OR (store = 8 AND year = 2025).
orderstoreyear
A42024
B42025
D82025
Order A (2024) sneaks in: 3 rows.
With brackets: (store = 4 OR store = 8) AND year = 2025.
orderstoreyear
B42025
D82025
2 rows, the right answer.
With IN: store IN (4, 8) AND year = 2025 gives the same 2 rows, and there is no bracket to forget.

Press Show how it runs on the two Northbay boxes above to see the same thing happen to 4,064 real orders. Then try it:

5. IN: one of a list

x IN (a, b, c) is short for x = a OR x = b OR x = c, and it can’t fall into the bracket trap.

-- try
SELECT order_id, item, amount
FROM mini_orders
WHERE item IN ('Kettle', 'Duvet')

NOT IN keeps rows whose value is none of the list:

-- try
SELECT store_name, city
FROM stores
WHERE city NOT IN ('Toronto', 'Ottawa')

You get 9 stores, not 10: Northbay Online has no city (NULL), and NULL is never “not in” anything. Lesson 2.4 explains why.

6. BETWEEN: a range, both ends included

x BETWEEN a AND b means x >= a AND x <= b. Both ends count.

-- try
SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 10 AND 20

With dates, BETWEEN is a neat way to say “this week”. Black Friday 2025 ran from 24 to 30 November:

-- try
SELECT COUNT(*) AS black_friday_orders
FROM orders
WHERE order_date BETWEEN '2025-11-24' AND '2025-11-30'

70 orders. Be careful when a column holds a date and a time (common in SQL Server and PostgreSQL): BETWEEN '2025-11-24' AND '2025-11-30' then stops at midnight at the start of the 30th. The safe habit there is >= '2025-11-24' AND < '2025-12-01'.

7. LIKE: a text pattern

LIKE matches text against a pattern with two wildcards:

  • % any run of characters, including none
  • _ exactly one character
-- try
SELECT product_name
FROM products
WHERE product_name LIKE '%pack%'

Seven product names contain “pack”. Try 'B%' (starts with B), '%s' (ends with s) and '_____' (exactly five characters).

In SQLite, LIKE ignores capitals for plain English letters, so 'b%' finds “Bagels” too. PostgreSQL’s LIKE does care about capitals (use ILIKE there to ignore them); SQL Server follows the database’s collation, which usually ignores them.

Maths and principle

Principle: conditions combine with the rules of Boolean algebra. AND keeps the rows in both sets (intersection), OR keeps rows in either (union), and NOT keeps rows outside (complement).

Equation: σp AND q(T) = σp(T) ∩ σq(T) and σp OR q(T) = σp(T) ∪ σq(T). Precedence: NOT, then AND, then OR, so a OR b AND c = a OR (b AND c), like a + b × c = a + (b × c). De Morgan: NOT (p OR q) = (NOT p) AND (NOT q).

Analogy: a Venn diagram. AND is the overlap, OR is everything shaded. It breaks down a little with NULL, which belongs to neither the “true” circle nor the “false” one.

Try it: using De Morgan, rewrite NOT (store_id = 4 OR store_id = 8). (store_id <> 4 AND store_id <> 8, which is the same as store_id NOT IN (4, 8).)

8. Worked example

Question: during Black Friday week 2025 (24 to 30 November), which completed orders came through the two Alberta stores (9 Calgary, 10 Edmonton)?

  1. The week: order_date BETWEEN '2025-11-24' AND '2025-11-30'.
  2. Completed only: AND status = 'completed'.
  3. Alberta stores: AND store_id IN (9, 10). IN avoids the OR bracket trap.
-- try
SELECT order_id, order_date, store_id
FROM orders
WHERE order_date BETWEEN '2025-11-24' AND '2025-11-30'
  AND status = 'completed'
  AND store_id IN (9, 10)

There are 5. Now change IN (9, 10) to = 12 to see the online orders for the same week.

9. Practice

-- exercise: List the name and price of products that cost between $30 and $40 (inclusive).
-- hint: unit_price BETWEEN 30 AND 40
SELECT product_name, unit_price
FROM products
WHERE unit_price > 30
-- answer:
SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 30 AND 40
-- exercise: Count customers who live in Alberta or British Columbia (province AB or BC).
-- hint: province IN ('AB', 'BC')
SELECT COUNT(*) AS customers
FROM customers
WHERE province = 'AB'
-- answer:
SELECT COUNT(*) AS customers
FROM customers
WHERE province IN ('AB', 'BC')
-- exercise: This query should count cancelled orders at store 1 or store 2, but the number is too big. Fix it.
-- hint: AND is worked out before OR. Put brackets around the OR (or use IN).
SELECT COUNT(*) AS cancelled
FROM orders
WHERE store_id = 1 OR store_id = 2 AND status = 'cancelled'
-- answer:
SELECT COUNT(*) AS cancelled
FROM orders
WHERE store_id IN (1, 2) AND status = 'cancelled'
-- exercise: Find every product whose name ends in "set". Show the name only.
-- hint: LIKE '%set' (the % goes before the word).
SELECT product_name
FROM products
WHERE product_name LIKE 'set%'
-- answer:
SELECT product_name
FROM products
WHERE product_name LIKE '%set'
  • AND needs both tests true; OR needs at least one; NOT flips a test.
  • SQL does NOT, then AND, then OR. Put brackets around every OR that sits next to an AND.
  • IN (a, b, c) is a safe short form of several ORs.
  • BETWEEN includes both ends; with date-and-time columns use >= start AND < next day.
  • LIKE matches patterns: % for any run of characters, _ for exactly one.

New words: Boolean, precedence, IN, BETWEEN, LIKE, wildcard.

Check yourself

1In WHERE a OR b AND c, which part is worked out first?
2Does BETWEEN 10 AND 20 include 10 and 20?
3Which product names does LIKE '%pack' match?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.