Which Clause is Similar to Having Clause in MySQL?

Introduction

A HAVING clause is similar to a WHERE clause. The Having clause applies only to groups or the rows in the result set that represent groups; in contrast, the WHERE clause is applicable to specific rows. The WHERE and HAVING clauses are both permitted in a query. In such instances:

• Individual table rows or table-valued items in the diagram pane are the initial places in which the WHERE clause is applied. Rows are only grouped if they satisfy the requirements in the WHERE clause.
• Next, the rows in the result set are subjected to the HAVING clause. The output of the query only contains the groups that satisfy the HAVING requirements. A HAVING clause can only be applied to columns that are part of an aggregate function or the GROUP BY clause.

MySQL HAVING Clause

In the SELECT statement, the HAVING clause is used to define filter conditions for a collection of rows or aggregates.

To filter groups according to certain criteria, the HAVING clause is frequently combined with the GROUP BY clause. The HAVING clause functions similarly to the WHERE clause if the GROUP BY clause is removed.

Syntax of MySQL HAVING Clause

The syntax of the HAVING clause is illustrated below:

SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Parameters:

• aggregate_function: Any aggregate function, including SUM, COUNT, MIN, MAX, or AVG, can be specified.
• expression1, expression2,... expression_n: It lists the expressions that need to be included in the GROUP BY clause because they are not contained in an aggregate function.
• WHERE conditions: You can choose not to. The criteria for choosing the records are provided by it.
• HAVING condition: This operator is implemented to restrict the units of again rows. It best presents the result set businesses for whom the necessities are TRUE.

Examples of MySQL HAVING Clause

HAVING Clause with the SUM function

Let us study a table named 'employees'; that carries the subsequent facts:

To gain the employee_name and the full in their running hours, we make use of the SUM function collectively with the HAVING clause.

Execute the subsequent query:

SELECT employee_name, SUM(Salary) AS "Total Salary"
FROM employees
GROUP BY employee_name
HAVING SUM(Salary) > 1000000;

Output

HAVING Clause with AVG function

To obtain the employee_name and the common operating hours of a specific worker, we utilize the AVG function together with the HAVING Clause.

Execute the following query:

SELECT employee_name, AVG(Salary) AS "Average Salary"
FROM employees
GROUP BY employee_name
HAVING AVG(Salary) >100000;

Output

MySQL WHERE Clause

To filter out the results, make use of the MySQL WHERE clause along with the SELECT, INSERT, UPDATE, and DELETE clauses. It details the appropriate area at which the operation needs to be performed.

Syntax for MySQL WHERE Clause

The syntax of the HAVING clause is given below:

WHERE conditions; 

Conditions: It outlines the requirements that must be met in order for records to be chosen.

Examples of MySQL WHERE clause

Let us look at an example of retrieving information from the "officers" table.

create table officers(officer_id int(10), officer_name varchar(50), address varchar(50));

Example:

SELECT *
FROM officers
WHERE address = 'HYD';

Output

MySQL WHERE Clause with AND condition

In this example, the AND condition is being used to retrieve data from the "officers" table.

Query

SELECT *
FROM officers
WHERE address = 'Mumbai'
AND officer_id < 4;

Output

WHERE Clause with OR condition

Query

SELECT *
FROM officers
WHERE address = 'Mumbai'
OR address = 'HYD';

Output

MySQL WHERE Clause with the combination of AND & OR conditions

The WHERE clause can also be used in conjunction with the AND and OR conditions.

See this example:

Execute the following query:

SELECT *
FROM officers
WHERE (address = 'HYD' AND officer_name = 'Abhimanyu')
OR (officer_id < 5);

Output:

Using the WHERE clause with the LIKE operator

If a value matches a given pattern, the LIKE operator evaluates it to TRUE.

You use the wildcards % and _ to create patterns. The % wildcard can fit a string of 0 or extra characters, and the _ wildcard can fit any single character.

The officer whose last name ends with the word "nyu" can be found by using the query below:

