5.1 HAVING statement
There is another useful operator in SQL that is used with GROUP BY
, it's called HAVING
.
In its meaning, it is completely analogous to the operator WHERE
. It only WHERE
allows you to set a row filter before grouping, and with the help HAVING
you can set a filter that is applied to records after grouping.
The general view of the query when using grouping and filtering grouping results is as follows:
SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING condition
HAVING
can only be used if the request contains GROUP BY
.
Let's write a query where we display the number of hired employees by year.
SELECT
YEAR(join_date) AS hire_year,
COUNT(*) AS total
FROM employee
GROUP BY hire_year
And the result of this query:
hire_year | total |
---|---|
2012 | 1 |
2013 | 1 |
2014 | 1 |
2015 | 2 |
2018 | 1 |
And now we exclude from it the years when one or fewer employees were hired. Example:
SELECT
YEAR(join_date) AS hire_year,
COUNT(*) AS total
FROM employee
GROUP BY hire_year
HAVING total > 1
And the result of this query:
hire_year | total |
---|---|
2015 | 2 |
5.3 Order of execution of statements
To write correct and efficient SQL queries, you need to understand how they are executed by the SQL server.
The procedure for performing actions is strictly regulated and does not depend on your desire. You cannot rearrange the operators and get a different order.
The SQL query is executed in several stages in this order.
- Stage 1 - fetching rows
- First, all rows from the specified table are selected.
- Then calculated fields are added to them.
- And then of all the rows, only those that satisfy the condition remain
WHERE
- Stage 2 - grouping
- Grouping is then applied to the results.
- During grouping, fields such as
COUNT(*)
. - Finally, a filter is applied to the grouping result
HAVING
. - Stage 3 - sorting
- The rows obtained in the previous steps are sorted using
ORDER BY
.
Finally, the result can be trimmed with LIMIT
and OFFSET
.