The Ultimate HAVING vs WHERE SQL Cheat Sheet

WHERE starts the race, HAVING finishes it

If you’ve been working with SQL for a while, you’ve likely come across HAVING and WHERE clauses. They both seem to filter data but they work in different stages of the query process. Understanding when to use HAVING vs WHERE can make your queries more efficient and accurate.

1. Understanding WHERE Clause

The WHERE clause filters rows before any grouping or aggregation happens.

Syntax:

SELECT column1, column2
FROM table_name
WHERE condition;

Key Points:

• Works on raw rows before aggregation.
• Can be used with =, <, >, LIKE, IN, etc.
• Cannot directly filter aggregate functions (like SUM(), AVG()).

Example — Filtering Orders Before Grouping:

Imagine we have an orders table:

order_id | customer_id | amount | order_date
1        | 101         | 150    | 2025-08-01
2        | 102         | 80     | 2025-08-02
3        | 101         | 200    | 2025-08-03
4        | 103         | 50     | 2025-08-04

Query:

SELECT *
FROM orders
WHERE amount > 100;

Output:

order_id | customer_id | amount | order_date
1        | 101         | 150    | 2025-08-01
3        | 101         | 200    | 2025-08-03

2. Understanding HAVING Clause

The HAVING clause filters data after grouping and aggregation are applied.

Syntax:

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING condition;

Key Points:

• Works after GROUP BY.
• Designed for filtering aggregate results (SUM(), COUNT(), AVG()).
• Cannot be used before grouping — use WHERE for that.

Example — Filtering Customers by Total Order Amount:

order_id | customer_id | amount | order_date
1        | 101         | 150    | 2025-08-01
2        | 102         | 80     | 2025-08-02
3        | 101         | 200    | 2025-08-03
4        | 103         | 50     | 2025-08-04

Query:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 200;

Output:

customer_id | total_spent
101         | 350

3. WHERE vs HAVING — Key Differences

Feature                 WHERE                               HAVING
----------------------  ----------------------------------  ----------------------------------
When Applied            Before grouping/aggregation         After grouping/aggregation
Used For                Filtering raw rows                  Filtering aggregated results
Can Use Aggregates?     No                                  Yes
Performance             Usually faster (less data to process) Slightly slower (works on grouped data)

4. Real-World Problem Example

Scenario: A sales manager wants to:

1. Exclude all orders below $100.
2. See only customers who have spent more than $500 in total.

Query:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE amount > 100
GROUP BY customer_id
HAVING SUM(amount) > 500;

Explanation:

• WHERE amount > 100 → filters raw orders first.
• HAVING SUM(amount) > 500 → filters aggregated totals.

Input Table:

order_id | customer_id | amount | order_date
1        | 101         | 150    | 2025-08-01
2        | 102         | 80     | 2025-08-02
3        | 101         | 200    | 2025-08-03
4        | 101         | 300    | 2025-08-05
5        | 104         | 600    | 2025-08-06

Output Table:

customer_id | total_spent
101         | 650
104         | 600

SQL #SQLTips #DataAnalysis #WHEREClause #HAVINGClause #DatabaseQueries #LearnSQL #SQLForBeginners #DataFiltering #SQLBestPractices