Wednesday, September 21, 2016

Hive WITH Clause - Subquery another way



The WITH clause may be processed as an inline view or resolved as a temporary table.
Considering ,or each employee we want to know how many other people are in their department. Using an inline view we might do the following.
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc
WHERE  e.deptno = dc.deptno;
Using a WITH clause this would look like the following.
WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno;
The difference seems rather insignificant here.