HAVING Clause in Oracle

HAVING Clause in Oracle with Examples

In this article, I am going to discuss the HAVING Clause in Oracle with Examples. Please read our previous article where we discussed the GROUP BY Clause in Oracle with Examples. At the end of this article, you will understand the following pointers.

  1. What is the Having Clause in Oracle?
  2. Why do we need the Having Clause in Oracle?
  3. Filtering Groups in Oracle
  4. Using both Having and Where Clause in Oracle
  5. Having Clause using SUM, MAX, MIN, AVG & COUNT Aggregate Functions
  6. Difference Between WHERE Clause and HAVING Clause in Oracle
What is the Having Clause in Oracle?

The Having Clause in Oracle is also used for filtering the data just like the where clause. Having Clause will filter the rows from the intermediate result set that is built by using the FROM, WHERE, or GROUP BY clauses in a SELECT statement.

The Having Clause in Oracle is typically used with a GROUP BY clause. That means the Having Clause is used in combination with a GROUP BY clause to restrict the number of groups to be returned by satisfying the condition which is specified using the having clause.

Why do we need the Having Clause in Oracle?

The WHERE clause could not be used with aggregate data rows, hence the HAVING clause was added to SQL. The purpose of the HAVING clause is to filter the group rows produced by the GROUP BY clause.

The HAVING clause is the same as the WHERE clause. The only difference is WHERE clause FILTERS the intermediate data results, while the HAVING clause operates on group rows. Likewise, WHERE clause, we can use conditions and operators with the HAVING clauses to build complex SQL statements.

Since the HAVING clause acts as a filter on group rows, the only possible column in group rows are columns specified in the GROUP BY clause.

Syntax to use Having Clause in Oracle:

Following is the syntax to use the HAVING Clause in Oracle.

What is the Having Clause in Oracle?

Parameters or Arguments of Having Clause in Oracle:
  1. aggregate_function: It can be any of the aggregate functions like SUM, COUNT, MIN, MAX, or AVG.
  2. expression1, expression2, expression_n: The expressions which are not encapsulated within an aggregate function must be included in the GROUP BY clause.
  3. Where Conditions: It is optional. If you want to retrieve selected records based on some conditions then you need to specify the conditions using the Where clause.
  4. HAVING having_condition: The Having Clause Condition is used to add a further filter condition that can be applied only to the aggregated results to restrict the number of groups to be returned. 
Example to understand HAVING Clause in Oracle:

We are going to use the following Employee table to understand the HAVING Clause in Oracle with Examples.

Why do we need the Having Clause in Oracle?

Please execute the below SQL query to drop the existing Employee table if any and create a new Employee table with the required data.

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Comm INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 3500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 4500, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 5000, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1008, 'Sambit', 'IT', 55000, 'Male', 5500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1009, 'Pranaya', 'IT', 57000, 'Male', 5700, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 7500, 'Mumbai');
Filtering Groups in Oracle:

We need to find the total salary given to all employees who are working in London. Here, we can achieve this using two ways i.e. using WHERE and HAVING clause. The Where clause is used to filter the rows before aggregation, whereas the Having clause is used to filter the groups that mean after aggregations.

Filtering rows using WHERE clause in Oracle
SELECT City, SUM(Salary) as TotalSalary
FROM Employee
WHERE City = 'London'
GROUP BY City;

When you execute the above query, you will get the following output.

Filtering rows using WHERE clause in Oracle

Filtering groups using the HAVING clause in Oracle
SELECT City, SUM(Salary) as TotalSalary
FROM Employee
GROUP BY City
HAVING City = 'London';

When you execute the above query, you will get the following output.

Filtering groups using the HAVING clause

Note: From the performance point of view, the HAVING Clause is slower than the WHERE clause, and having clause should be avoided if possible. The point that we should consider is, try to eliminate the records as soon as possible which you don’t want in your result set.

Using both Having and Where Clause in Oracle

It is also possible in Oracle to use both WHERE and HAVING clauses in a single SQL Select Statement. Let us understand this with an example. We want to retrieve the total salary of all the Male employees who are working in London. The following SQL Script exactly does the same. In the following query, first, we filter the data based on the WHERE clause i.e. filter the employees by Gender. Then GROUP BY clause is applied to the filter result set based on the city column. Finally, the Having clause applied to the groups and return only the London group.

SELECT City, SUM(Salary) as TotalSalary
FROM Employee
WHERE Gender = 'Male'
GROUP BY City
HAVING City = 'London';

When you execute the above query, you will get the following output.

Using both Having and Where Clause in Oracle

Having Clause using SUM Function in Oracle

