PostgreSQL Subquery: Find the name of the employees who are not supervisors

12. Write a SQL subquery to find the first_name and last_name of the employees who are not supervisors.

Sample Solution:

Code:

-- This SQL query retrieves the first name and last name of employees who do not have any direct reports (i.e., employees who are not managers).

SELECT b.first_name, -- Selects the first_name column from the employees table (aliased as 'b')
       b.last_name -- Selects the last_name column from the employees table (aliased as 'b')
FROM employees b -- Specifies the table from which to retrieve data (aliased as 'b'), in this case, the employees table
WHERE NOT EXISTS ( -- Filters the rows to include only those where there does not exist any employee (aliased as 'a') whose manager_id matches the employee_id of the current row (aliased as 'b')
    SELECT 'X' -- Subquery: Selects a constant value 'X' (could be any value, used for existence check)
    FROM employees a -- Specifies the table from which to retrieve data (aliased as 'a'), in this case, the employees table
    WHERE a.manager_id = b.employee_id -- Specifies the condition for existence check: where the manager_id of the employee (aliased as 'a') matches the employee_id of the current row (aliased as 'b')
);

Explanation:

• This SQL query retrieves the first name and last name of employees who do not have any direct reports (i.e., employees who are not managers).
• The outermost SELECT statement retrieves the first name and last name from the employees table (aliased as 'b').
• The WHERE clause filters the rows to include only those where there does not exist any employee (aliased as 'a') whose manager_id matches the employee_id of the current row (aliased as 'b').
• The subquery selects a constant value 'X' (could be any value, used for existence check) from the employees table (aliased as 'a'), where the manager_id of the employee (aliased as 'a') matches the employee_id of the current row (aliased as 'b').
• The NOT EXISTS condition checks for the non-existence of such rows.

Sample table: employees

Output:

pg_exercises=# SELECT b.first_name,b.last_name
pg_exercises-# FROM employees b
pg_exercises-# WHERE NOT EXISTS (
pg_exercises(# SELECT 'X'
pg_exercises(# FROM employees a
pg_exercises(# WHERE a.manager_id = b.employee_id);
 first_name  |  last_name
-------------+-------------
 Bruce       | Ernst
 David       | Austin
 Valli       | Pataballa
 Diana       | Lorentz
 Alexander   | Khoo
 Shelli      | Baida
 Sigal       | Tobias
 Guy         | Himuro
 Karen       | Colmenares
 Jason       | Mallin
 Michael     | Rogers
 Ki          | Gee
 Hazel       | Philtanker
 Stephen     | Stiles
 John        | Seo
 Joshua      | Patel
 Trenna      | Rajs
 Curtis      | Davies
 Randall     | Matos
 Peter       | Vargas
 Janette     | King
...          | ...
 Susan       | Mavris
 Hermann     | Baer
 William     | Gietz
(88 rows)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a subquery to find the first_name, last_name, job_id and salary of the employees who draws a salary that is higher than the salary of all the Shipping Clerk (JOB_ID = 'SH_CLERK'). Sort the results on salary from the lowest to highest.
Next: Write a SQL subquery to find the employee ID, first name, last name and department names of all employees.