Understanding WHERE vs HAVING

SQL provides WHERE and HAVING to filter data, but they operate at different logical stages.

Imagine a company wants to know which departments have more than five employees.

First, SQL needs to collect employees into department groups. Then it can count the employees in each department and decide which groups qualify.

Now imagine the company only wants to count active employees. SQL should exclude inactive employees before calculating those counts.

These are two different requirements:

- WHERE decides which individual rows participate in the query.
- HAVING decides which groups remain after grouping.

A simple memory trick:

WHERE = filter rows.
HAVING = filter groups.

This distinction is important for reports, dashboards, analytics, and interview questions.

WHERE evaluates conditions against individual rows before the grouping stage.

General syntax:

SELECT column_name
FROM table_name
WHERE condition;

Example:

SELECT employee_id, department, salary
FROM employees
WHERE salary > 50000;

This returns employees whose salary is greater than 50,000.

No grouping is required because the condition is about each employee's own salary.

Other examples:

WHERE status = 'Completed'
WHERE age >= 18
WHERE department = 'Engineering'
WHERE salary IS NOT NULL

Important: a row that does not satisfy WHERE is excluded from the rows that proceed through the rest of that query.

HAVING evaluates conditions on groups created by GROUP BY. It is commonly used with aggregate functions.

General syntax:

SELECT grouping_column, aggregate_function(column_name)
FROM table_name
GROUP BY grouping_column
HAVING aggregate_condition;

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

The query:
1. Groups employees by department.
2. Calculates the number of rows in each department.
3. Keeps only departments with more than five rows.

A department with five employees is excluded because the condition uses > 5, not >= 5.

Compare these two queries.

Query A:

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department;

This counts only employees whose salary is above 50,000 in each department.

Query B:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

This counts all employees in each department and then keeps departments with more than five employees.

The queries answer different questions.

WHERE changes which rows are counted.
HAVING changes which completed groups are returned.

Do not replace one clause with the other without checking the requirement.

A useful simplified logical processing order for a grouped SELECT query is:

1. FROM — identify the source tables and joins.
2. WHERE — filter individual rows.
3. GROUP BY — form groups.
4. HAVING — filter groups.
5. SELECT — determine the output expressions.
6. ORDER BY — sort the output.
7. LIMIT / OFFSET — restrict the returned rows when used.

This order explains why an aggregate condition such as COUNT(*) > 5 belongs in HAVING rather than WHERE.

For example:

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 2
ORDER BY employee_count DESC
LIMIT 10;

Interpretation:
- Exclude employees earning 50,000 or less.
- Group the remaining employees by department.
- Keep groups with more than two remaining employees.
- Sort the qualifying departments by count.
- Return at most ten rows.

This is the logical model used to understand SQL semantics. Database engines can choose different physical execution strategies while preserving the query's required meaning.

Suppose employees contains these illustrative rows:

| employee_id | department | salary | status |
|---:|---|---:|---|
| 1 | IT | 60000 | Active |
| 2 | IT | 40000 | Active |
| 3 | IT | 70000 | Inactive |
| 4 | HR | 45000 | Active |
| 5 | HR | 55000 | Active |
| 6 | HR | 65000 | Active |
| 7 | Sales | 80000 | Active |

Requirement:
Count active employees with salary above 50,000 in each department, but return only departments with at least two qualifying employees.

Query:

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE status = 'Active'
AND salary > 50000
GROUP BY department
HAVING COUNT(*) >= 2;

Step 1 — WHERE:
- IT: employee 1 remains; employees 2 and 3 are excluded.
- HR: employee 5 remains; employee 4 is excluded.
- Sales: employee 7 remains.

Step 2 — GROUP BY:
- IT has one qualifying employee.
- HR has one qualifying employee.
- Sales has one qualifying employee.

Step 3 — HAVING:
Each group has a count of one, so each group fails COUNT(*) >= 2.

Final result: zero rows.

This is intentional. The query correctly follows the requirement even though it returns an empty result.

Real reporting queries frequently need both clauses.

Scenario:
Find product categories with at least five completed orders and completed-order sales of at least 2,000.

Use WHERE to select completed orders.
Use GROUP BY to create one group per category.
Use HAVING to filter each category's count and total.

Both HAVING conditions must be true because they are joined with AND.

This query is invalid in PostgreSQL:

SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department;

The reason is that WHERE filters input rows before the group count is available.

The correct version is:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Important nuance: an aggregate may be calculated inside a subquery, and the outer query can then filter the resulting ordinary column with WHERE.

