HAVING clause is used to apply a filter on the result of GROUP BY based on the specified condition. The conditions are Boolean type i.e. use of logical operators(AND, OR). This clause was included in SQL as the WHERE keyword failed when we use it with aggregate expressions. It is a very generally used clause in SQL. Similar to WHERE it helps to apply conditions, but HAVING works with groups. If you wish to filter a group, the HAVING clause comes into action.
Few important points:
- It is used to filter data according to the conditions provided.
- It is generally used in reports of large data.
- It is only used with the SELECT clause.
- The expression in the syntax can only have constants.
- In the query, ORDER BY is to be placed after the HAVING clause, if any.
- It implements in column operation.
- It is generally used after GROUP BY.
- The GROUP BY clause is used to arrange required data into groups.
HAVING Clause Syntax
SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;
Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause near the end of the SQL statement.
aggregate_function
This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.
aggregate_expression
This is the column or expression that the aggregate_function will be used on.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. These are the conditions for the records to be selected.
HAVING condition
This is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.
Difference between HAVING Clause and WHERE Clause
The difference between the WHERE and HAVING clauses in the database is the most important question asked during an IT interview. The following table shows the comparisons between these two clauses, but the main difference is that the WHERE clause uses conditions for filtering records before any groupings are made while HAVING clause uses conditions for filtering values from a group.
| HAVING | WHERE |
| This clause is used in database systems to fetch the data/values from the groups according to the given condition. | This clause is used in database systems to fetch the data/values from the tables according to the given condition. |
| It is always executed with the GROUP BY clause. | It can be executed without the GROUP BY clause. |
| It can include SQL aggregate functions in a query or statement. | We cannot use the SQL aggregate function with WHERE clause in statements. |
| We can only use SELECT statement with HAVING clause for filtering the records. | Whereas, we can easily use WHERE clause with UPDATE, DELETE, and SELECT statements. |
| It is used in SQL queries after the GROUP BY clause. | It is always used before the GROUP BY clause in SQL queries. |
| We can implements this SQL clause in column operations. | We can implements this SQL clause in row operations. |
| It is a post-filter. | It is a pre-filter. |
| It is used to filter groups. | It is used to filter the single record of the table. |
Examples using HAVING clause
ORDER_DETAILS | ID | EMPLOYEE | DEPT | CITY | SALARY | SALES | +——+----------+-----+-----------+----------+----- | 1 | Satvi | HR | Atlanta | 9000.00 | 90000 | 2 | Kannav | MKT | BBSR | 5000.00 | 50000 | 3 | Kiran | MKT | BBSR | 8000.00 | 50000 | 4 | Lilly | IT | WH | 6000.00 | 40000 | 5 | Alex | IT | SFO | 8500.00 | 80000 | 6 | Stacy | PR | Malibu | 4500.00 | 20000 | 7 | Barbie | PR | Malibu | 1000.00 | 25000
Example – Using SUM function
You could also use the SQL SUM function to return the name of the department and the total sales (in the associated department). This clause will filter the results so that only departments with sales greater than $50000 will be returned.
SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 50000; Result DEPT Total sales HR 90000 MKT 100000 IT 120000
Example – Using COUNT function
You could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that makeovers or equals $25,000 / year. This clause will filter the results so that only departments with more than 1 employee will be returned.
SELECT department, COUNT(*) AS "Number of employees" FROM employees WHERE salary >= 25000 GROUP BY department HAVING COUNT(*) > 1; Result DEPT Number of employees MKT 2 IT 2 PR 1
Example – Using MIN function
You could also use the SQL MIN function to return the name of each department and the minimum salary in the department. This clause will return only those departments where the minimum salary is greater than $35,000.
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) > 35000; Result DEPT Lowest salary HR 90000 MKT 50000 IT 40000
Example – Using MAX function
For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department. This clause will return only those departments whose maximum salary is less than $60,000.
SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) < 60000; Result DEPT Highest salary MKT 50000 PR 25000
Example of HAVING Clause with JOIN
“Orders” table:
| OrderID | CustomerID | EmployeeID | OrderDate |
| 101 | 10 | 5 | 1996-07-04 |
| 102 | 11 | 1 | 1996-07-05 |
| 103 | 14 | 4 | 1996-07-08 |
| 104 | 24 | 3 | 1996-07-08 |
| 105 | 16 | 4 | 1996-07-09 |
| 106 | 18 | 3 | 1996-07-10 |
| 107 | 24 | 5 | 1996-07-11 |
| 108 | 18 | 1 | 1996-07-12 |
| 109 | 28 | 3 | 1996-07-15 |
| 110 | 25 | 4 | 1996-07-16 |
| 111 | 20 | 2 | 1996-07-17 |
| 112 | 13 | 4 | 1996-07-18 |
| 113 | 15 | 4 | 1996-07-19 |
“Employees” table:
| EmployeeID | LastName | FirstName | BirthDate |
| 1 | Aube | Andrew | 1968-12-08 |
| 2 | Roberts | Wesley | 1952-02-19 |
| 3 | Kong | Ming | 1963-08-30 |
| 4 | Kutz | Margaret | 1958-09-19 |
| 5 | Parsley | Steven | 1955-03-04 |
Employees that have registered more than or equals to 3 orders
SELECT Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) >= 3;
| LastName | NumberOfOrders |
| Kutz | 5 |
| Kong | 3 |
Employees with last name’Aube’or ‘Kong’ that have registered more than 2 orders
SELECT Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Aube' OR LastName = 'Kong'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 2;
| LastName | NumberOfOrders |
| Kong | 3 |