Hi, I still haven't got the mechanism of the EXISTS statement with subquery. I did the homework by executing this script and it returned 300027 rows: select *from employeeswhere exists (select * from titles where title = 'Assistant Engineer'); as you can see I lacked the t.emp_no = e.emp_no AND title = 'Assistant Engineer' in the where statement of the inner query. My question is when will the exists return TRUE? I thought that it would use some values in the 'employees' table that is shared between two tables which is the emp_no column, and it would return TRUE only if the value found in the 'employees' table matched that in the 'titles' table ?? Can you explain more about the 'row by row' mechanism of the EXISTS statement? Thank you!

Hi Vũ!Thanks for reaching out. If you run this query: SELECT *FROM employees eWHERE EXISTS( SELECT * FROM titles WHERE title = 'Assistant Engineer');the result will be all rows. Why? Because the condition in the subquery is always true. But we want to limit only these results which have title="Assistant Engineer". So what we need? Some line of code which will ensure we can get only these records. So, please use: SELECT *FROM employees eWHERE EXISTS( SELECT * FROM titles t WHERE t.emp_no = e.emp_no AND title = 'Assistant Engineer');This query will give a result set with 15 128 rows. Also the following query will give also 15 128 rows: SELECT *FROM titlesWHERE title = 'Assistant Engineer';But because we use in our query EXISTS we need to limit the result set with this additional line of code. The point is to exercise EXISTS. Please, watch the lecture one more time. Thank you! The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records. Hope this helps.Best,Tsvetelin

Follow this topic

Vũ Phạm

Last answered:

06 Feb 2023

Posted on:

04 Feb 2023

Resolved: Question about the mechanism of the EXISTS statement

in SQL / SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE

1 answers ( 1 marked as helpful)

Tsvetelin Tsonkov

Instructor

Posted on:

06 Feb 2023

Helpful answer

Resolved: Question about the mechanism of the EXISTS statement

Submit an answer

related questions