The WHERE Clause — Filtering with Operators and Conditions

The WHERE clause is what transforms a full-table operation into a targeted one — it filters which rows a SELECT returns, which rows an UPDATE modifies, and which rows a DELETE removes. Without WHERE, SQL statements operate on every row. With WHERE, they operate on exactly the rows that satisfy the condition. Every FastAPI query parameter that filters results — ?status=published, ?author_id=5, ?created_after=2025-01-01 — translates to a WHERE condition. Understanding all the operators and patterns available in WHERE clauses is the foundation for writing precise, efficient database queries.

Comparison Operators #

-- Equality and inequality
SELECT * FROM users WHERE role = 'admin';
SELECT * FROM users WHERE role != 'admin';   -- also: <> is equivalent
SELECT * FROM posts WHERE view_count >= 1000;
SELECT * FROM posts WHERE view_count > 0 AND view_count < 100;

-- BETWEEN (inclusive on both ends)
SELECT * FROM posts
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
-- Equivalent to: created_at >= '2025-01-01' AND created_at <= '2025-12-31'

SELECT * FROM posts WHERE view_count BETWEEN 100 AND 1000;

-- IN — match any value from a list
SELECT * FROM users WHERE role IN ('editor', 'admin');
SELECT * FROM posts WHERE id IN (1, 5, 12, 42);
SELECT * FROM posts WHERE status NOT IN ('archived', 'deleted');

-- IN with a subquery
SELECT * FROM posts
WHERE author_id IN (
    SELECT id FROM users WHERE role = 'admin'
);

NULL Handling #

-- NULL is not a value — it means "unknown" or "missing"
-- You CANNOT use = or != to compare with NULL

-- Wrong: WHERE col = NULL always returns no rows
-- Wrong: WHERE col != NULL always returns no rows

-- Correct: IS NULL / IS NOT NULL
SELECT * FROM posts WHERE published_at IS NULL;     -- not published yet
SELECT * FROM posts WHERE published_at IS NOT NULL; -- has been published

SELECT * FROM users WHERE deleted_at IS NULL;       -- active users
SELECT * FROM posts WHERE excerpt IS NULL;          -- no excerpt provided

-- NULL in expressions: NULL propagates
-- NULL + 5 = NULL
-- NULL = NULL = NULL (not TRUE!)
-- COALESCE returns first non-NULL value
SELECT
    id,
    COALESCE(excerpt, SUBSTRING(body, 1, 200)) AS display_excerpt
FROM posts;
-- Uses excerpt if available, otherwise first 200 chars of body

String Pattern Matching #

-- LIKE: case-sensitive pattern matching
-- % = any sequence of characters
-- _ = exactly one character

SELECT * FROM users WHERE name LIKE 'Alice%';     -- starts with 'Alice'
SELECT * FROM users WHERE name LIKE '%Smith';     -- ends with 'Smith'
SELECT * FROM users WHERE name LIKE '%ali%';      -- contains 'ali'
SELECT * FROM users WHERE email LIKE '_@%.com';   -- 1 char, @, domain, .com

-- ILIKE: case-insensitive (PostgreSQL-specific)
SELECT * FROM users WHERE name ILIKE 'alice%';    -- matches Alice, ALICE, alice

-- SIMILAR TO: SQL standard regex (rarely used — prefer ~ for regex)
SELECT * FROM posts WHERE slug SIMILAR TO '[a-z0-9-]+';

-- Regular expressions (PostgreSQL-specific, very powerful)
SELECT * FROM posts WHERE slug ~ '^[a-z0-9-]+$';   -- ~ means matches regex
SELECT * FROM posts WHERE title ~* 'python';        -- ~* is case-insensitive regex
SELECT * FROM posts WHERE slug !~ '[A-Z]';          -- !~ means does NOT match

Combining Conditions #

-- AND — all conditions must be true
SELECT * FROM posts
WHERE status = 'published'
  AND author_id = 1
  AND view_count > 100;

-- OR — at least one condition must be true
SELECT * FROM users
WHERE role = 'admin'
   OR role = 'editor';
-- Better written as: WHERE role IN ('admin', 'editor')

-- NOT — negate a condition
SELECT * FROM posts WHERE NOT (status = 'archived');
SELECT * FROM posts WHERE status != 'archived';   -- equivalent

-- Complex combinations (use parentheses for clarity)
SELECT * FROM posts
WHERE (status = 'published' OR status = 'draft')
  AND author_id IN (
      SELECT id FROM users WHERE role = 'editor'
  )
  AND created_at >= NOW() - INTERVAL '30 days';

-- Dynamic filtering pattern used in FastAPI
-- Build conditions based on query parameters:
-- WHERE (status = :status OR :status IS NULL)
-- This pattern: matches the status if a filter is provided,
-- or matches all rows if the filter is NULL (not provided)

Common Mistakes #

Mistake 1 — Using = NULL instead of IS NULL #

❌ Wrong — always returns zero rows:

SELECT * FROM posts WHERE published_at = NULL;   -- matches nothing!

✅ Correct:

SELECT * FROM posts WHERE published_at IS NULL;   -- ✓

Mistake 2 — String interpolation (SQL injection risk) #

❌ Wrong — vulnerable to SQL injection:

query = f"SELECT * FROM users WHERE email = '{user_email}'"
# If user_email = "' OR 1=1 --", this returns all users!

✅ Correct — always use parameterised queries:

cursor.execute("SELECT * FROM users WHERE email = %s", (user_email,))   # ✓

Mistake 3 — OR without parentheses produces unexpected results #

❌ Wrong — AND binds tighter than OR, so this means (A AND B) OR C:

WHERE status = 'published' AND author_id = 1 OR role = 'admin'
-- Matches: (published posts by author 1) OR (any row where role=admin)

✅ Correct — use parentheses to make intent explicit:

WHERE status = 'published'
  AND (author_id = 1 OR author_id = 2)   -- ✓ clear grouping

Quick Reference #