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

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

Sample Solution:

Code:

SELECT * FROM departments 
WHERE department_id 
NOT IN (select department_id 
FROM employees);

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)

Practice Online

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

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