WHERE vs. HAVING in SQL
WHERE Clause
The WHERE clause filters records based on the given condition. Each individual row in the table is compared with the specified condition. Then, the rows that satisfy the condition are returned or manipulated as a result of this clause.
The condition can be any Boolean expression using numeric or string values along with operators such as >, <, =, NOT, OR, LIKE, etc. It can be used within SELECT, INSERT, UPDATE and DELETE statements. The WHERE clause is placed before an optional GROUP BY clause and after FROM.
Syntax with SELECT statement:
SELECT column1
FROM table
WHERE column2 = condition;Syntax with UPDATE statement:
UPDATE table
SET column1 = value
WHERE column2 = condition;Let’s take a look at an example. Assume that there is a table with customer order information for an online store and each row is one order by a customer. What information would the query below retrieve?
SELECT order_id, name
FROM customer_orders
WHERE total >= 100;This would result in all order IDs with the name, where the order total was $100 or more.
HAVING Clause
The HAVING clause also filters records based on a condition. However, it is generally used with the GROUP BY clause, in which case it filters based on groups of rows instead of individual rows. By comparing the condition with the group’s values, it will retrieve the groups that satisfy the condition.
When not used in conjunction with GROUP BY, it behaves in the same way as the WHERE clause by processing on individual rows.
The HAVING clause can only be used within a SELECT statement. It is placed after the GROUP BY clause. It can contain aggregate functions such as SUM, AVERAGE, MIN, etc.
Syntax with SELECT statement:
SELECT column1
FROM table
GROUP BY column2
HAVING column3 = condition;Let’s see another example using the previously defined table. What will the result of this query look like?
SELECT order_id, name
FROM customer_orders
GROUP BY name
HAVING total >= 100;This would result in one order ID per name (group) that has at least one order total of $100 or more.
WHERE vs. HAVING
The main difference to remember is that a WHERE clause processes the condition on individual rows, while a HAVING clause processes it on groups of rows.
- WHERE clause: all rows that satisfy the condition
- HAVING clause: one row per group that satisfies the condition
Note: They can be used together within one SELECT statement. Check out this example:
SELECT order_id, name
FROM customer_orders
WHERE date > 2020-12-31
GROUP BY name
HAVING total >= 100;