📽 Watch Video!
What is the WHERE Clause?
The WHERE clause filters rows before any aggregation occurs. It is used to restrict which rows are selected from a table.
Example:
SELECT FirstName, LastName, State FROM Customers WHERE State = 'CA';
This query returns all customers located in California. The filtering happens before any aggregation, grouping, or calculation.
Key Points About WHERE
- Filters individual rows in a table
- Cannot be used with aggregate functions like SUM() or COUNT() directly
- Works with SELECT, UPDATE, DELETE statements
- Supports operators like =, >, <, IN, BETWEEN, LIKE, IS NULL
What is the HAVING Clause?
The HAVING clause filters groups of rows after aggregation has been applied. It works in conjunction with the GROUP BY clause.
Example:
SELECT State, COUNT(*) AS CustomerCount FROM Customers GROUP BY State HAVING COUNT(*) > 50;
This query counts customers in each state and then only returns states with more than 50 customers. The filtering occurs after the aggregation.
Key Points About HAVING
- Filters groups of rows after aggregation
- Requires GROUP BY to be meaningful (though some SQL engines allow it without GROUP BY)
- Can use aggregate functions like SUM(), COUNT(), AVG()
- Helps identify meaningful trends in grouped data
WHERE vs HAVING — Main Differences
| Feature | WHERE | HAVING |
|---|---|---|
| When applied | Before aggregation | After aggregation |
| Filters | Rows | Groups |
| Can use aggregates? | No | Yes |
| Used with | SELECT, UPDATE, DELETE | GROUP BY queries |
Practical Examples
1. Using WHERE with a Simple Filter
SELECT ProductName, Price FROM Products WHERE Price > 50;
Returns all products priced above $50 before any grouping or calculations.
2. Using HAVING with Aggregated Data
SELECT Category, AVG(Price) AS AvgPrice FROM Products GROUP BY Category HAVING AVG(Price) > 100;
Returns categories where the average product price is above $100 after aggregation.
3. Combining WHERE and HAVING
SELECT Category, COUNT(*) AS ProductCount FROM Products WHERE Price > 20 GROUP BY Category HAVING COUNT(*) > 5;
This query first filters rows with Price > 20 using WHERE, then groups them by Category, and finally only returns groups with more than 5 products using HAVING.
Common Mistakes to Avoid
- Using HAVING instead of WHERE for simple row-level filtering
- Using WHERE with aggregate functions (incorrect)
- Forgetting to include GROUP BY when using HAVING
- Confusing the order of execution (WHERE first, then GROUP BY, then HAVING)
Best Practices
- Use WHERE for filtering rows before aggregation
- Use HAVING for filtering aggregated results
- Combine WHERE and HAVING when needed for precise control
- Always test queries with sample datasets
Practice Exercises
Try these exercises to master WHERE and HAVING:
- List all customers with orders greater than $100 (use WHERE).
- Show total sales by region and filter regions with sales above $10,000 (use HAVING).
- Filter orders greater than $50 and show product categories with more than 3 such orders (combine WHERE + HAVING).
Conclusion
The SQL WHERE and HAVING clauses are essential for controlling which data is returned by queries. WHERE filters individual rows, while HAVING filters groups after aggregation. Mastering these clauses ensures your SQL queries are accurate, efficient, and meaningful.
⭐ Need an SQL Certificate, CEUs, or a FREE start with Hands-On Practice! → Get Started Here!