Subqueries

Subqueries in WHERE Clauses

A subquery is a SQL expression that returns a set of rows. The subquery is evaluated and its query result set used to evaluate the parent query, the outer query that contains the subquery. Hive supports subqueries to include WHERE clauses, as shown in the following example:

SELECT state, net_payments FROM transfer_payments WHERE transfer_payments.year 
  IN (SELECT year FROM us_census);

There are some restrictions for the use of subqueries in WHERE clauses.

• Subqueries in WHERE SQL clauses have the following limitations:

• Subqueries must appear on the right hand side of an expression.

• Nested subqueries are not supported.

• Only one subquery expression is allowed for a single query.

• Subquery predicates must appear as top level conjuncts.

• Subqueries support four logical operators in query predicates: IN, NOT IN, EXISTS, and NOT EXISTS.

• The IN and NOT IN logical operators may select only one column in a WHERE clause subquery.

• The EXISTS and NOT EXISTS operators must have at least one correlated predicate.

• The left side of a subquery must qualify all references to table columns.

• References to columns in the parent query are allowed only in the WHERE clause of the subquery.

• Subquery predicates that reference a column in a parent query must use the Equals To ( =) predicate operator.

• Subquery predicates may not refer only to columns in the parent query.

• Correlated subqueries with an implied GROUP BY statement may return only one row.

• All unqualified references to columns in a subquery must resolve to tables in the subquery.

• Correlated subqueries cannot contain windowing clauses.