Let us see an example of using the Having Clause in Oracle with the SUM aggregate function. Our requirement is to find all the departments whose total salary is greater than 170000. So, we need to group the employees by department and the Having Clause will filter the results so that only departments with a total salary greater than 170000 will be returned. The following SQL Query exactly does the same.

SELECT Department, SUM(Salary) AS Total_Salary
FROM Employee
GROUP BY Department
HAVING SUM(Salary) > 170000;

When you execute the above query, you will get the following output. As you can in the below image, the Finance department is not included in the output as the total Salary given to all employees in the Finance department is less than 170000.

blank

Having Clause using COUNT Function in Oracle:

Let us see an example of using the Having Clause in Oracle with the COUNT aggregate function. Our requirement is the find all the departments whose total number of employees working in that department is greater than 3.

The following query uses the GROUP BY Clause and COUNT Aggregate function to return the Department and the number of employees in that department. The Having Clause is then applied to the groups and filter the results so that only the department with more than 3 employees will be returned as a result.

SELECT Department, COUNT(*) AS "Number of Employees"
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 3;

When you execute the above query, you will get the following output. As you can in the below image, the Finance department is not included in the result set as there are only 2 employees working in the Finance department.

Having Clause using COUNT Function in Oracle

Having Clause using AVG Aggregate Function in Oracle:

Let us see an example of using Having Clause in Oracle with the AVG Aggregate function. Our requirement is to find all the departments where the average salary is more than 49000.

The following query uses the GROUP BY clause and AVG Aggregate function to return the name of the department and the Average salary of each department. The Having clause is then applied and filtered to the group and will return only those departments where the average salary is greater than 49000.

SELECT Department, AVG(Salary) AS "Average salary"
FROM Employee
GROUP BY Department
HAVING AVG(Salary) > 49000;

When you execute the above query, you will get the following output. As you can see the IT department is not included in the result set as the Average Salary of the IT department is less than 49000.

Having Clause using AVG Aggregate Function in Oracle

Having Clause Using MIN Function in Oracle:

Let us see an example of using Having Clause in Oracle with MIN Aggregate function. Our requirement is to find all the departments where the minimum salary is greater than 40000.

The following query uses the MIN aggregate function to return the name of each department and the minimum salary in that department. The Having Clause will return only those departments where the minimum salary is greater than 40000.

SELECT Department, MIN(Salary) AS "Minimum salary"
FROM Employee
GROUP BY Department
HAVING MIN(Salary) > 40000;

When you execute the above query, you will get the following output.

Having Clause Using MIN Function in Oracle

Having Clause Using MAX Function in Oracle:

Let us see an example of using Having Clause in Oracle with MAX Aggregate function. Our requirement is to find all the departments where the maximum salary is greater than 50000.

The following query uses the MAX aggregate function to return the name of each department and the maximum salary in that department. The Having Clause will return only those departments where the maximum salary is greater than 50000.

SELECT Department, MAX(Salary) AS "Maximum salary"
FROM Employee
GROUP BY Department
HAVING MAX(Salary) > 50000;

When you execute the above query, you will get the following output.

Having Clause Using MAX Function in Oracle

Difference Between WHERE Clause and HAVING Clause in Oracle
  1. WHERE clause in Oracle cannot be used with aggregate functions whereas HAVING clause can be used with aggregate functions. That means the WHERE clause in Oracle is used for filtering individual rows on a table whereas the HAVING clause in Oracle is used for filtering the groups which are created by the Group by Clause.
  2. The WHERE comes before the GROUP BY clause. That means the WHERE clause filters rows before aggregate calculations are performed. On the other hand, the HAVING clause comes after GROUP by Clause. That means the HAVING clause filters rows after aggregate calculations are performed. So, from a performance point of view, the HAVING Clause is slower than the WHERE Clause and should be avoided if possible.
  3. WHERE and HAVING clauses can be used together in a single SELECT statement. In that case, the WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then only the HAVING clause filters the groups in Oracle. 
  4. The Oracle WHERE clause can be used with SELECT, INSERT, and UPDATE statements whereas the HAVING clause can only be used with the SELECT statement.
  5. We can use the WHERE clause without using the GROUP BY Clause but we cannot use the HAVING Clause without using the GROUP BY Clause in Oracle.
ORDER OF EXECUTION:
  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

In the next article, I am going to discuss ROLLUP and CUBE in Oracle with Examples. Here, in this article, I try to explain the HAVING Clause in Oracle with Examples and I hope you enjoy this HAVING Clause in Oracle with Examples article.

Leave a Reply

Your email address will not be published.