Why is inner join needed in the 'SQL Subqueries nested in SELECT and FROM' example?
In the lecture video involving displaying employee_id, dept_code and manager_id we see a join on the emp_no between the employees and dept_emp table.
Why is this join needed - all required emp_no (10001-10040) and dept_no are present in the dept_emp table itself to satisfy the query.
The below code returns identical results without a join.
SELECT emp_no, MIN(dept_no) AS dept_code,
(SELECT emp_no
FROM employees
WHERE emp_no = '110022') AS manager_id
FROM dept_emp
WHERE emp_no BETWEEN 10001 AND 10020
GROUP BY emp_no
UNION
SELECT emp_no, MIN(dept_no) AS dept_code,
(SELECT emp_no
FROM employees
WHERE emp_no = '110039') AS manager_id
FROM dept_emp de
WHERE emp_no BETWEEN 10021 AND 10040
GROUP BY emp_no;
Hi Devrath!
Thanks for reaching out.
The join is necessary if you need additional details from the employees table, such as names, hire dates, or other employee-specific information. If your query only requires data present in the dept_emp table, such as emp_no and dept_no, then the join is not required. However, in scenarios where more comprehensive employee data is needed, the join becomes essential.
Hope this helps.
Best,
Tsvetelin