Filtering with AND, OR, IN, BETWEEN

Filtering with AND, OR, IN, BETWEEN

A single WHERE condition rarely captures a real test scenario. Combining conditions with AND, OR, IN, and BETWEEN is how you express "give me exactly this slice of data" — and how you verify the application is filtering correctly too.

AND vs OR

-- AND: every condition must be true
SELECT * FROM users WHERE country = 'US' AND is_active = 1;

-- OR: at least one condition must be true
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

Operator Precedence — Use Parentheses

AND binds tighter than OR. Without parentheses, a mixed condition can silently filter the wrong rows:

-- Looks like "US orders that are pending or processing"...
SELECT * FROM orders
WHERE country = 'US' AND status = 'pending' OR status = 'processing';

-- ...but it actually returns ALL processing orders, regardless of country,
-- because AND is evaluated before OR. Fix it with parentheses:
SELECT * FROM orders
WHERE country = 'US' AND (status = 'pending' OR status = 'processing');

This is a real bug pattern to test for: any query (or ORM-generated query) mixing AND/OR without parentheses is a candidate for a data-filtering bug.

IN — Match Against a List

IN replaces a long chain of OR comparisons on the same column:

-- Equivalent, but IN is clearer and usually faster
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing' OR status = 'on_hold';
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'on_hold');

-- NOT IN excludes a list
SELECT * FROM users WHERE country NOT IN ('US', 'CA');

BETWEEN — Inclusive Ranges

-- Inclusive on both ends: price BETWEEN 10 AND 100 includes 10 and 100
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- Dates work the same way — watch for time-of-day truncation
SELECT * FROM orders WHERE created_at BETWEEN '2024-06-01' AND '2024-06-30';
-- Careful: '2024-06-30' means midnight — orders placed later that day are excluded.
-- Safer:
SELECT * FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';

Test Scenario: Verifying a Multi-Filter Search

An app feature filters orders by status and a date range at the same time:

SELECT id, status, total, created_at
FROM   orders
WHERE  status IN ('completed', 'refunded')
  AND  created_at >= '2024-06-01' AND created_at < '2024-07-01'
ORDER BY created_at DESC;

Run this directly against the database and compare the row count and IDs against what the UI shows. A mismatch usually means the application is missing a condition, using the wrong boundary (<= vs <), or combining AND/OR incorrectly.