What’s the Difference Between Where and Having in SQL?

When writing queries to filter and summarize data, it’s important for you to know the difference between WHERE and HAVING in SQL. Let’s look at the functionality associated with each keyword and compare the differences.

SQL WHERE Clause

In SQL, the WHERE clause is used to filter the rows returned from a SELECT, INSERT, UPDATE, or DELETE statement. It specifies a condition that must be met for the rows to be included in the result set. The WHERE clause operates at the row level in your query. It is applied before any aggregate functions and therefore also before any HAVING clause.

If we want to get all the records from an employees table that meet certain criteria, we would use the WHERE clause. For example, let’s filter on the department and salary.

SELECT *
FROM employees
WHERE department = 'Marketing' AND salary > 50000;

In this case, we’re not using functions like SUM to aggregate anything in our query. This will just return all the columns for every row matching the criteria in the WHERE clause.

The HAVING Clause

The HAVING clause is similar to the WHERE clause, but it is used to filter rows after the results have been grouped using a GROUP BY clause. It is often used with aggregate functions, such as COUNT, AVG, MAX, etc.

If we want to filter on the value of an aggregate function, we use HAVING to do so.

SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

In this example, the GROUP BY clause groups the rows by department, and the HAVING clause filters the groups to only include departments that have more than 5 employees. Out of necessity, the database will perform the aggregation – in this case the COUNT – and the grouping before it applies the filter specified by the HAVING clause.

Using WHERE and HAVING in the Same Query

You can use both the WHERE and HAVING clauses in the same query. The WHERE clause is used to filter rows before the GROUP BY clause is applied, while the HAVING clause is used to filter rows after the GROUP BY clause has been applied.

Here is an example of a query that uses both the WHERE and HAVING clauses:

SELECT department, COUNT(*) as num_employees
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;

In this example, the WHERE clause filters the rows to only include employees with a salary greater than 50000. The GROUP BY clause groups the remaining rows by department. The HAVING clause then filters the groups to only include departments that have more than 5 employees.

The order of the WHERE, GROUP BY, and HAVING clauses is important. The WHERE clause must come before the GROUP BY clause, and the HAVING clause must come after the GROUP BY clause.

You have the ability with these two clauses to perform powerful data analysis. You can use them independently or together to find meaningful insights into your data. Happy coding!