Where vs Having in MySQL

Mehvish Ashiq Feb 15, 2024
  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
Where vs Having 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:

where vs having in mysql - transactions data

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:

where vs having in mysql - where clause 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:

where vs having in mysql - having clause output one

Example code (with aggregate function):

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

Output:

where vs having in mysql - having clause output two

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.

The WHERE Clause The HAVING Clause
Implemented in the row (record) operations. Implemented in the column (attribute) operations.
Perform filter operation on the individual rows before aggregate calculations. Perform filter operation on aggregated (group) data.
Retrieves the specific data from specific rows that satisfy the given condition. Retrieves all data first, then separates depending on the specified condition.
We can not use aggregate methods with this clause. We can easily use aggregate methods with this clause.
It behaves like a pre-filter and comes before the GROUP BY clause. It behaves like a post-filter and comes after the GROUP BY clause.
It can be used with DELETE, SELECT, and UPDATE statements. It can only be used with the SELECT statement.
Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

Related Article - MySQL Query