Where vs Having in MySQL

1. WHERE vs HAVING in MySQL
2. the WHERE Clause in MySQL
3. the HAVING Clause in MySQL
4. Differences Between the WHERE and HAVING Clauses in MySQL

Today, we will learn about the differences between the WHERE and HAVING clauses in MySQL. We will understand the clauses individually with code examples and compare them in tabular form to highlight the differences.

WHERE vs HAVING in MySQL

The WHERE and HAVING clauses are pretty similar. The primary difference between these clauses occurs when they are used with GROUP BY.

We can not use the WHERE clause with aggregated data, but HAVING can be used.

We can say that WHERE filters the records (rows) before grouping, but the HAVING clause excludes the records (rows) after grouping.

To continue step-by-step with this article, we should have a table.

So, create a transactions table containing four attributes named ID, Product, MonthOfTransaction, and AmountInUSD.

Example code:

#create a table
CREATE TABLE `ms20`.`transactions` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `Product` VARCHAR(45) NOT NULL,
    `MonthOfTransaction` VARCHAR(20) NOT NULL,
    `AmountInUSD` INT NOT NULL,
    PRIMARY KEY (`ID`));

#insert data into a table
INSERT INTO ms20.transactions(Product, MonthOfTransaction, AmountInUSD) VALUES
('Air Conditioner', 'January', 500),
('Television', 'January', 600),
('Refrigerator', 'January', 550),
('Television', 'March', 600),
('Air Conditioner', 'March', 500),
('Juicer Machine', 'March', 200);

#select all data from the table
SELECT * FROM ms20.transactions;

Output:

the WHERE Clause in MySQL

In MySQL, we use the WHERE clause to filter the records and extract only those rows (records) that meet the specified condition. We can use it with SELECT statements and the UPDATE, INSERT, and DELETE commands.

The WHERE clause concerns a particular condition placed on the selected columns while retrieving records from single or multiple tables using the JOIN clause. We can perform logical operations in the WHERE clause, for instance, AND, NOT, OR.

We can also call them Boolean conditions that must be true while retrieving the information from the table (also called relation). These logical operators use comparison operators, including <, >, <=, >=, =, and <>.

Example code:

SELECT Product, sum(AmountInUSD) AS Total
FROM ms20.transactions
WHERE Product in ( 'Television', 'Refrigerator')
GROUP BY Product;

Output:

the HAVING Clause in MySQL

In MySQL, the HAVING clause is combined with the GROUP BY clause. The aim to use this clause is to do column operation and applied on aggregated data or groups as per the given conditions.

The HAVING clause only returns those results from groups that fulfill a particular condition. If the WHERE and HAVING clauses are used together, the WHERE filters the individual records (rows).

Then, the records (rows) are grouped, the aggregate calculations are performed, and finally, HAVING filters the groups. The HAVING clause checks the condition of the groups that the GROUP BY clause created.

In the absence of the GROUP BY clause, the HAVING clause behaves like the WHERE clause.

We can also use various aggregate functions by combining the HAVING clause with the SELECT statement. The aggregate (group) methods include SUM, MAX, MIN, COUNT, and AVG.

We can easily use the aggregate functions with the HAVING clause, while we will get an error as an invalid use of group function if used with the WHERE clause.

Example code (without aggregate function):

SELECT Product, sum(AmountInUSD) AS Total
FROM ms20.transactions
GROUP BY Product
HAVING Product in ('Television', 'Refrigerator');

Output:

Example code (with aggregate function):

SELECT Product, sum(AmountInUSD) AS Total
FROM ms20.transactions
GROUP BY Product
HAVING sum(AmountInUSD) > 800;

Output:

It is important to know the execution order when we have multiple clauses in one query. We must remember the order FWGHSOL (starts from F and ends at L) to know the execution sequence where F = FROM, W = WHERE, G = GROUP BY, H = HAVING, S = SELECT, O = ORDER BY, and L = LIMIT.

Differences Between the WHERE and HAVING Clauses in MySQL

We must consider the following points while writing the queries to manipulate data.