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

  1. select_statement FROM ( select_statement ) [ AS ] name

Example

  1. SELECT col
  2. FROM (
  3. SELECT a+b AS col
  4. FROM t1
  5. ) t2

Sub-Queries in the WHERE Clause

Description

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

Syntax

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

Examples

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