Impala HAVING Clause with Syntax & Restrictions

Keeping you updated with latest technology trends, Join DataFlair on Telegram

1. Objective – HAVING Clause

In our previous, tutorial we learn Impala LIMIT Clause. Here, we are going to learn the Impala Having Clause. This is used to specify conditions that filter which group results appear in the final results in Impala. Moreover, we will see the introduction, syntax, type, and example, to understand it well.
So, let’s discuss Impala Having Clause.

Impala HAVING Clause with Syntax & Restrictions

Impala HAVING Clause with Syntax & Restrictions

2. Impala HAVING Clause

Basically, Impala HAVING Clause examines the results of aggregation functions rather than testing each individual table row then performs a filter operation on a SELECT query. Hence, we can say we use it in conjunction with functions always. Such as COUNT(), SUM(), AVG(), MIN(), or MAX(). Also, with the GROUP BY clause,  typically.
Let’s see Impala GROUP BY Clause
In other words, it enables you to specify conditions that filter which group results appear in the final results in Impala.

However, we use this clause along with the Group By Clause. So, that places conditions on groups created by the GROUP BY Clause.

a. The syntax of HAVING Clause

So, the syntax for using Impala HAVING Clause is-

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]

b. Restrictions in Impala Having Clause 

There is one restriction while using  Impala HAVING Clause is that here filter expression cannot include a scalar subquery.

3. Example of Having Clause in Impala

For Example,
Let us suppose we have a table named Employees in the database my_db. Its contents are −

[quickstart.cloudera:21000] > select * from Employees;

Query: select * from Employees

id name age address salary
1 shubham 32 delhi 20000
2 monika 25 mumbai 15000
3 kajal 27 alirajpur 40000
4 revti 25 indore 35000
5 shreyash 23 pune 30000
6 mehul 22 hyderabad 32000
7 Vaishnavi 25 Goa 23000
8 Rishabh 22 chennai 31000
9 Pulkit 23 Gandhi nagar 28000

Fetched 9 row(s) in 0.51s

Read about Impala OFFSET Clause with Example
So, now let’s see an example of using Having clause in Impala −

[quickstart.cloudera:21000] > select max(salary) from Employees group by age having max(salary) > 20000;

This query displays those salaries, which are greater than 20000, but initially, it groups the table by age and selects the maximum salaries of each group.
20000

max(salary)
30000
35000
40000
32000

Fetched 4 row(s) in 1.30s
So, this was all about Impala Having Clause. Hope you like our explanation.

4. Conclusion

Hence, we have seen the whole concept of Impala HAVING Clause. Moreover, we discussed syntax and example of Having Clause in Impala. Still, if any doubt occurs, feel free to ask in the comment section.
Related Topic- Impala WITH Clause 
For reference 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.