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
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
The HAVING Clause
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
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
Using WHERE and HAVING in the Same Query
You can use both the
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
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
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!
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?