Filtering and Predicates

Filter rows precisely with boolean expressions in WHERE.

AND, OR, NOT and precedence

SELECT *
FROM orders
WHERE status = 'paid' AND (total > 100 OR promo IS NOT NULL)
  AND NOT cancelled;

AND binds tighter than OR; use parentheses for clarity.

IN, BETWEEN, LIKE

SELECT * FROM users WHERE country IN ('US', 'CA');
SELECT * FROM orders WHERE total BETWEEN 100 AND 200;  -- inclusive
SELECT * FROM users WHERE email LIKE '%@example.com';   -- case-sensitive in many engines

Postgres offers ILIKE for case-insensitive matching.

NULL-safe predicates

WHERE last_login IS NULL
WHERE last_login IS NOT NULL

col = NULL is never true.

EXISTS and subqueries (preview)

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Use EXISTS for efficient presence checks.

Pattern pitfalls

Leading % in LIKE '%x' prevents index use
Prefer IN over many OR comparisons
Beware of implicit conversions; cast explicitly when necessary

Summary

Use parentheses to control precedence
Prefer IN, handle NULL with IS [NOT] NULL, and consider EXISTS for presence checks