SQL: WHERE vs HAVING - Unterschied in der Zeilen- und Gruppenfilterung

// Logical execution order in a standard SQL engine:
// 1. FROM / JOIN (Identify source data)
// 2. WHERE (Filter individual rows)
// 3. GROUP BY (Organize rows into groups)
// 4. HAVING (Filter the resulting groups)
// 5. SELECT (Compute aggregates and project columns)
// 6. ORDER BY (Sort the final result set)

SELECT product_name, price
FROM sales
WHERE sale_date >= '2023-01-01' -- Efficiently filters rows BEFORE grouping

SELECT category, SUM(amount) AS total
FROM orders
GROUP BY category
HAVING SUM(amount) > 10000; -- Filters groups AFTER aggregation

-- Efficiency comparison:
-- GOOD: Filter rows first to minimize work
SELECT user_id, COUNT(*) 
FROM logs 
WHERE event_type = 'login' 
GROUP BY user_id 
HAVING COUNT(*) > 5;

-- BAD: Filtering via HAVING (inefficient because it groups everything first)
SELECT user_id, COUNT(*) 
FROM logs 
GROUP BY user_id 
HAVING event_type = 'login' AND COUNT(*) > 5;

SELECT region, AVG(temperature)
FROM weather_data
WHERE year = 2023 -- Removes irrelevant years before the expensive AVG calculation
GROUP BY region
HAVING AVG(temperature) > 25; -- Filters regions based on the calculated average

SELECT c.name, SUM(o.amount)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id 
  AND o.status = 'completed' -- This condition is part of the join logic
GROUP BY c.name
HAVING SUM(o.amount) > 100;

SELECT customer_id, COUNT(order_id)
FROM sales
WHERE category = 'Electronics' 
  AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY customer_id
HAVING COUNT(order_id) > 3;

-- Quick Reference Cheat Sheet:
-- WHERE: Operates on individual rows; cannot use aggregate functions; executes BEFORE GROUP BY.
-- HAVING: Operates on grouped rows; designed for aggregate functions; executes AFTER GROUP BY.