Subqueries in the FROM Clause
Code Block |
---|
|
SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...
(Note: Only valid starting with Hive 0.13.0) |
Hive supports subqueries only in the FROM clause (through Hive 0.12). The subquery has to be given a name because every table in a FROM clause must have a name. Columns in the subquery select list must have unique names. The columns in the subquery select list are available in the outer query just like columns of a table. The subquery can also be a query expression with UNION. Hive supports arbitrary levels of subqueries.
The optional keyword "AS" can be included before the subquery name in Hive 0.13.0 and later versions (HIVE-6519).
Example with simple subquery:
Code Block |
---|
|
SELECT col
FROM (
SELECT a+b AS col
FROM t1
) t2
|
Example with subquery containing a UNION ALL:
Code Block |
---|
|
SELECT t3.col
FROM (
SELECT a+b AS col
FROM t1
UNION ALL
SELECT c+d AS col
FROM t2
) t3
|
...
As of Hive 0.13 some types of subqueries are supported in the WHERE clause. Those are queries where the result of the query can be treated as a constant for IN and NOT IN statements (called uncorrelated subqueries because the subquery does not reference columns from the parent query):
Code Block |
---|
|
SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);
|
The other supported types are EXISTS and NOT EXISTS subqueries:
Code Block |
---|
|
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)
|
...