Filtering, boolean logic, NULL & patterns

WHERE decides which input rows continue through the query. Most SQL bugs in everyday work are not syntax errors; they are predicate errors that include or exclude the wrong rows.

Comparison predicates

Common comparisons are =, <>, <, <=, >, and >=. Strings use quotes; numbers do not.

SELECT id, status, total
FROM orders
WHERE total >= 100
ORDER BY total DESC;

Run the query to see the result.

AND, OR, NOT and parentheses

AND binds more tightly than OR in SQL, so mixed boolean expressions should usually be parenthesized to make intent unambiguous.

SELECT id, email, role, status
FROM users
WHERE status = 'active'
  AND (role = 'qa' OR role = 'analyst')
ORDER BY id;

Run the query to see the result.

A common defect is writing A OR B AND C when the intended rule is (A OR B) AND C.

IN and BETWEEN

IN is clearer than a long chain of equality OR conditions. BETWEEN is inclusive at both ends.

SELECT id, name, salary
FROM employees
WHERE salary BETWEEN 4000 AND 5000
  AND department_id IN (1, 2)
ORDER BY salary DESC, id;

Run the query to see the result.

For timestamps, inclusive BETWEEN can be dangerous at day boundaries. A half-open interval such as created_at >= start AND created_at < next_day is usually easier to reason about.

NULL is unknown, not an ordinary value

NULL represents absence or unknown information. Comparisons such as email = NULL and email <> NULL do not evaluate to true. Use IS NULL and IS NOT NULL.

SELECT id, first_name, email, phone
FROM users
WHERE email IS NULL OR phone IS NULL
ORDER BY id;

Run the query to see the result.

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. This explains many surprises with NOT IN, nullable columns, and outer joins.

LIKE and wildcard matching

LIKE uses % for any sequence of characters and _ for exactly one character. Case sensitivity varies by engine and collation.

SELECT id, email
FROM users
WHERE email LIKE '%@example.com'
ORDER BY id;

Run the query to see the result.

Do not confuse wildcard matching with regular expressions. Regex support is vendor-specific.

NOT IN and NULL trap

If the subquery/list used by NOT IN can contain NULL, the predicate can become UNKNOWN for every candidate row. NOT EXISTS is often safer for anti-joins because it expresses the intent directly.

Practice: find active users whose phone is missing and whose role is QA. Then rewrite the same logic with the conditions in a different order and confirm the result is unchanged.