TECH **SQL for Data Analysis: HAVING vs WHERE – The Zero-Fluff Guide**

SQL for Data Analysis: HAVING vs WHERE – The Zero-Fluff Guide

1. What This Is & Why It Matters

You’re analyzing sales data for an e-commerce company. Your boss asks: "Show me all product categories where the average order value is over $100, but only for orders placed in the last 30 days."

You write a query, but it either: - Returns all categories (ignoring the $100 threshold), or - Filters out all data (because you applied the condition too early).

Why does this happen?
Because WHERE and HAVING do similar but critically different things in SQL. Mix them up, and your analysis is either wrong or inefficient—costing time, money, and credibility.

Real-World Impact

• Wrong results → Bad business decisions (e.g., misallocating marketing budget).
• Slow queries → Wasted cloud compute costs (e.g., scanning millions of rows unnecessarily).
• Debugging hell → Wasting hours fixing a query that "should work."

Superpower you gain:
You’ll write precise, efficient queries that filter data at the right stage—saving compute, time, and headaches.

2. Core Concepts & Components

1. WHERE

• Definition: Filters rows before aggregation (e.g., SUM, AVG, COUNT).
• Production insight: Use WHERE to reduce the dataset early—this speeds up queries by avoiding unnecessary calculations.
• Example: WHERE order_date > '2023-01-01' (filters rows before grouping).

2. HAVING

• Definition: Filters groups after aggregation.
• Production insight: Use HAVING for conditions on aggregated results (e.g., "only show categories with AVG(sales) > 100").
• Example: HAVING AVG(order_value) > 100 (filters groups after aggregation).

3. Aggregation Functions (SUM, AVG, COUNT, etc.)

• Definition: Perform calculations on groups of rows (e.g., SUM(sales) per category).
• Production insight: Aggregations require GROUP BY (unless you’re aggregating the entire table).

4. GROUP BY

• Definition: Groups rows by one or more columns before applying aggregations.
• Production insight: If you use HAVING, you must have a GROUP BY (or it’s a syntax error).

5. Query Execution Order

• Definition: SQL processes clauses in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
• Production insight: WHERE runs before aggregation; HAVING runs after. This is why you can’t use WHERE on aggregated results.

6. Performance Impact

• WHERE is faster because it filters rows before aggregation (reduces the dataset early).
• HAVING is slower because it filters after aggregation (requires computing all groups first).
• Production insight: If you can filter with WHERE, do it—it saves compute costs.

7. Column Aliases in HAVING

• Definition: You can reference column aliases (e.g., AVG(sales) AS avg_sales) in HAVING but not in WHERE.
• Production insight: This is a common exam trap—WHERE can’t see aliases because it runs before SELECT.

8. WHERE vs HAVING on Non-Aggregated Columns

• WHERE: Can filter on any column (aggregated or not).
• HAVING: Can only filter on aggregated columns or columns in GROUP BY.
• Production insight: If you try HAVING category = 'Electronics', it fails unless category is in GROUP BY.

3. Step-by-Step Hands-On: Writing a Real Query

Prerequisites

• A SQL environment (e.g., PostgreSQL, MySQL, BigQuery, or SQLite).
• A table with sales data (we’ll use this schema): sql CREATE TABLE sales (
order_id INT,
product_category VARCHAR(50),
order_value DECIMAL(10,2),
order_date DATE );
• Sample data: sql INSERT INTO sales VALUES (1, 'Electronics', 150.00, '2023-05-01'), (2, 'Electronics', 200.00, '2023-05-02'), (3, 'Clothing', 50.00, '2023-05-01'), (4, 'Clothing', 30.00, '2023-05-03'), (5, 'Electronics', 80.00, '2023-04-01'); -- Old data (should be excluded)

Task

Write a query that: 1. Only includes orders from May 2023 (WHERE).
2. Groups by product_category.
3. Shows only categories where the average order value > $100 (HAVING).

Step-by-Step Solution

Step 1: Filter with WHERE (before aggregation)

