WHERE and HAVING

SQL · Reference cheat sheet

📋 Overview

WHERE filters rows before aggregation; HAVING filters groups after GROUP BY. Use WHERE for row predicates and HAVING for aggregate conditions (COUNT, SUM, …).

🔧 Core concepts

• Order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• Predicates — =, <>, <, IN, BETWEEN, LIKE/ILIKE, IS NULL.
• Boolean — AND / OR / NOT; parentheses for clarity.
• Aliases — HAVING may allow select aliases in some engines; don’t rely on it portably.
• Sargability — avoid wrapping indexed columns in functions in WHERE.

💡 Examples

SELECT id, email
FROM users
WHERE is_active = TRUE
  AND created_at >= DATE '2026-01-01'
  AND email LIKE '%@example.com';

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 3
   AND SUM(total) > 1000;

-- Prefer WHERE over HAVING when possible
SELECT customer_id, COUNT(*) AS c
FROM orders
WHERE region = 'EU'          -- row filter
GROUP BY customer_id
HAVING COUNT(*) >= 3;        -- group filter

-- NULL-safe checks
WHERE deleted_at IS NULL
-- NOT: WHERE deleted_at = NULL

⚠️ Pitfalls

• WHERE COUNT(*) > 1 is invalid — aggregates belong in HAVING (or subquery).
• OR across columns often disables simple index use — consider UNION.
• WHERE col = NULL never matches — use IS NULL.
• MySQL ONLY_FULL_GROUP_BY rejects non-aggregated selected columns.
• Filtering on SELECT aliases in WHERE is invalid in standard SQL.

🔗 Related

• select.md
• aggregate.md
• distinct_group_by.md
• nulls.md
• order_limit.md
• subquery.md