For example:

SELECT department, employee_count
FROM (
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
) AS department_totals
WHERE employee_count > 5;

The outer WHERE works because employee_count is now a column produced by the inner query.

Yes. PostgreSQL permits HAVING in an aggregate query without an explicit GROUP BY.

Example:

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;

The query calculates one overall count. HAVING decides whether that overall aggregate result should be returned.

If the count is 125, the query returns one row containing 125.
If the count is 80, it returns no rows.

Compare that with:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

The second query evaluates a separate count for each customer group.

Use HAVING without GROUP BY only when filtering the overall aggregate result is what you intend.

SQL comparisons can evaluate to TRUE, FALSE, or UNKNOWN. A row or group is retained by a filter only when the condition evaluates to TRUE.

For example:

WHERE salary > 50000

A row with salary = NULL does not pass this condition because NULL > 50000 is UNKNOWN.

Aggregate functions also handle NULL values differently:

- COUNT(*) counts all rows.
- COUNT(salary) counts non-NULL salary values.
- AVG(salary) ignores NULL salary values.
- SUM(amount) ignores NULL amount values and returns NULL if there are no non-NULL inputs.

For example:

SELECT department, SUM(bonus) AS total_bonus
FROM employees
GROUP BY department
HAVING SUM(bonus) > 0;

A department where every bonus is NULL does not pass the HAVING condition because the sum is NULL and NULL > 0 is UNKNOWN.

Do not automatically treat missing data as zero unless that matches the business meaning.

When a condition applies to individual source rows, putting it in WHERE can reduce the amount of data that must be grouped.

For example:

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING COUNT(*) > 5;

Only active employees contribute to the counts.

If the requirement is specifically to count active employees, this is the appropriate query.

Do not move a condition from HAVING to WHERE merely to make it look faster. The two clauses can change the meaning of a query.

For example:

HAVING SUM(amount) > 1000

cannot generally be replaced by:

WHERE amount > 1000

The first tests the total of each group. The second removes individual rows with amounts of 1,000 or less before calculating the total. They answer different questions.

Use EXPLAIN to inspect a PostgreSQL execution plan. EXPLAIN ANALYZE actually executes the query, so use it thoughtfully, particularly for expensive statements or queries with side effects.

Indexes can help with suitable row-level filters, joins, and access patterns, but their benefit depends on selectivity, table size, statistics, and the plan selected by PostgreSQL.

Trap 1:
"WHERE is always faster than HAVING."

Not necessarily. The correct clause is determined by the requirement. A suitable WHERE condition may reduce input rows, but performance must be evaluated using the query plan.

Trap 2:
"HAVING is only valid when GROUP BY is present."

False in PostgreSQL. HAVING can filter the single aggregate group in a query without GROUP BY.

Trap 3:
"WHERE salary > 50000 and HAVING AVG(salary) > 50000 mean the same thing."

False. The first excludes individual salaries at or below 50,000 before aggregation. The second tests the average salary of each group.

Trap 4:
"Having a GROUP BY automatically sorts the results."

False. Use ORDER BY for a requested order.

Trap 5:
"Every group that appears in the source table will appear in the output."

False. WHERE can remove all rows for a group, and HAVING can remove groups that fail its condition.

Trap 6:
"An empty result means the SQL query failed."

False. A valid query can return zero rows when no rows or groups satisfy its conditions.

Assume the following PostgreSQL tables exist:

employees(employee_id, department, salary, status)

orders(order_id, customer_id, category, status, amount)

Write each query before checking the solution section.

Task 1:
Return employees whose salary is at least 40,000.

Task 2:
Return departments with more than five employees.

Task 3:
Return departments with more than five active employees. Count only active employees.

Task 4:
Return categories with at least three completed orders.

Task 5:
Return completed-order categories whose total sales exceed 5,000.

Task 6:
Return departments with at least three employees and an average salary greater than 45,000.

Task 7:
Explain why this query is invalid in PostgreSQL:

SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department;

Task 8:
Explain how a subquery lets you filter a calculated count with an outer WHERE.

Task 9:
What is the difference between filtering salaries with WHERE salary > 50000 and filtering groups with HAVING AVG(salary) > 50000?

Task 10:
Can a valid HAVING query return zero rows? Give a reason.

Task 1 — Filter individual employees:

SELECT employee_id, department, salary
FROM employees
WHERE salary >= 40000;

Task 2 — Filter departments by employee count:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Task 3 — Count active employees only:

SELECT department, COUNT(*) AS active_employee_count
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING COUNT(*) > 5;

Task 4 — Categories with at least three completed orders:

SELECT category, COUNT(*) AS completed_order_count
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING COUNT(*) >= 3;

Task 5 — Completed sales above 5,000:

SELECT category, SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING SUM(amount) > 5000;

Task 6 — Departments with a sufficient count and average:

SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3
AND AVG(salary) > 45000;

Task 7:
WHERE operates before grouping, so the grouped COUNT(*) is not available there. Use HAVING COUNT(*) > 5.

Task 8:
Calculate the grouped count in an inner query. The outer query treats the count as a normal output column and can filter it with WHERE.

Task 9:
WHERE salary > 50000 filters individual employees before aggregation. HAVING AVG(salary) > 50000 tests each group's average and can include employees earning less than 50,000.

Task 10:
Yes. HAVING can exclude all groups when none satisfies its condition.

Choose the best answer for each question.

Q1. What is the difference between WHERE and HAVING?
Answer: WHERE filters individual source rows before grouping. HAVING filters groups after grouping and aggregate evaluation.

Q2. Can WHERE and HAVING be used together?
Answer: Yes. WHERE selects source rows, and HAVING filters the groups formed from those rows.

Q3. Why can an aggregate condition not normally appear in WHERE?
Answer: WHERE is logically evaluated before the aggregate result is available at that query level.

Q4. How do you find departments with more than five employees?
Answer: Group employees by department and use HAVING COUNT(*) > 5.

Q5. What does WHERE status = 'Active' do before GROUP BY?
Answer: It excludes inactive rows from the aggregation.

Q6. What does HAVING AVG(salary) > 50000 mean?
Answer: It retains groups whose average non-NULL salary exceeds 50,000.

Q7. Does HAVING always make a query slower?
Answer: No. The correct clause depends on query semantics, and performance depends on the execution plan and data.

Q8. Can HAVING be used without GROUP BY in PostgreSQL?
Answer: Yes. In an aggregate query without GROUP BY, it can filter the overall aggregate result.

Q9. What happens if no group passes HAVING?
Answer: The query returns no result rows.

Q10. Does GROUP BY automatically sort the results?
Answer: No. Use ORDER BY to request a specific order.

Q11. What is the difference between COUNT(*) and COUNT(column)?
Answer: COUNT(*) counts all rows; COUNT(column) counts rows where that column is not NULL.

Q12. How does NULL affect WHERE salary > 50000?
Answer: If salary is NULL, the comparison is UNKNOWN, so the row does not pass the WHERE filter.

Q13. Can an outer WHERE filter an aggregate calculated in a subquery?
Answer: Yes. The outer query sees the subquery's output columns as its input rows.

Q14. Why can moving a condition from HAVING to WHERE change results?
Answer: WHERE removes individual rows before aggregation, while HAVING removes completed groups after aggregation.

Q15. What is the role of EXPLAIN in PostgreSQL?
Answer: It displays the planned execution strategy and helps investigate query performance.

Q16. Does EXPLAIN ANALYZE execute the query?
Answer: Yes. It runs the statement and reports actual execution information.

Q17. Why use parentheses with AND and OR?
Answer: Parentheses make the intended logic explicit and help prevent accidental changes in filtering behavior.

Q18. What should you consider before replacing HAVING with WHERE?
Answer: Check whether the condition is row-level or aggregate-level and confirm that the rewritten query preserves the intended result.

You should now be able to decide between WHERE and HAVING confidently.

Final checklist:

- WHERE filters individual rows.
- GROUP BY forms groups.
- Aggregate functions calculate group-level summaries.
- HAVING filters groups.
- WHERE can reduce the rows used in aggregation.
- HAVING is necessary for conditions that depend on aggregate results at the same query level.
- A subquery or CTE can expose aggregate results as columns for an outer WHERE.
- COUNT(*), COUNT(column), SUM, and AVG handle NULL values differently.
- HAVING can be used without an explicit GROUP BY in PostgreSQL aggregate queries.
- ORDER BY controls the requested output order.
- EXPLAIN can help investigate query performance.
- Logical processing order explains query meaning, but it does not dictate the exact physical execution plan.

Before writing a query, ask:

1. Am I filtering individual records?
2. Or am I filtering a calculated group summary?

If you are filtering individual records, start with WHERE.
If you are filtering groups using aggregate results, use HAVING.

Review these mistakes before moving to the next lesson.