Having Clause

Introduction

One fine day a company hires you as a developer. Your work is to write database queries. Now on the same day, your manager comes to you with a task to get all the department’s names having employees less than 50. 

You try your best to solve the problem and write a SQL query something like 

SELECT Dept
FROM Table_Name
GROUP BY Dept
WHERE COUNT(*)<50;

But now you find out that the WHERE clause is not working with the GROUP BY clause. Because WHERE clause works on the whole table.

 

To solve this problem, let’s learn a new clause named HAVING and how to use it in our query.

The HAVING Clause 

The HAVING clause places the condition in the groups defined by the GROUP BY clause using the SELECT statement. It can only be used with the SELECT statements.

HAVING clause is preferred to use with the GROUP BY clause. Else it will work like a WHERE clause.

Syntax

The following code block will show how to use the HAVING clause in a query.

SELECT Column_Name
FROM Table_Name
GROUP BY Column_Name
HAVING [conditions];

It is compulsory to use the GROUP BY clause before using the HAVING clause. 

 

Solution for the question which our manager gives us to solve.

SELECT Dept
FROM EMPLOYEES
GROUP BY Dept
HAVING COUNT(*)<50;

Some more Examples

Let consider the EMPLOYEES table having all the employee data consisting of columns like EmployeeName, EmployeeAge, EmployeeSalary, Dept, and EmployeeID as the primary key( it is a set of values of the table, which uniquely defines each record in this table). 

 

Now from the given Database Table, 

 

We need to find all dept names where the numbers of employees are less than 3 and need to print the Dept column as a result.
SQL Query:

SELECT Dept 
FROM EMPLOYEES 
GROUP BY Dept 
HAVING COUNT(*)<3;

Result for the above query:

 

Let us now discuss another scenario: 

We need to find the age group where the number of employees is less than 2 and need to print the EmployeeAge column as a result.

SQL Query:

SELECT EmployeeAge 
FROM EMPLOYEES 
GROUP BY EmployeeAge 
HAVING COUNT(*)<2;

Result for the above query:

 

Another variation of the above query is as follows:

We need to find the age group where the number of employees is less than 2 and sort the output result in descending order and need to print the EmployeeAge column as a result.

SQL Query:

SELECT EmployeeAge 
FROM EMPLOYEES 
GROUP BY EmployeeAge 
HAVING COUNT(*)<2
ORDER BY COUNT(*) DESC;

Result for the above query:

 

Let’s consider one more example,

We need to find those Dept name whose employee salary is minimum of that dept and also greater than 10000 and need to print the EmployeeSalary, Dept column as a result.

SQL Query

SELECT MIN(EmployeeSalary), Dept
FROM EMPLOYEES
GROUP BY Dept
HAVING MIN(EmployeeSalary)>10000;

Result for the above query:

Aggregate functions with which HAVING clause can be used.

An aggregate function performs a calculation of one or more values and returns a single value. The aggregate function is often used in combination with the SELECT statement's GROUP BY clause and HAVING clause.

 

Let us now have a look at some functions:

1. AVG – to get the average of a set of data values. 

We need to find the average salaries of each department and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT AVG(EmployeeSalary), Dept
FROM EMPLOYEES
GROUP BY Dept
HAVING AVG(EmployeeSalary);

Result for the above query:

 

2. COUNT – To count rows in a specified table. 

We need to find the count rows in each department and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT COUNT(EmployeeSalary), Dept
FROM EMPLOYEES
GROUP BY Dept
HAVING COUNT(EmployeeSalary);

Result for the above query:

 

3. MIN – To get the minimum value in a set of data values. 

We need to find those Dept names whose employee salary is minimum of that dept and also greater than 10000 and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT MIN(EmployeeSalary), Dept
FROM EMPLOYEES
GROUP BY Dept
HAVING MIN(EmployeeSalary)>10000;

Result for the above query:

 

4. MAX – To get the maximum value in a set of data values. 

We need to find those Dept names whose employee salary is maximum of that dept and also greater than 10000 and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT MAX(EmployeeSalary), Dept
FROM EMPLOYEES
GROUP BY Dept
HAVING MAX(EmployeeSalary)>10000;

Result for the above query:

 

5. SUM – To calculate the sum of data values. 

We need to find the sum of salaries of each department and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT SUM(EmployeeSalary), Dept
FROM EMPLOYEES
GROUP BY Dept
HAVING SUM(EmployeeSalary);

Result for the above query:

 

HAVING v/s WHERE clause

Frequently Asked Questions

  1. What is the core difference between WHERE clause and HAVING Clause?
    WHERE clause can only apply to a single row and cannot be used with aggregate functions. HAVING Clause can apply to summarized rows or groups, and aggregate functions can have them.
     
  2. Is it possible to use the HAVING clause without the GROUP BY clause?
    It can be used without group by clause in an aggregate function. But in that case, it behaves like a where clause.  
     
  3. In which types of queries HAVING clause can be used?
    This clause can be used where data is in groups or summarized rows.
     
  4. Which SQL statement is faster? (HAVING or WHERE)
    As per the SQL standard, WHERE restricts the result before returning rows. HAVING determines the outcome after bringing all the rows. So it can be concluded that WHERE is faster.

Key Takeaways 

In this article, we discussed how to use HAVING Clause using various examples and SQL queries. We also saw the difference between WHERE clause and HAVING clause. Also, learnt when to use these clauses based on queries asked. 

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Furthermore, to get hang on and practice different types of questions on SQL queries, you can visit Database Management Systems FREE Guided path. If you want to learn DBMS from an industry expert, check out a fantastic course on DBMS

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think