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)

Go to:

• PostgreSQL Subquery Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : 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.

Practice Online

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