Introduction to MySQL having
In real-time applications, we often feel a necessity to generate complex reports from the data that is 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 even if a large amount of data is present in the tables the performance of the application is not hampered. The SELECT query that is constructed for such reports needs to make complete use of the functionalities provided in MySQL.
Having a clause is one such functionality that helps in applying the filters on the expressions. These expressions can be single or multiple columns or even the condition on the grouped aggregated data that is 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 the 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 – It is 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 – It is the name of the table that 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 that you can specify on the columns of the table that needs to be fulfilled while retrieving the data.
- expressions_for_grouping – The resultset retrieved from the query structure above GROUP BY clause can be summarized and grouped based on certain expressions that can include columns as well as 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 filter, 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 that are retrieved instead of a single row of the table. When we do not use the GROUP BY clause in the SELECT query, then the HAVING clause behaves in the same manner as that of WHERE clause and applies restriction on individual row-level of the table as there is an absence of grouping expression. If the condition of filter specified in the HAVING clause returns a true value then the grouped record that is grouped according to the GROUP BY clause is included in the final resultset else is excluded.
Evaluation order:
The evaluation order followed by standard SQL is different than that of MySQL. Evaluation order determines that which and when clause will be considered for execution when the SELECT query will contain multiple clauses in it. The order in which the clauses of the SELECT query are evaluated in MySQL is as shown in the below diagram.
Here, we can see that the HAVING clause is evaluated after FROM, WHERE SELECT, and GROUP BY clause and before the LIMIT and ORDER BY clauses. However, the SQL standard follows different orders for evaluation which has the HAVING clause executed after the GROUP BY clause and before the SELECT clause.
Example
Let us consider one 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 gives the following output after execution –
Now, suppose we have to calculate the total payment for each of the articles for each of the months and retrieve only those records whose payment 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 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 –
4.5 (5,653 ratings)
View Course
SELECT author, MONTH, SUM(rate*pages) AS payment
FROM educba_articles
GROUP BY author, MONTH
HAVING payment > 2000;
that gives the following output –
Consider one other example, in which we want to retrieve the total payment to be paid to every author with a total payment of all the months exceeding 5000. For this groupism should be made on the author and the payment should be calculated by multiplying the rate and pages and aggregating this value with the SUM() function to calculate the total payment of that author. Further, a restriction on the grouped calculated value should be applied in 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
This is a guide to MySQL having. Here we discuss the Introduction, syntax, Evaluation order, and examples along with code implementation respectively. You may also have a look at the following articles to learn more –
