SQL Having Clause

HAVING Clause is used to filter the record based on group rows. Having Clause always used with Group By clause.

Syntax:-


SELECT column(s)
From table_name
WHERE [condition]
GROUP BY column(s)
HAVING [condition]

Example:-

Example:-Suppose you have an employees table that have 7 records.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | clerk     | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
+----+------------+-----------+----------------+-----------+

Now you want to get the number of employees more than 2 belongs to each country.


SELECT COUNT(id), country 
FROM employees 
GROUP BY country HAVING COUNT(id)>2

Output:-


+-----------+---------+
| COUNT(id) | country |
+-----------+---------+
|         3 | India   |
+-----------+---------+
1 row in set (0.00 sec)

SQL Having Clause – Interview Questions

Q 1: What is HAVING used for?

Ans: HAVING filters grouped records after GROUP BY.

Q 2: Difference between WHERE and HAVING?

Ans: WHERE filters rows; HAVING filters groups.

Q 3: Can HAVING be used without GROUP BY?

Ans: Yes, but it behaves like WHERE with aggregates.

Q 4: Can aggregate functions be used in HAVING?

Ans: Yes, that is its main purpose.

Q 5: Is HAVING processed before WHERE?

Ans: No, WHERE is processed before HAVING.