WHERE vs HAVING, let's understand the difference

Both do filtering, but in different ways

At the start of my SQL learning journey, I had difficulties getting my head around the WHERE and HAVING clauses. I understood they were both for filtering but didn’t quite understand when to use them.

In this post, I hope to bring clarity to those of you who may be in the same position as I once was.

Let’s start with understanding each clause separately.

The WHERE clause

We use the WHERE clause to filter individual rows that are returned by the FROM clause in a query.

The HAVING clause

We use the HAVING clause to filter groups. So the HAVING clause is only processed after the rows have been grouped.

Now let’s understand the relationship between the WHERE and HAVING clauses and the GROUP BY clause.

WHERE and HAVING in respect to the GROUP BY clause

It helps to keep in mind where these clauses (WHERE and HAVING) are processed in the logical order of processing.

As we know, the logical order of processing for a query generally follows the below order:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Here we can see that the WHERE clause is processed before the GROUP BY clause and the HAVING clause is processed after the GROUP BY clause. This is why we say WHERE filters on un-grouped individual rows and HAVING filters on groups, as HAVING is processed after the rows have been grouped by the GROUP BY clause.

Earlier this week I posted some reels comparing WHERE and HAVING on my Instagram (@sql.handbook). Feel free to check them out below for further learning.

NOTE: I have attached the code that I have used in the below reels for you guys to copy and paste into your local environments and play around with them.

What is the difference between WHERE and HAVING in SQL?

sql.handbook

A post shared by @sql.handbook

SQL Demo: WHERE vs HAVING

sql.handbook

A post shared by @sql.handbook

Code for creating the table used in the Demo video above:

CREATE TABLE OrderDetails (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID CHAR(1),
    Amount DECIMAL(10, 2),
    OrderDate DATE
);

INSERT INTO OrderDetails (OrderID, CustomerID, ProductID, Amount, OrderDate) VALUES
(1, 101, 'A', 100.00, '2024-01-01'),
(2, 102, 'B', 200.00, '2024-01-05'),
(3, 101, 'C', 150.00, '2024-01-10'),
(4, 103, 'A', 300.00, '2024-01-15'),
(5, 104, 'B', 250.00, '2024-02-01'),
(6, 101, 'A', 120.00, '2024-02-03'),
(7, 105, 'C', 180.00, '2024-02-10'),
(8, 106, 'A', 130.00, '2024-03-01'),
(9, 102, 'B', 220.00, '2024-03-05'),
(10, 107, 'C', 200.00, '2024-03-10'),
(11, 104, 'A', 160.00, '2024-03-15'),
(12, 105, 'B', 190.00, '2024-03-20'),
(13, 101, 'C', 130.00, '2024-04-01'),
(14, 108, 'A', 250.00, '2024-04-05'),
(15, 109, 'B', 300.00, '2024-04-10'),
(16, 110, 'C', 210.00, '2024-04-15'),
(17, 102, 'A', 180.00, '2024-04-20'),
(18, 101, 'B', 200.00, '2024-05-01'),
(19, 104, 'C', 250.00, '2024-05-05'),
(20, 105, 'A', 160.00, '2024-05-10');

Code that is used in the Demo reel:

-- WHERE and HAVING Demo

SELECT *
FROM OrderDetails

/* Task 1 - 
Find all orders 
after the 2024-03-10 */

SELECT *
FROM OrderDetails
WHERE OrderDate > '2024-03-10'

/* Task 2 - 
Find the products 
that made in total more 
than 1200 dollars */

SELECT productid
	, SUM(Amount) As totalAmount
FROM OrderDetails
GROUP BY productid
HAVING SUM(Amount) > 1200

Hope this information helped, please feel free to email me back any questions on this topic if you get confused at all.

Have a great week ahead guys!