PostgreSQL Subquery: Display the employee ID, first name, last name and department name of all employees

13. Write a SQL subquery to find the employee ID, first name, last name and department names of all employees.

Sample Solution:

Code:

-- This SQL query retrieves the employee ID, first name, last name, and department name of employees, ordered by department name.

SELECT employee_id, -- Selects the employee_id column from the employees table
       first_name, -- Selects the first_name column from the employees table
       last_name, -- Selects the last_name column from the employees table
       (SELECT department_name -- Subquery: Selects the department_name associated with the department_id of the current employee
        FROM departments d 
        WHERE e.department_id = d.department_id) -- Matches the department_id of the current employee (aliased as 'e') with the department_id in the departments table (aliased as 'd')
       AS department -- Renames the subquery result column as 'department'
FROM employees e -- Specifies the table from which to retrieve data (aliased as 'e'), in this case, the employees table
ORDER BY department; -- Orders the results by department name

Explanation:

• This SQL query retrieves the employee ID, first name, last name, and department name of employees, ordered by department name.
• The outermost SELECT statement selects the employee ID, first name, last name, and the department name associated with each employee.
• The subquery selects the department name from the departments table (aliased as 'd') where the department_id matches the department_id of the current employee (aliased as 'e').
• The result of the subquery is then used as the 'department' column in the main query.
• The ORDER BY clause sorts the result set by department name.

Sample table: employees

Sample table: departments

Output:

pg_exercises=# SELECT employee_id, first_name, last_name,
pg_exercises-# (SELECT department_name
pg_exercises(# FROM departments d
pg_exercises(# WHERE e.department_id = d.department_id)
pg_exercises-# department FROM employees e
pg_exercises-# ORDER BY department;
 employee_id | first_name  |  last_name  |    department
-------------+-------------+-------------+------------------
         206 | William     | Gietz       | Accounting
         205 | Shelley     | Higgins     | Accounting
         200 | Jennifer    | Whalen      | Administration
         102 | Lex         | De Haan     | Executive
         100 | Steven      | King        | Executive
         101 | Neena       | Kochhar     | Executive
         108 | Nancy       | Greenberg   | Finance
         110 | John        | Chen        | Finance
         111 | Ismael      | Sciarra     | Finance
         112 | Jose Manuel | Urman       | Finance
         109 | Daniel      | Faviet      | Finance
         113 | Luis        | Popp        | Finance
         203 | Susan       | Mavris      | Human Resources
         103 | Alexander   | Hunold      | IT
         107 | Diana       | Lorentz     | IT
         104 | Bruce       | Ernst       | IT
         105 | David       | Austin      | IT
         106 | Valli       | Pataballa   | IT
         201 | Michael     | Hartstein   | Marketing
         202 | Pat         | Fay         | Marketing
         204 | Hermann     | Baer        | Public Relations
...          |  ...        | ...         | ... 
         187 | Anthony     | Cabrio      | Shipping
         188 | Kelly       | Chung       | Shipping
         124 | Kevin       | Mourgos     | Shipping
         178 | Kimberely   | Grant       |
(106 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 who are not supervisors.
Next: Write a SQL subquery to find the employee ID, first name, last name and salary of all employees whose salary is above the average salary for their departments.