Search code examples
mysqldatabasejoinwhere-clausesql-in

Subquery - W3Resources

I was wondering if my code works or not? Question #3 on this w3school exercise :

SELECT first_name, last_name
FROM employees
WHERE 
    manager_id IS NOT NULL
    AND manager_id IN (
        SELECT manager_id
        FROM departments
        WHERE location_id IN (
            SELECT location_id 
            FROM locations
            WHERE country_id='US' 
        )
    )
;
Solution

  • Yes, your code should work.

    Please note that the part of the SQL that checks if manager_id is not null is superfluous and can be removed (the IN clause already ensures that manager_id is set).

    It is possible to optimize the query using a set of JOINs, like :

    SELECT e.first_name, e.last_name
FROM employees e
INNER JOIN departments d ON d.manager_id = e.manager_id
INNER JOIN locations l ON l.department_id = d.department_id AND l.country_id='US'