WHERE Clause

Introduction

WHERE filters rows before grouping or projection. It is your single most-used optimization tool — a good WHERE turns a 10-second scan into a 5-millisecond index lookup.

Understanding the topic

Core concepts to understand:

• Comparison: = < > <= >= <>.
• Logical: AND, OR, NOT.
• Membership: IN, NOT IN.
• Pattern: LIKE, ILIKE (case-insensitive in PG), ~ regex.
• NULL: IS NULL / IS NOT NULL — never = NULL.
• Range: BETWEEN a AND b (inclusive both sides).

Syntax reference

Visual workflow / architecture:

Query rows ── WHERE filter ──▶ remaining rows ──▶ GROUP/SELECT

WHERE status = 'paid'
  AND amount > 100
  AND created_at >= now() - interval '7 days'
  AND email ILIKE '%@gmail.com'

SELECT <cols>
FROM   <table>
WHERE  <col> <op> <value>
   [AND|OR <col> <op> <value> ...];

SELECT id, email, amount
FROM   orders
WHERE  status = 'paid'
  AND  amount > 50;

Real-world use

Every page in every product is filtered: 'unread emails', 'orders this month', 'active users'. WHERE drives all of it.

Best practices

• Filter first, project last — use WHERE, not HAVING, for row filters.
• Make sure WHERE columns have indexes for big tables.
• Avoid functions on indexed columns: WHERE lower(email) = ... kills the index.

Common mistakes

• = NULL never matches — use IS NULL.
• NOT IN (...) with NULLs always returns empty.
• Functions on columns prevent index use.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why doesn't = NULL work?
• Q2. Difference between WHERE and HAVING.
• Q3. LIKE vs ILIKE.
• Q4. What is a sargable predicate?
• Q5. Inclusive nature of BETWEEN.