SELECT
    product_category,
    AVG(order_value) AS avg_order_value
FROM sales
WHERE order_date >= '2023-05-01' AND order_date < '2023-06-01'
GROUP BY product_category;

Output:
| product_category | avg_order_value | |------------------|-----------------| | Electronics | 175.00 | | Clothing | 40.00 |

Step 2: Add HAVING to filter groups

SELECT
    product_category,
    AVG(order_value) AS avg_order_value
FROM sales
WHERE order_date >= '2023-05-01' AND order_date < '2023-06-01'
GROUP BY product_category
HAVING AVG(order_value) > 100;

Output:
| product_category | avg_order_value | |------------------|-----------------| | Electronics | 175.00 |

Step 3: Verify the query works

• Check 1: Only May 2023 orders are included (April order is excluded).
• Check 2: Only "Electronics" is returned (avg = $175 > $100; Clothing avg = $40 < $100).

Step 4: Try a common mistake (and fix it)

Mistake: Using WHERE instead of HAVING for the aggregation:

-- ❌ WRONG: This will fail because WHERE can't filter on aggregated results
SELECT
    product_category,
    AVG(order_value) AS avg_order_value
FROM sales
WHERE order_date >= '2023-05-01' AND order_date < '2023-06-01'
  AND AVG(order_value) > 100 -- ERROR: "aggregate functions not allowed in WHERE"
GROUP BY product_category;

Fix: Replace WHERE with HAVING for the aggregation condition.

4. ? Production-Ready Best Practices

Performance

• Use WHERE for row-level filtering (faster, reduces dataset early).
• Use HAVING only for group-level filtering (slower, runs after aggregation).
• Avoid HAVING on non-aggregated columns unless they’re in GROUP BY.

Readability

• Put WHERE before GROUP BY (matches SQL execution order).
• Use column aliases in HAVING for clarity: sql HAVING AVG(order_value) > 100 -- Hard to read HAVING avg_order_value > 100 -- Clearer (if alias is defined in SELECT)

Debugging

• Check execution order: If a query is slow, ask: "Am I filtering too late?"
• Test with EXPLAIN (PostgreSQL/MySQL) to see if HAVING is causing full table scans.

Cloud Cost Optimization

• BigQuery/Redshift: WHERE reduces the amount of data scanned (cheaper).
• Snowflake: HAVING can trigger additional compute (more expensive).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "Which clause filters rows before aggregation?"
2. ❌ HAVING

3. ✅ WHERE

4. "You need to filter groups where COUNT(*) > 5. Which clause do you use?"

5. ❌ WHERE

6. ✅ HAVING

7. "Why does this query fail?"
   sql
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE avg_salary > 50000
GROUP BY department;

8. Answer: WHERE can’t reference aliases or aggregated results. Use HAVING.

Key Trap Distinctions

Scenario-Based Question

"You’re analyzing customer orders. You need to: 1. Exclude orders under $10. 2. Show only cities with more than 5 orders. Which clauses do you use?"

Answer:

SELECT city, COUNT(*) AS order_count
FROM orders
WHERE order_value >= 10  -- Filter rows first
GROUP BY city
HAVING COUNT(*) > 5;     -- Filter groups after

7. ? Hands-On Challenge

Challenge

Write a query that: 1. Shows the total sales per product category.
2. Only includes categories with total sales > $500.
3. Excludes orders from before 2023.

Solution:

SELECT
    product_category,
    SUM(order_value) AS total_sales
FROM sales
WHERE order_date >= '2023-01-01'
GROUP BY product_category
HAVING SUM(order_value) > 500;

Why it works:
- WHERE filters out old orders before aggregation.
- GROUP BY groups by category.
- HAVING filters groups after aggregation.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Documentation: SELECT – Official docs on WHERE and HAVING.
2. SQLZoo: GROUP BY and HAVING – Interactive exercises.
3. Mode Analytics: SQL Tutorial – Real-world SQL examples.
4. "SQL Performance Explained" by Markus Winand – Deep dive into query optimization.