What is the SQL HAVING clause?

The SQL HAVING clause is used instead of the WHERE clause in aggregate functions . This is because the WHERE clause cannot be used in aggregate functions. The difference between the WHERE clause and the HAVING clause is that the former imposes defined conditions on the columns, whereas the latter imposes conditions on GROUPS that are created by the GROUP BY clause.

Note: SQL is not a case-sensitive language. This means that a letter written in lowercase or uppercase will have the same meaning for the SQL language.

Syntax

The following code portrays the placement of the HAVING clause in a query:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example 1

The following example shows the process of grouping being performed on the count of EmployeeID on the basis of the Country that each employee belongs to. The HAVING clause filters out and returns only those countries that have a COUNT(EmployeeID) that is greater than 2.

CREATE DATABASE test;
CREATE TABLE Employees (
    EmployeeID int,
    EmployeeName varchar(20),
    Salary int,
    Country varchar(20)
);
INSERT INTO Employees
VALUES (1, 'Ethan White',60000,'U.S.A');
INSERT INTO Employees
VALUES (2, 'Erin Smith',100000,'U.S.A');
INSERT INTO Employees
VALUES (3, 'Yousaf Khan',50000,'Germany');
INSERT INTO Employees
VALUES (4, 'Saad Qureshi',200000,'Germany');
INSERT INTO Employees
VALUES (5, 'John Wood',30000,'Germany');
INSERT INTO Employees
VALUES (6, 'Sung Lee',20000,'U.S.A');
INSERT INTO Employees
VALUES (7, 'Sumit Kumar',80000,'Germany');
SELECT COUNT(EmployeeID), Country
FROM Employees
GROUP BY Country
HAVING COUNT(EmployeeID) > 2;

Example 2

The following example again shows the process of grouping being being performed on the count of EmployeeID on the basis of the Country that each employee belongs to. This time the HAVING clause filters out and returns only those countries that have a COUNT(EmployeeID) that is less than 2.

CREATE DATABASE test;
CREATE TABLE Employees (
    EmployeeID int,
    EmployeeName varchar(20),
    Salary int,
    Country varchar(20)
);
INSERT INTO Employees
VALUES (1, 'Ethan White',60000,'U.S.A');
INSERT INTO Employees
VALUES (2, 'Erin Smith',100000,'U.S.A');
INSERT INTO Employees
VALUES (3, 'Yousaf Khan',50000,'Germany');
INSERT INTO Employees
VALUES (4, 'Saad Qureshi',200000,'Germany');
INSERT INTO Employees
VALUES (5, 'John Wood',30000,'Germany');
INSERT INTO Employees
VALUES (6, 'Sung Lee',20000,'U.S.A');
INSERT INTO Employees
VALUES (7, 'Sumit Kumar',80000,'Germany');
INSERT INTO Employees
VALUES (8, 'Pankaj Kumar',80000,'India');
SELECT COUNT(EmployeeID), Country
FROM Employees
GROUP BY Country
HAVING COUNT(EmployeeID) < 2;

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)