Introduction to MySQL having
In real-time applications, we often need to generate complex reports from the data saved in the database and retrieve the filtered records to display the report to the user. For this, your SQL query must be optimized and correct so that the application’s performance is not hampered even if a large amount of data is present in the tables. The SELECT query constructed for such reports needs to use the functionalities provided in MySQL.
Having a clause is one such functionality that helps apply the expressions’ filters. These expressions can consist of a single column, multiple columns, or even conditions applied to the grouped aggregated data retrieved using the GROUP BY clause. In this article, we will learn about the syntax of the HAVING clause, its evaluation order while execution, and study some examples to get a grip on the usage of the HAVING clause in your queries for retrieval of data.
Syntax:
The following SELECT query shows the syntax and placement of the HAVING clause in it –
SELECT
list_of_expressions
FROM
name_of_table
WHERE
restrictions_and_conditions
GROUP BY
expressions_for_grouping
HAVING
condition_or_filter_on_grouped_expressions;In the above syntax –
- list_of_expressions – The comma-separated list of the columns and the other expressions, such as aggregated values or manipulated values such as product, etc., that the query needs to retrieve from the table data.
- name_of_table – The table’s name resides in your database and from where the summarized data needs to be retrieved for your use case.
- restrictions_and_conditions – These are the conditions you can specify on the table columns that need to be fulfilled while retrieving the data.
- expressions_for_grouping – The resultset retrieved from the query structure above the GROUP BY clause can be summarized and grouped based on certain expressions, including columns and aggregated values of columns as the expressions_for_grouping.
- condition_or_filter_on_grouped_expressions – The WHERE clause only applies restrictions on the individual records or row of the column. We can use the HAVING clause to apply filters and restrictions and specify conditions on the grouped expressions of the resultset.
In the case of the HAVING clause, the restriction is applied to the grouped values retrieved instead of a single row of the table. When we do not use the GROUP BY clause in the SELECT query, the HAVING clause behaves in the same manner as that of the WHERE clause and applies restriction on individual row-level of the table as grouping expression is absent. If the filter condition specified in the HAVING clause evaluates to true, the grouped record is included in the final resultset based on the GROUP BY clause. Conversely, if the condition evaluates to false, the record is excluded.
Evaluation order:
The evaluation order followed by standard SQL is different than that of MySQL. Evaluation order determines which and when clause will be considered for execution when the SELECT query contains multiple clauses. The diagram below illustrates the order in which MySQL evaluates the clauses of the SELECT query.
Here, we observe that the evaluation of the HAVING clause takes place after the FROM, WHERE, SELECT, and GROUP BY clauses, but before the LIMIT and ORDER BY clauses. However, it is important to note that in the SQL standard, the evaluation order differs. In this case, the execution of the HAVING clause occurs after the GROUP BY clause and before the SELECT clause.
Example of MySQL having
For example, we will create a table named educba_articles that will contain the details about the articles, such as id, name, author, rate, pages, month, and status.
CREATE TABLE educba_articles (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
articlename varchar(10) NOT NULL,
author varchar(10) NOT NULL,
rate decimal(5,2) DEFAULT NULL,
month varchar(10) NOT NULL,
status varchar(10) NOT NULL,
pages INTEGER DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;that gives the following output after execution –
Now, we will insert some records in it –
INSERT INTO `educba_articles` VALUES
(1, 'sp', 'Payal', 125.65, 'May','Submitted' ,3),
(2,'having', 'Vyankatesh', 326.22, 'June','Pending' ,2),
(3,'something', 'Omprakash', 123.22, 'July','Approved' ,2),
(4, 'anything','sakshi', 645.54 ,'May','Pending' ,3),
(5, 'everything','prerna', 356.54 ,'June','Submitted' ,4),
(6,'events', 'Vyankatesh', 326.22, 'June','Approved' ,2),
(7,'group by', 'Omprakash', 123.22, 'July','Approved' ,2),
(8, 'from','sakshi', 645.54, 'May','Pending' ,3),
(9, 'where','prerna', 356.54 ,'June','Submitted' ,4),
(10, 'limit ', 'Payal', 125.65, 'May','Submitted' ,3),
(11, 'coalesce ', 'Payal', 125.65, 'May','Submitted' ,3),
(12,'order by', 'Vyankatesh', 326.22, 'June','Approved' ,2),
(13,'datatypes', 'Omprakash', 123.22, 'July','Approved' ,2),
(14, 'varchar','sakshi', 645.54 ,'May','Pending' ,3),
(15, 'integer','prerna', 356.54 ,'June','Approved' ,4),
(16,'date', 'Vyankatesh', 326.22, 'June','Pending' ,2),
(17,'now', 'Omprakash', 123.22, 'July','Approved' ,2),
(18, 'curdate','sakshi', 645.54 ,'May','Pending' ,3),
(19, 'not null','prerna', 356.54, 'June','Pending' ,4);that provides the following result after execution –
Suppose we have to calculate the total payment for each article for each month and retrieve only those records whose income for the month exceeds 2000. For this, the total amount of article will be rate into pages and then for retrieving the total amount for a particular article for a particular month, we will have to group our result set based on the author and month column and then for retrieving the result whose payment exceeds 2000 amount, we will apply the restriction on our calculated amount in the having clause. Our query statement will be as follows –
SELECT author, MONTH, SUM(rate*pages) AS payment
FROM educba_articles
GROUP BY author, MONTH
HAVING payment > 2000;that gives the following output –
Let’s consider another example where our goal is to retrieve the total payment that we need to pay to each author. In this case, the total payment for all months should exceed 5000. To achieve this, we need to group the data by author, calculate the payment by multiplying the rate and pages, and aggregate this value using the SUM() function to calculate the total payment for each author. Additionally, we should apply a restriction on the calculated value within the HAVING clause. Our query statement will look like follows –
SELECT author, SUM(rate*pages) AS payment
FROM educba_articles
GROUP BY author;
HAVING payment > 5000;that gives the following output after execution –
Conclusion
HAVING clause can be used to apply restrictions and filters on the grouped expressions in complex queries that are generally used for reporting purposes using the SELECT query statement in MySQL.
Recommended Articles
We hope that this EDUCBA information on “MySQL having” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
