Sub-Queries

Sub-Queries in the FROM Clause

Description

Hive dialect supports sub-queries in the FROM clause. The sub-query has to be given a name because every table in a FROM clause must have a name. Columns in the sub-query select list must have unique names. The columns in the sub-query select list are available in the outer query just like columns of a table. The sub-query can also be a query expression with UNION. Hive dialect supports arbitrary levels of sub-queries.

Syntax

select_statement FROM ( select_statement ) [ AS ] name

Example

SELECT col
FROM (
  SELECT a+b AS col
  FROM t1
) t2

Sub-Queries in the WHERE Clause

Description

Hive dialect also supports some types of sub-queries in the WHERE clause.

Syntax

select_statement FROM table WHERE { colName { IN | NOT IN } 
                                  | NOT EXISTS | EXISTS } ( subquery_select_statement )

Examples

SELECT * FROM t1 WHERE t1.x IN (SELECT y FROM t2);
SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x = t2.x);