Where vs Having in MySQL
-
WHERE
vsHAVING
in MySQL -
the
WHERE
Clause in MySQL -
the
HAVING
Clause in MySQL -
Differences Between the
WHERE
andHAVING
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.
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. |