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:

SELECT employee_id, first_name, last_name, 
(SELECT department_name 
FROM departments d 
WHERE e.department_id = d.department_id) 
department FROM employees e 
ORDER BY department;

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.