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:

-- 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)

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.