HAVING SQL: description, syntax, examples

SQL is the standard language for working with relational databases. He has in his arsenal of many powerful tools for manipulating data stored in the form of tables.

Undoubtedly, the ability to group data when it is sampled by a certain feature is one such tool. The SQL HAVING statement, along with the WHERE clause, allows you to define the sampling conditions for data already grouped in some way.

HAVING SQL parameter: description

First of all, it is worth noting that this parameter is optional and is used exclusively in conjunction with the GROUP BY parameter. As you remember, GROUP BY is used when SELECT uses aggregate functions, and the results of their calculations must be obtained for certain groups. If WHERE allows you to set the conditions for a selection before the data is grouped, then HAVING contains the conditions relating to the data already in the groups themselves. For a better understanding, let's take a look at the example with the diagram shown in the figure below.

This is a great example, giving a HAVING SQL description. A table is given with a list of product names, companies that produce them, and some other fields. In the query in the upper right corner, we are trying to get information about how many product names each company produces, while we want to output only those companies that produce more than 2 titles. The GROUP BY parameter formed three groups corresponding to the names of companies, for each of which the number of products (rows) was calculated. But the parameter HAVING by its condition cut off one group from the resulting sample, because it did not satisfy the condition. As a result, we get two groups corresponding to companies with the number of products 5 and 3.

A question may arise about why to use HAVING if there is a WHERE in SQL. If we used WHERE, it would look at the total number of rows in the table, not in groups, and the condition would not make sense in this case. However, quite often they perfectly coexist in one request.

In the example above, we can see how the data are first selected by the employee names specified in the WHERE parameter, and then the result grouped in GROUP BY passes an additional check on the amount of salary for each employee.

SQL HAVING parameter: examples, syntax

Let's consider some peculiarities of HAVING SQL syntax. The description of this parameter is quite simple. First, as already noted, it is used exclusively in conjunction with the GROUP BY parameter and is specified immediately after it and before the ORDER BY, if there is one in the query. It is understandable, since HAVING defines the conditions for the already grouped data. Secondly, in the condition of this parameter, you can only use the aggregate functions and fields specified in the GROUP BY parameter. All conditions in this parameter are specified exactly in the same way as in the case of WHERE.

Conclusion

As you can see, there is nothing complicated in this operator. Semantically it is used in the same way as WHERE. It is important to understand that WHERE is used relative to all selectable data, and HAVING is only relative to the groups defined in the GROUP BY parameter. We presented an exhaustive description for HAVING SQL, which is enough for confident work with it.