4.18

For the database of Figure 4.12, write a query to find the ID of each employee with no manager. Note that an employee may simply have no manager listed or may have a null manager. Write your query using an outer join and then write it again using no outer join at all.

---

Note that, the primary key of the manages relation is id. Thus each employee can have at most one manager.

Using an outer join we can write the query as:-

SELECT e.id
FROM employee e LEFT OUTER JOIN manages m 
    ON e.id = m.id
WHERE m.id IS NULL 
    OR m.manager_id IS NULL;

Using no outer join we can write the query as:-

SELECT id
FROM employee
WHERE id NOT IN (
    -- this gives us all of the employee ids that have a manager
    SELECT id
    FROM manages
    WHERE manager_id IS NOT NULL
);