Overview of Nested Sub Queries

#1

Let us recap about Nested Sub Queries.

  • We typically have Nested Sub Queries in FROM Clause.
  • We need to provide alias to the Nested Sub Queries in FROM Clause in Hive.
  • We use nested queries quite often over queries using Analytics/Windowing Functions
SELECT * FROM (SELECT current_date) q;

Let us see few more examples with respected to Nested Sub Queries.

SELECT * FROM (
  SELECT order_date, count(1) AS order_count
  FROM orders
  GROUP BY order_date
) q
LIMIT 10;

SELECT * FROM (
  SELECT order_date, count(1) AS order_count
  FROM orders
  GROUP BY order_date
) q
WHERE q.order_count > 0;
// We can achieve using HAVING clause (no need to be nested to filter)

Practice hive on state of the art Big Data cluster - https://labs.itversity.com
You can sign up for our courses on Udemy using $10 coupons - Udemy Coupons - Big Data Courses


0 Likes