AND, OR, NOT, IN, BETWEEN

A WHERE clause with one condition is rare in real testing work. The interesting questions almost always have several parts: "active testers in the last 30 days," "orders that are pending OR processing for users in the US, UK, or CA," "products priced between £10 and £50 that are in stock." This lesson covers the five operators that compose simple conditions into useful, expressive filters — and the precedence rules that catch out almost every SQL beginner at least once.

AND — both conditions must be true

AND joins two conditions that must both be true for a row to match:

SELECT name, email, role FROM users
WHERE role = 'admin' AND is_active = TRUE;

Result:

| name        | email             | role  |
|-------------|-------------------|-------|
| Alice Khan  | alice@example.com | admin |

You can chain as many ANDs as you like:

SELECT name, price FROM products
WHERE in_stock = TRUE
  AND price >= 20.00
  AND price <= 100.00
  AND category_id = 1;

Each AND narrows the result. More conditions = fewer rows.

OR — either condition can be true

OR widens the result — a row matches if any OR-joined condition is true:

SELECT name, category_id FROM products
WHERE category_id = 1 OR category_id = 3;

Result (Electronics + Home, skipping Books):

| name                     | category_id |
|--------------------------|-------------|
| Wireless Mouse           | 1           |
| Mechanical Keyboard Pro  | 1           |
| USB-C Hub                | 1           |
| 27-inch Monitor          | 1           |
| Desk Lamp                | 3           |
| Standing Desk Mat        | 3           |
| Cable Organiser          | 3           |
| Webcam HD                | 1           |

More ORs = more rows.

NOT — flip a condition

NOT inverts the truth of the condition that follows. Two equivalent ways to write the same idea:

SELECT name, role FROM users WHERE NOT role = 'viewer';
SELECT name, role FROM users WHERE role != 'viewer';

Result:

| name        | role   |
|-------------|--------|
| Alice Khan  | admin  |
| Bob Patel   | tester |
| Carol Singh | tester |
| Esha Roy    | tester |

NOT shines when the condition is more complex — for instance, with IN or LIKE:

-- Products that are NOT in stock
SELECT name, in_stock FROM products WHERE NOT in_stock;

A NULL surprise: NOT (column = 'x') doesn't match rows where column IS NULL, because comparing NULL to anything yields NULL (not TRUE, not FALSE). If you want NULL rows included, add OR column IS NULL explicitly.

Operator precedence — the trap every beginner falls into

AND binds tighter than OR. That sounds harmless until you write a query that returns the wrong rows for a reason you can't see:

-- WRONG: this finds all admins, OR any active tester
-- (the AND only applies to "role = 'tester' AND is_active = TRUE")
SELECT * FROM users
WHERE role = 'admin' OR role = 'tester' AND is_active = TRUE;

What you almost certainly meant:

-- CORRECT: finds admins or testers, but only the active ones
SELECT * FROM users
WHERE (role = 'admin' OR role = 'tester') AND is_active = TRUE;

Always parenthesise mixed AND/OR conditions. The extra characters cost nothing and the bug they prevent is invisible to anyone reading the query later. Belt-and-braces parentheses are a habit, not a luxury.

IN — match against a list

IN (...) is short for "equal to any of these values" — much cleaner than chaining ORs:

SELECT id, status FROM orders
WHERE status IN ('pending', 'shipped', 'completed');
 
-- Equivalent to:
-- WHERE status = 'pending' OR status = 'shipped' OR status = 'completed'

The NOT IN variant excludes the list:

-- All orders that aren't cancelled or refunded
SELECT id, status FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

Result (cancelled order #5 dropped):

| id | status     |
|----|------------|
| 1  | completed  |
| 2  | completed  |
| 3  | shipped    |
| 4  | pending    |

A NOT IN gotcha worth remembering: if any value in the list is NULL, NOT IN returns zero rows (because of three-valued logic). When the list might contain NULL, use NOT EXISTS or filter NULLs out explicitly. We'll meet EXISTS in Chapter 6.

BETWEEN — inclusive range filter

BETWEEN x AND y is shorthand for column >= x AND column <= y — both bounds included:

SELECT name, price FROM products
WHERE price BETWEEN 10.00 AND 50.00;

Result:

| name                     | price |
|--------------------------|-------|
| Wireless Mouse           | 25.00 |
| USB-C Hub                | 45.00 |
| SQL for Testers (book)   | 18.50 |
| Clean Code (book)        | 30.00 |
| Desk Lamp                | 42.00 |

It works on numbers, dates, and timestamps:

-- All orders placed in Q1 2026
SELECT id, total FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31';

Combining everything in one query

The composing power is what makes WHERE so useful. Watch how five ideas slot together:

SELECT name, price, category_id, in_stock
FROM products
WHERE price BETWEEN 20.00 AND 100.00
  AND category_id IN (1, 2, 3)
  AND in_stock = TRUE
ORDER BY price DESC;

Read it line by line: products in the £20–£100 range, in any of three categories, currently in stock, sorted most expensive first. Five-second specification → five-second SQL.

How filters compose, in one diagram

In practice the database doesn't apply them sequentially like that — the optimizer rearranges them. But conceptually: AND narrows, OR widens, IN matches a set, BETWEEN matches a range. Pick the operator that says what you mean.

A real QA query

Find every order from the last 7 days that isn't cancelled and is over £50 — the rough shape of a "money-flow sanity check" you might run before a release:

SELECT id, user_id, total, status, created_at
FROM orders
WHERE status NOT IN ('cancelled', 'refunded')
  AND total > 50.00
  AND created_at >= DATE('now', '-7 days')
ORDER BY total DESC;

Three conditions, each saying something specific, joined by AND. If you spot an unexpected row in the result, that's a finding worth following up on.

⚠️ Common Mistakes

• Mixing AND and OR without parentheses. WHERE a OR b AND c is WHERE a OR (b AND c) — almost never what you wanted. Always parenthesise.
• NOT IN with a list that might contain NULL. Returns zero rows, silently. If the list comes from a subquery, filter NULLs out (WHERE col IS NOT NULL) or use NOT EXISTS.
• BETWEEN with timestamps. BETWEEN '2026-01-01' AND '2026-01-31' includes anything before or equal to 2026-01-31 00:00:00 — it does not include the rest of January 31. For "the whole month of January," use created_at >= '2026-01-01' AND created_at < '2026-02-01' (start of next month, exclusive).

🎯 Practice Task

25 minutes. Run each query against the e-commerce database.

1. Active users (is_active = TRUE) whose role is admin or tester. (Use parentheses!)
2. Products in category 1 priced between £25 and £100.
3. Orders that are not in ('cancelled', 'pending') — i.e., the rows representing fulfilment activity.
4. Users whose role is admin OR who have an @test.com email.
5. Products that are out of stock OR priced over £100 — your "won't ship today" report.
6. Run the same query two ways: once with chained ORs, once with IN. Confirm the results are identical.
7. Stretch: find products in any of categories (1, 2, 3) priced between £15 and £80, in stock, sorted by price descending. Five conditions in one WHERE clause — a realistic shape for a real verification.

Next lesson we move beyond a single table — JOINs let you combine rows from orders and users so you can ask "Alice ordered what?" instead of "user_id 1 ordered what?".