WHERE vs. HAVING: The Key Difference in SQL Filtering

A simple guide to a common SQL question.

Welcome! In SQL, both the **WHERE** clause and the **HAVING** clause are used to filter records, but they do so at different stages of a query. This is a common point of confusion for new coders. The simplest way to remember the difference is this: **WHERE filters rows**, while **HAVING filters groups.**

The WHERE Clause: Filtering Rows

The `WHERE` clause is your primary tool for filtering individual records in a table. It's used to specify conditions that must be met for a row to be included in the result set. It operates on the raw data **before** any grouping or aggregation (like `SUM`, `COUNT`, `AVG`) takes place.

Key points about `WHERE`:

• It's used to filter individual rows based on a condition.
• It's processed early in the query execution.
• It **cannot** contain aggregate functions.
• It can be used with `SELECT`, `UPDATE`, and `DELETE` statements.

Example: Let's say you want to find all employees in the "Sales" department.

SELECT *
FROM Employees
WHERE Department = 'Sales';
            

The HAVING Clause: Filtering Groups

The `HAVING` clause is used to filter the results of a **`GROUP BY`** clause. It's applied to the aggregated, grouped data and is used to filter those groups based on conditions involving aggregate functions. It operates **after** the `WHERE` clause and `GROUP BY` clause have been processed.

Key points about `HAVING`:

• It's used to filter groups of rows.
• It's processed late in the query execution, after `GROUP BY`.
• It **can** contain aggregate functions.
• It's almost always used with `GROUP BY` and only with `SELECT`.

Example: Let's say you want to find departments where the total number of employees is greater than 10.

SELECT Department, COUNT(EmployeeID)
FROM Employees
GROUP BY Department
HAVING COUNT(EmployeeID) > 10;
            

Putting It All Together

You can use both `WHERE` and `HAVING` in the same query. The `WHERE` clause filters the rows first, and then the `GROUP BY` clause groups the remaining rows. Finally, the `HAVING` clause filters the groups based on a condition.

For example, to find the number of employees hired after 2020 for each department with more than 5 employees:

SELECT Department, COUNT(EmployeeID)
FROM Employees
WHERE HireDate > '2020-01-01'
GROUP BY Department
HAVING COUNT(EmployeeID) > 5;
            

Remembering this simple distinction between filtering **rows** and filtering **groups** will help you write more accurate and efficient SQL queries.

Master SQL with more of our coding tutorials!