Last answered:

23 May 2024

Posted on:

11 May 2024

0

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;

1 answers ( 0 marked as helpful)
Instructor
Posted on:

23 May 2024

0

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

Submit an answer