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.
What is the difficulty level of this exercise?
New Content: Composer: Dependency manager for PHP, R Programming