There are times when a query is way too complex. At that time using Impala WITH Clause, we can define aliases to complex parts and include them in the query. Although, there is much more to learn about using Impala WITH Clause.
So, in this article, we will discuss the whole concept of Impala WITH Clause. Apart from its introduction, it includes its syntax, type as well as its example, to understand it well.
Impala WITH Clause
Basically, to define aliases for complicated expressions that are referenced multiple times within the body of the SELECT, it is a clause that can be added before a SELECT statement.
Although, it is quite same as CREATE VIEW, with the only difference that the table and column names defined in the WITH clause do not persist after the query finishes. Also, it does not conflict with names used in actual tables or views. We also call it “subquery factoring”.
Moreover, by using subqueries, we can rewrite a query to work the same as with the WITH clause.
There are several purposes offered by WITH clause. They are:
- It provides ease of maintenance and convenience from less repetition with the body of the query. Also, we can use it along Various queries, where the similar complicated expressions are referenced multiple times. Such as UNION, joins, or aggregation functions.
- Also, abstracts the most complex part of the query into a separate block of SQL code to make it easier to read and understand.
- Moreover, it enhances compatibility with SQL from other database systems that support the same clause. Like primarily Oracle Database.
However, it is very important to note that this clause does not support recursive queries in the WITH. Even if those are supported in some other database systems.
a. Syntax
So, the syntax for Impala WITH Clause is-
with x as (select 1), y as (select 2) (select * from x union y);
b. Standards Compliance
Basically, it was Introduced in SQL:1999.
Examples of Impala WITH Clause
Let’s understand Impala WITH Clause with several Examples;
- Example1
— Define 2 subqueries that can be referenced from the body of a longer query.
with t1 as (select 1), t2 as (select 2) insert into tab select * from t1 union all select * from t2;
Technology is evolving rapidly!
Stay updated with DataFlair on WhatsApp!!
— Define one subquery at the outer level, and another at the inner level as part of the
— initial stage of the UNION ALL query.
with t1 as (select 1) (with t2 as (select 2) select * from t2) union all select * from t1;
- Example2
For Example,
Let us suppose we have a table named Students in the database my_db. Its contents are −
[quickstart.cloudera:21000] > select * from Students;
Query: select * from Students
| id | name | age | address | salary |
| 1 | shubham | 32 | delhi | 20000 |
| 9 | Pulkit | 23 | Gandhi nagar | 28000 |
| 2 | monika | 25 | mumbai | 15000 |
| 4 | revti | 25 | indore | 35000 |
| 7 | Vaishnavi | 25 | Goa | 23000 |
| 6 | mehul | 22 | hyderabad | 32000 |
| 8 | Rishabh | 22 | chennai | 31000 |
| 5 | shreyash | 23 | pune | 30000 |
| 3 | kajal | 27 | alirajpur | 40000 |
Fetched 9 row(s) in 0.59s
Similarly, assume we have another table named Users. Its contents are −
[quickstart.cloudera:21000] > select * from Users ;
Query: select * from Users
| id | name | age | address | salary |
| 3 | vishal | 54 | Banglore | 55000 |
| 2 | Shubham | 44 | Banglore | 50000 |
| 4 | Mansi | 64 | kolkata | 60000 |
| 1 | Ankur | 34 | kolkata | 40000 |
Fetched 4 row(s) in 0.59s
So, here is an example of the Impala WITH clause. Basically, using the UNION clause, we are displaying the records from both Users and Students whose age is greater than 25.
[quickstart.cloudera:21000] >
with t1 as (select * from Students where age>25),
t2 as (select * from Users where age>25)
(select * from t1 union select * from t2);
Hence, we get the following output, on executing the above query.
Query: with t1 as (select * from Students where age>25), t2 as (select * from Users where age>25)
(select * from t1 union select * from t2)
| id | name | age | address | salary |
| 3 | vishal | 54 | Banglore | 55000 |
| 1 | Ankur | 34 | kolkata | 40000 |
| 2 | Shubham | 44 | Banglore | 50000 |
| 5 | shreyash | 23 | pune | 30000 |
| 4 | Mansi | 64 | kolkata | 60000 |
| 1 | shubham | 32 | delhi | 20000 |
Fetched 6 row(s) in 1.73s
So, this was all about Impala with Clause. Hope you like our explanation.
Conclusion
Hence, in this article, we have seen how to use Impala WITH Clause properly. However, if you want to ask any doubt, feel free to ask in the comment section.we will definitely respond.