Resolved: Question about the mechanism of the EXISTS statement

Hi Vũ!
Thanks for reaching out.

If you run this query:

SELECT 
    *
FROM
    employees e
WHERE
    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 e
WHERE
    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
    titles
WHERE
    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