Subquery condition e.emp_no = t.emp_no
Dear,
I accidently missed the connecting link (marked in bold) but the query gave me the same result with the provided solution. I understand that the subquery condition must include the connecting link which is the emp_no, but how did it work without that condition, when there is no 'title' in employees table? Did the MySQL mechanism just assumed it to be logical since there is only one matching column?
---------
SELECT
*
FROM
employees e
WHERE
EXISTS( SELECT
*
FROM
titles t
WHERE
t.emp_no = e.emp_no
AND title = 'Assistant Engineer');
-----------
Thanks in advance for support!
Yoonji.
Hi Song!
Thanks for reaching out.
Without this crucial linking condition, the subquery essentially checks if there exists any entry in the titles table where the title is 'Assistant Engineer', irrespective of which employee it belongs to. If at least one such record exists, the subquery returns true for all rows in the outer query (employees), resulting in the main query returning all employee records.
Thus, the condition t.emp_no = e.emp_no is essential to correctly link and filter employees based on specific titles from the titles table. Omitting this condition will indeed result in an incorrect result set where potentially all rows from the employees table are returned, assuming there's at least one 'Assistant Engineer' in the entire database.
Hope this helps.
Best,
Tsvetelin