Versions Compared

Old Version 3

changes.mady.by.user Vineet Garg

Saved on

compared with

New Version 4

changes.mady.by.user Vineet Garg

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • 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
    languagesql
    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
    languagesql
    SELECT customer.customer_num,
	(SELECT ship_charge, customer_num
		FROM orders LIMIT 1
	) AS total_ship_chg
FROM customer

Design

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

  • Scalar subqueries e.g. 

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

  • IN subqueries

    Code Block
    languagesql
    SELECT p_size IN (
		SELECT MAX(p_size) FROM part)
FROM part

  • EXISTS subqueries

    Code Block
    languagesql
    SELECT EXISTS(SELECT p_size FROM part)
FROM part

All of the above queries could be correlated or uncorrelated.