Data · SQL
The difference between WHERE and HAVING in SQL
6 orders, 2 of them under $30: how many does WHERE amount >= 30 keep?
Emma has 3 of those 6 orders, including the 2 under $30. How many are left in her group after WHERE?
From an order kept by WHERE to a group kept by HAVING, in five steps:
What does WHERE do in a query with GROUP BY?
With WHERE amount >= 100, a customer whose orders are all under $100 disappears: WHERE removes their rows before GROUP BY builds the groups.
Let’s take seven orders and three customers.
SELECT customer, COUNT(*) FROM orders WHERE amount >= 30 GROUP BY customer
| id | customer | amount |
|---|---|---|
| 101 | Emma | $40 |
| 102 | Noah | $25 |
| 103 | Mia | $60 |
| 104 | Emma | $15 |
| 105 | Noah | $20 |
| 106 | Mia | $35 |
| 107 | Emma | $50 |
| customer | COUNT(*) |
|---|---|
| Emma | 2 |
| Mia | 2 |
SELECT customer, COUNT(*) FROM orders WHERE amount >= 50 GROUP BY customer
orders id customer amount 101 Emma $65 102 Noah $30 103 Ava $55 104 Emma $20 105 Emma $55 SELECT customer, COUNT(*) FROM orders WHERE amount >= 40 GROUP BY customer
orders id customer amount 101 Ava $65 102 Noah $35 103 Emma $50 104 Ava $45 105 Mia $75 106 Ava $65 107 Emma $70 108 Mia $15
What does HAVING do in a SQL query?
HAVING judges each group once it is built: with HAVING COUNT(*) >= 7, a customer comes out if they have at least 7 orders.
Let’s take seven orders and four customers.
SELECT customer, COUNT(*) FROM orders GROUP BY customer HAVING COUNT(*) >= 2
| id | customer | amount |
|---|---|---|
| 101 | Mia | $30 |
| 102 | Emma | $45 |
| 103 | Noah | $20 |
| 104 | Mia | $55 |
| 105 | Liam | $35 |
| 106 | Emma | $25 |
| 107 | Mia | $40 |
| customer | COUNT(*) |
|---|---|
| Emma | 2 |
| Mia | 3 |
SELECT customer, COUNT(*) FROM orders GROUP BY customer HAVING COUNT(*) >= 2
orders id customer amount 101 Noah $75 102 Liam $55 103 Emma $55 104 Noah $50 105 Liam $30 106 Noah $90 SELECT customer, COUNT(*) FROM orders GROUP BY customer HAVING COUNT(*) >= 3
orders id customer amount 101 Emma $80 102 Mia $50 103 Emma $25 104 Mia $10 105 Emma $60 106 Mia $40 107 Ava $30 108 Emma $35
Should you filter an amount with WHERE or HAVING?
WHERE judges each order on its own, HAVING judges the customer’s total: two orders of $70 pass HAVING SUM(amount) >= 120, but neither one passes WHERE amount >= 120.
Let’s take six orders and four customers.
SELECT customer, SUM(amount) FROM orders GROUP BY customer HAVING SUM(amount) >= 60
| id | customer | amount |
|---|---|---|
| 101 | Noah | $30 |
| 102 | Emma | $70 |
| 103 | Mia | $20 |
| 104 | Noah | $35 |
| 105 | Mia | $25 |
| 106 | Liam | $45 |
| customer | SUM(amount) |
|---|---|
| Emma | $70 |
| Noah | $65 |
With
WHERE amount >= 60instead of HAVING, which customer would still come out?
SELECT customer, SUM(amount) FROM orders GROUP BY customer HAVING SUM(amount) >= 70
orders id customer amount 101 Noah $30 102 Ava $80 103 Noah $45 104 Ava $35 105 Noah $20 106 Ava $90 107 Mia $20 SELECT customer, SUM(amount) FROM orders WHERE amount >= 70 GROUP BY customer
orders id customer amount 101 Noah $30 102 Ava $80 103 Noah $45 104 Ava $35 105 Noah $20 106 Ava $90 107 Mia $20
Which runs first in SQL, WHERE or HAVING?
HAVING counts what WHERE left behind: a customer with 8 orders, 7 of them under $10, has only one left after WHERE amount >= 10.
Let’s take seven orders and three customers.
SELECT customer, COUNT(*) FROM orders WHERE amount >= 30 GROUP BY customer HAVING COUNT(*) >= 2
| id | customer | amount |
|---|---|---|
| 101 | Emma | $50 |
| 102 | Liam | $35 |
| 103 | Emma | $20 |
| 104 | Noah | $60 |
| 105 | Liam | $45 |
| 106 | Emma | $15 |
| 107 | Noah | $25 |
| customer | COUNT(*) |
|---|---|
| Liam | 2 |
Does Emma show up in the result?
SELECT customer, COUNT(*) FROM orders WHERE amount >= 40 GROUP BY customer HAVING COUNT(*) >= 2
orders id customer amount 101 Liam $45 102 Liam $65 103 Ava $85 104 Ava $15 105 Liam $75 106 Noah $70 107 Noah $75 108 Mia $65 SELECT customer, SUM(amount) FROM orders WHERE amount >= 30 GROUP BY customer HAVING SUM(amount) >= 90
orders id customer amount 101 Mia $65 102 Mia $25 103 Mia $45 104 Liam $65 105 Liam $25 106 Emma $90 107 Ava $45
How do you read a WHERE and HAVING query quickly?
It goes faster if you first set aside the orders WHERE rejects: only the ones left get grouped by customer, then HAVING judges each group.
SELECT customer, COUNT(*) FROM orders WHERE amount >= 50 GROUP BY customer
orders id customer amount 101 Mia $15 102 Mia $30 103 Mia $40 104 Liam $85 105 Ava $20 106 Liam $85 107 Ava $45 SELECT customer, COUNT(*) FROM orders GROUP BY customer HAVING COUNT(*) >= 2
orders id customer amount 101 Liam $65 102 Ava $80 103 Mia $55 104 Emma $80 105 Ava $85 106 Mia $80 107 Mia $25 SELECT customer, COUNT(*) FROM orders WHERE amount >= 50 GROUP BY customer HAVING COUNT(*) >= 2
orders id customer amount 101 Emma $40 102 Ava $35 103 Ava $40 104 Emma $75 105 Mia $80 106 Ava $50 107 Ava $75 SELECT customer, SUM(amount) FROM orders WHERE amount >= 30 GROUP BY customer HAVING SUM(amount) >= 80
orders id customer amount 101 Emma $90 102 Ava $20 103 Mia $85 104 Emma $45 105 Liam $90 106 Liam $65 107 Ava $65