JIYIK CN >

Today, we will understand the difference between the WHEREand clauses in MySQL . We will understand these clauses individually with code examples and compare them in a tabular form to highlight the differences.HAVING

In WHEREMySQLHAVING

WHEREThe and HAVINGclauses are very similar. The main difference between these clauses is GROUP BYwhen they are used with .

We cannot use the clause with aggregate data WHERE, but we can use the HAVING.

We can say that the AND WHEREclause filters the records(rows) before grouping, but HAVINGthe IF clause excludes the records(rows) after grouping.

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

Therefore, create a transactionstable with four attributes named ID, Product, MonthOfTransactionand .AmountInUSD

Sample 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:

WHEREClause in MySQL

In MySQL, we use WHEREthe EXEC clause to filter records and extract only those rows (records) that meet the specified condition. We can use it with SELECTthe EXEC statement and UPDATEthe , INSERTand DELETEcommands.

WHEREThe clause involves JOINspecific conditions placed on the selected columns while retrieving records from single or multiple tables using the IN clause. We can WHEREperform logical operations such as AND, , NOT.OR

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

Sample code:

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

Output:

HAVINGClause in MySQL

In MySQL, HAVINGthe clause is used in conjunction with GROUP BYthe clause. The purpose of using this clause is to perform column operations and apply to aggregate data or group based on given conditions.

HAVINGThe clause returns only results for groups that meet the specified condition. If used WHEREwith HAVINGthe clause, WHEREfilters a single record (row).

Then, the records (rows) are grouped, aggregate calculations are performed, and finally, HAVINGthe groups are filtered. HAVINGThe clause checks GROUP BYthe conditions of the groups created by the clause.

In the absence GROUP BYof a clause, HAVINGthe clause behaves like WHEREa clause.

We can also use various aggregate functions by combining HAVINGthe clause with the statement. The aggregate (group) methods include , , , , and .SELECTSUMMAXMINCOUNTAVG

We can easily HAVINGuse aggregate functions with the clause whereas if WHEREused with the clause we will get an error 组函数的无效使用i.e.

Sample code (without aggregate functions):

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

Output:

Sample code (with aggregate functions):

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

Output:

When we have multiple clauses in a query, it is important to understand the order of execution. We must remember the order FWGHSOL( Fstarting with and Lending with ) to know the order of execution, where F = FROM, W = WHERE, G = GROUP BY, H = HAVING, S = SELECT, O = ORDER BYand L = LIMIT.

Difference between WHEREand clauses in MySQLHAVING

While writing queries to manipulate data, we have to consider the following points.