SELECT officer_name
FROM officers
WHERE officer_name LIKE '%nyu'
ORDER BY officer_name;

Output:

Using the HAVING and WHERE Clauses Together

To obtain the employee_name and the average working hours of a particular employee, we utilize the AVG function together with the HAVING Clause.

USE jtp;
SELECT category_id, AVG(list_price)
FROM products
WHERE model_year = 2018
GROUP BY category_id
HAVING AVG(list_price) > 1500

WHERE model_year = 2018

The WHERE clause comes into effect before the GROUP BY

You can refer to columns that are not defined in the SELECT

It is not allowed to use aggregate functions in the WHERE

HAVING AVG(list_price) > 1500

The SELECT clause comes first, while the HAVING clause runs after the GROUP BY clause.

You have to use the same aggregate function in the SELECT if you use it in the HAVING clause.

You have to include the same column or expression in the SELECT if you make reference to it in the HAVING clause.

Aggregate functions are not allowed in a WHERE

Advantages of the HAVING Clause over the WHERE Clause

As for the two clauses, the primary difference is that, although it is prohibited in the WHERE block, HAVING may be used for subsets of aggregated groups. Put differently, after having, we are able to have an aggregate function in a condition; however, using aggregate functions inside of conditions is not allowed in WHERE.

When to use WHERE and when to use HAVING?

• Deciding whether to use HAVING or WHERE in a certain situation is crucial.
• Setting conditions that relate to subsets of certain rows is made possible via WHERE. Prior to rearranging the output into groups, these conditions are implemented.
• The rows move forward in the data retrieval process and can be grouped by unique values entered in a particular field or fields after the rows that meet the WHERE conditions are selected.
• At this point, the output cannot be further enhanced or filtered in accordance with the HAVING clause's requirements.
• Lastly, use the ORDER BY clause to arrange the records of the final list.

Solving a Task

• Let's look at an example that has both a WHERE and a HAVING condition to help you better comprehend the data retrieval procedure.
• The objective is to obtain a list of every name that appears fewer than 200 times. Limit the data to individuals hired on or after January 1st, 1999.
• Let's formulate the query, step by step.
• It appears that we need to choose the first names and the frequency with which a first name appears, renaming the second choice as "names_count."
• Designating "employees" as the table from which we will receive data is the second step.
• The following is how the code will appear:

SELECT
employee_name, COUNT(employee_name) AS names_count
FROM
employees;

What to use WHERE or HAVING?

To solve this issue, we need to meet two requirements.

First Condition

One is that there must be fewer than 200 instances of each name. The phrase "200 times" indicates right away that we need to utilize COUNT(), which counts the instances of a given first name in the "employees" data table.

Since COUNT() is an aggregate function, it must be used in conjunction with HAVING, as we previously stated.

Second Condition

A general requirement needs to be met as well: "Every row that is extracted must contain individuals who were hired after January 1st, 1999." This condition covers every single row in the "employees" table. There is no need to use a particular aggregate function. As a result, this requirement needs to match the WHERE clause.

We should not overlook inserting the GROUP BY segment in between the WHERE and HAVING blocks. Our objective needs us to aggregate our output by the frequency of a given first name being encountered; hence, we must group by "first_name" and not by any other variable.

Let us really arrange the results in descending order according to the "first_name."

The Final Solution

The entire query can be expressed as follows:/pr

SELECT

employee_name, COUNT(employee_name) AS names_count

FROM

employees

WHERE

hire_date > '2000-01-01'

GROUP BY employee_name

HAVING COUNT(employee_name) < 200

ORDER BY employee_name DESC;

Output

Our issue turned into resolved when the question was executed; as a result, we have a list containing the total quantity of specific names of employees employed after January 1st, 2000.

Conclusion

The WHERE clause is similar to a HAVING clause; the handiest difference is that the "Where" clause is used to filter statistics from a desk consistent with a given circumstance, whereas the "Having" clause is used to filter information from corporations in keeping with a given criteria.