PostgreSQL Subquery: Find the name of the employees who are managers

4. Write a SQL subquery to find the first_name and last_name of the employees who are working as a manager.

Sample Solution:

Code:

-- This SQL query retrieves the first name and last name of employees who are also managers.

SELECT first_name, -- Selects the first_name column from the employees table
       last_name -- Selects the last_name column from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE (employee_id -- Filters the rows to include only those where the employee_id matches a manager_id
        IN (SELECT manager_id -- Subquery: Selects the manager_id of employees who are managers
            FROM employees)
      );

Explanation:

• This SQL query retrieves the first name and last name of employees who are also managers.
• The outer SELECT statement retrieves the first name and last name from the employees table.
• The WHERE clause filters the rows to include only those where the employee_id matches a manager_id obtained from the subquery.
• The subquery selects manager_ids of employees who are managers.
• The subquery is enclosed in parentheses and executed before the outer query.

Sample table: employees

Output:

pg_exercises=# SELECT first_name, last_name
pg_exercises-# FROM employees
pg_exercises-# WHERE (employee_id
pg_exercises(# IN (SELECT manager_id
pg_exercises(# FROM employees));

 first_name | last_name
------------+-----------
 Alexander  | Hunold
 Den        | Raphaely
 Steven     | King
 Neena      | Kochhar
 Payam      | Kaufling
 Shanta     | Vollman
 Kevin      | Mourgos
 John       | Russell
 Karen      | Partners
 Alberto    | Errazuriz
 Gerald     | Cambrault
 Eleni      | Zlotkey
 Lex        | De Haan
 Matthew    | Weiss
 Adam       | Fripp
 Nancy      | Greenberg
 Michael    | Hartstein
 Shelley    | Higgins
(18 rows)

Practice Online

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

Previous: Write a SQL subquery to find the first_name and last_name of the employees under a manager who works for a department based in the United States.
Next: Write a SQL subquery to find the first_name, last_name and salary, which is greater than the average salary of the employees.