Difference Between WHERE and HAVING Clauses in SQL

Q: Can WHERE clause use aggregate functions?

No, WHERE filters rows before aggregation and cannot use aggregate functions.

Q: Can HAVING clause be used without GROUP BY?

Yes, HAVING can be used without GROUP BY but is mainly intended to filter aggregated groups.

💡 Concept Name

WHERE vs HAVING Clauses – Two SQL clauses used to filter data, but at different stages of query execution and with different capabilities.

📘 Quick Intro

The WHERE clause filters rows before any grouping occurs in a query. The HAVING clause filters groups created by GROUP BY after aggregation functions have been applied. Both help narrow down results but serve distinct purposes in SQL queries.

🧠 Analogy / Short Story

Imagine a bouncer at a club checking each person before they enter—that’s the WHERE clause filtering rows early. Later, if the club wants to kick out entire groups that cause trouble, that’s like HAVING filtering groups after they have formed.

🔧 Technical Explanation

📌 WHERE filters individual rows before aggregation.
📌 HAVING filters aggregated groups after GROUP BY.
📌 WHERE cannot use aggregate functions (like SUM(), COUNT()).
📌 HAVING can filter using aggregate conditions.
📌 Both can be used together in a single query to first filter rows, then filter groups.

🎯 Purpose & Use Case

✅ Use WHERE to filter raw data before grouping or aggregation.
✅ Use HAVING to filter groups based on aggregate calculations.
✅ Combine both for precise filtering in complex queries.
✅ Improve query efficiency by filtering early with WHERE.

💻 Real Code Example

-- WHERE filters rows before aggregation
SELECT department, salary
FROM Employees
WHERE salary > 50000;

-- HAVING filters groups after aggregation
SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department
HAVING COUNT(*) > 5;

-- Combined example
SELECT department, AVG(salary) AS avg_salary
FROM Employees
WHERE salary > 30000
GROUP BY department
HAVING AVG(salary) > 60000;

❓ Interview Q&A

Q1: Can WHERE clause use aggregate functions?
A: No, WHERE filters individual rows before aggregation.

Q2: Can HAVING clause be used without GROUP BY?
A: Technically yes, but it’s mainly designed for filtering groups after aggregation.

Q3: Which clause is more efficient for filtering large datasets?
A: WHERE is more efficient since it filters rows early before grouping.

Q4: Can both WHERE and HAVING be used in the same query?
A: Yes, WHERE filters rows first, then HAVING filters aggregated groups.

Q5: What happens if HAVING is used without GROUP BY?
A: HAVING will filter on the entire result treated as a single group.

Q6: Is HAVING supported in all SQL dialects?
A: Yes, it is standard SQL and supported widely.

Q7: Can WHERE filter NULL values?
A: Yes, you can use IS NULL or IS NOT NULL in WHERE.

Q8: Can HAVING filter non-aggregated columns?
A: Generally, HAVING is for aggregates, but some DBMS allow filtering on non-aggregates if also in GROUP BY.

Q9: What’s the order of execution between WHERE and HAVING?
A: WHERE is applied before GROUP BY, HAVING after GROUP BY.

Q10: Can HAVING be replaced by WHERE?
A: No, HAVING filters aggregated data, which WHERE cannot do.

📝 MCQs

Q1. When is WHERE clause applied in SQL?

After grouping
Before grouping
After aggregation
Before SELECT

Q2. Can HAVING filter groups without GROUP BY?

No
Yes, but rarely used
Only with WHERE
Never

Q3. Can WHERE use aggregate functions?

Yes
No
Only COUNT()
Only SUM()

Q4. Which clause filters grouped records?

WHERE
GROUP BY
HAVING
ORDER BY

Q5. Can WHERE filter NULL values?

No
Yes
Only in HAVING
Only in GROUP BY

Q6. Which is more efficient for filtering rows?

HAVING
WHERE
GROUP BY
ORDER BY

Q7. What does HAVING filter?

Rows
Aggregated groups
Columns
Indexes

Q8. Can both WHERE and HAVING be used together?

No
Yes
Only in Oracle
Only in MySQL

Q9. Does HAVING work without GROUP BY?

No
Yes
Only in SQL Server
Only in PostgreSQL

Q10. What is the order of execution of WHERE and HAVING?

HAVING first, then WHERE
WHERE first, then HAVING
Simultaneously
Depends on DBMS

💡 Bonus Insight

Using WHERE to filter early in your query can improve performance significantly, especially with large datasets. Reserve HAVING for filtering aggregated results where WHERE cannot be applied.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon