...

• Scalar subquery can only return at most one row. HIVE will check for this case at runtime and throw an error if not satisfied. e.g. following query is invalid

  Code Block
  language sql
  SELECT customer.customer_num, (SELECT ship_charge FROM orders WHERE customer.customer_num = orders.customer_num ) AS total_ship_chg FROM customer

• Scalar subquery can only have one column. HIVE will check for this case during compilation and throw an error. e.g following query is invalid

  Code Block
  language sql
  SELECT customer.customer_num, (SELECT ship_charge, customer_num FROM orders LIMIT 1 ) AS total_ship_chg FROM customer

• Correlated variables are only permitted in filter i.e. WHERE and HAVING clause. e.g. following query is invalid

  Code Block
  language sql
  SELECT customer.customer_num, (SELECT customer.customer_num FROM orders WHERE customer.customer_num = orders.customer_num ) AS total_ship_chg FROM customer

• Subqueries with DISTINCT is not allowed. Since DISTINCT <expression> will be evaluated as GROUP BY <expression> subqueries with DISTINCT is disallowed for now.

Design

Given the assumptions above following kind of subqueries could be used in select 

...