PostgreSQL Subquery: Display the information for all the departments where no employee is working

Last update on October 27 2025 12:16:42 (UTC/GMT +8 hours)

18. Write a subquery to display the information for all the departments where no employee is working.

Sample Solution:

Code:

-- This SQL query retrieves all departments that do not have any associated employees.

SELECT * -- Selects all columns from the departments table
FROM departments -- Specifies the table from which to retrieve data, in this case, the departments table
WHERE department_id NOT IN ( -- Filters the rows to include only those departments whose department_id does not exist in the subquery result
    SELECT department_id -- Subquery: Selects department_id from employees table
    FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
);

Explanation:

This SQL query retrieves all departments that do not have any associated employees.
The outermost SELECT statement selects all columns from the departments table.
The WHERE clause filters the rows to include only those departments whose department_id does not exist in the subquery result.
The subquery selects department_id from the employees table.

Sample table: employees

Output:

pg_exercises=# SELECT * FROM departments
pg_exercises-# WHERE department_id
pg_exercises-# NOT IN (select department_id
pg_exercises(# FROM employees);

 department_id |   department_name    | manager_id | location_id
---------------+----------------------+------------+-------------
           120 | Treasury             |          0 |        1700
           130 | Corporate Tax        |          0 |        1700
           140 | Control And Credit   |          0 |        1700
           150 | Shareholder Services |          0 |        1700
           160 | Benefits             |          0 |        1700
           170 | Manufacturing        |          0 |        1700
           180 | Construction         |          0 |        1700
           190 | Contracting          |          0 |        1700
           200 | Operations           |          0 |        1700
           210 | IT Support           |          0 |        1700
           220 | NOC                  |          0 |        1700
           230 | IT Helpdesk          |          0 |        1700
           240 | Government Sales     |          0 |        1700
           250 | Retail Sales         |          0 |        1700
           260 | Recruiting           |          0 |        1700
           270 | Payroll              |          0 |        1700
(16 rows)

Go to:

PREV : Write a subquery to select last 10 records from a table.
NEXT : Write a query to get three maximum salaries.

Practice Online

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