Data · SQL

The difference between WHERE and HAVING in SQL

Published

6 orders, 2 of them under $30: how many does WHERE amount >= 30 keep?

From an order kept by WHERE to a group kept by HAVING, in five steps:

  1. Filter orders with WHERE
  2. Filter groups with HAVING
  3. Tell an amount from a sum
  4. Run WHERE, then HAVING
  5. Practice

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
orders
idcustomeramount
101Emma$40
102Noah$25
103Mia$60
104Emma$15
105Noah$20
106Mia$35
107Emma$50
result
customerCOUNT(*)
Emma2
Mia2
  1. Read the amount column row by row. An order stays if its amount is at least $30.

    orders

  2. GROUP BY groups the 4 remaining orders by customer. The result has one row per customer.

    rows

  1. SELECT customer, COUNT(*)
    FROM orders
    WHERE amount >= 50
    GROUP BY customer
    orders
    idcustomeramount
    101Emma$65
    102Noah$30
    103Ava$55
    104Emma$20
    105Emma$55

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
orders
idcustomeramount
101Mia$30
102Emma$45
103Noah$20
104Mia$55
105Liam$35
106Emma$25
107Mia$40
result
customerCOUNT(*)
Emma2
Mia3
  1. COUNT(*) counts the rows of a group. Look for Mia in the customer column.

    orders

  2. Count each customer’s orders the same way. A group comes out if its COUNT(*) is at least 2.

    rows

  1. SELECT customer, COUNT(*)
    FROM orders
    GROUP BY customer
    HAVING COUNT(*) >= 2
    orders
    idcustomeramount
    101Noah$75
    102Liam$55
    103Emma$55
    104Noah$50
    105Liam$30
    106Noah$90

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
orders
idcustomeramount
101Noah$30
102Emma$70
103Mia$20
104Noah$35
105Mia$25
106Liam$45
result
customerSUM(amount)
Emma$70
Noah$65
  1. SUM(amount) adds up the amounts of all of Noah’s orders.

  2. Add up each customer’s orders the same way. A group comes out if its sum is at least $60.

    rows

  3. With WHERE amount >= 60 instead of HAVING, which customer would still come out?

  1. SELECT customer, SUM(amount)
    FROM orders
    GROUP BY customer
    HAVING SUM(amount) >= 70
    orders
    idcustomeramount
    101Noah$30
    102Ava$80
    103Noah$45
    104Ava$35
    105Noah$20
    106Ava$90
    107Mia$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
orders
idcustomeramount
101Emma$50
102Liam$35
103Emma$20
104Noah$60
105Liam$45
106Emma$15
107Noah$25
result
customerCOUNT(*)
Liam2
  1. Emma has 3 orders. WHERE keeps the ones of at least $30.

    order

  2. Does Emma show up in the result?

  3. Do the same for Noah and for Liam. A group comes out if it keeps at least 2 orders after WHERE.

    row

  1. SELECT customer, COUNT(*)
    FROM orders
    WHERE amount >= 40
    GROUP BY customer
    HAVING COUNT(*) >= 2
    orders
    idcustomeramount
    101Liam$45
    102Liam$65
    103Ava$85
    104Ava$15
    105Liam$75
    106Noah$70
    107Noah$75
    108Mia$65

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.

  1. SELECT customer, COUNT(*)
    FROM orders
    WHERE amount >= 50
    GROUP BY customer
    orders
    idcustomeramount
    101Mia$15
    102Mia$30
    103Mia$40
    104Liam$85
    105Ava$20
    106Liam$85
    107Ava$45