THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
Table of Contents |
---|
...
Subqueries in the FROM clause
Code Block |
---|
SELECT ... FROM (subquery) name ... |
...
Code Block |
---|
SELECT t3.col FROM ( SELECT a+b AS col FROM t1 UNION ALL SELECT c+d AS col FROM t2 ) t3 |
Subqueries in the WHERE clause
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);
{code:sql}
The other supported type are EXISTS and NOT EXISTS subqueries:
{code:sql}
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)
|
There are a few limitations:
- These subqueries are only supported on the right hand side of an expression
- IN/NOT IN subqueries may only select a single column
- EXISTS/NOT EXISTS must have one or more correlated predicates
- References to the parent query are only supported in the WHERE clause of the subqueries