SQL subqueries with WHERE clause.
Hello Martin,
Thank you for a great course on SQL, it had never been simpler.
However I was having doubt in the following codes can you pls tell me why am I getting different outputs for the following:
SELECT
e.emp_no, e.first_name, e.last_name
FROM employees e
WHERE
EXISTS (SELECT
dm.emp_no
FROM dept_manager dm
WHERE e.emp_no = dm.emp_no
);
SELECT
e.emp_no, e.first_name, e.last_name
FROM employees e
WHERE
EXISTS (SELECT
dm.emp_no
FROM dept_manager dm
);
In 2nd code I get more records than first one why is that so ?
Like the logic behind it.
My thoughts:
I think the inner subquery in both the codes has to return the employee no's of the dept_managers, then the outer query has to return the employee information for these dept managers only.
However results are not inline with my thoughts.
Hoping for your help.
Thank you.
Kind Regards
Yuvraj.
Hi Yuvraj!
Thanks for reaching out and for the kind words!
Actually, the results are indeed in line with your thoughts.
By setting the WHERE condition:
WHERE e.emp_no = dm.emp_no
you indicate that you'd like the result to only reflect the employee numbers of the managers in the company.
The managers are 24, hence the number of obtained rows being 24.
Hope this helps.
Best,
Martin
Hi Martin,
Thanks for your reply,
Actually the second code gives an output of 300028 rows while the first one as you mentioned returns only 24 rows, can
you explain why is that so ?
Hi Yuvraj!
Indeed, because of using only employee numbers of the managers (indicated through the WHERE condition where we say that we want to match the employee numbers from the "department manager" table by adding dm.emp_no.
Since we only have 24 managers in the dept_manager table, you obtain 24 rows.
Whenever this WHERE condition is not set, you obtain the number of employees there are in the employees table.
Hope this helps.
Best,
Martin
Okay Thank you,
I got why the first code actually gives the output of 24 records only, but however still I am not sure about the second code's output.
Is it this way:
- The inner query returns true to exists but doesn't filter any record, thus the OUTER query selects every employees records, thus giving an output of 300028 records.
Is it correct let me know this, thank you.
Kind Regards
Yuvraj.
Hi Yuvraj!
Thanks for your reply. That's the reason indeed! Congratulations on understanding the mechanics behind the operation.
Kind regards,
Martin
Great then,
Thank you for being along the process.
Best,
Yuvraj.
Hi Yuvraj!
You are very welcome!
Best,
Martin