Understanding order of execution of SQL

#1

Let us review the order of execution of SQL. First let us review the order of writing the query.

  1. SELECT
  2. FROM
  3. JOIN or OUTER JOIN with ON
  4. WHERE
  5. GROUP BY and optionally HAVING
  6. ORDER BY

Let us come up with a query which will compute daily revenue using COMPLETE or CLOSED orders and also ordered by order_date.

SELECT o.order_date,
  round(sum(oi.order_item_order_id), 2) AS revenue
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY o.order_date
ORDER BY o.order_date
LIMIT 10;

However order of execution is different.

  1. FROM
  2. JOIN or OUTER JOIN with ON
  3. WHERE
  4. GROUP BY and optionally HAVING
  5. SELECT
  6. ORDER BY

As SELECT is executed before ORDER BY Clause, we will not be able to refer the aliases in SELECT in other clauses except for ORDER BY.


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