SQL Exercise: List the department where there are no employees

SQL subqueries on employee Database: Exercise-77 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

77. From the following table, write a SQL query to find those departments where no employee works. Return department ID.

Sample table: employees

Sample table: department

Sample Solution:

SELECT b.dep_id,
       count(a.dep_id)
FROM department b
LEFT OUTER JOIN employees a ON a.dep_id=b.dep_id
GROUP BY b.dep_id
HAVING count(a.dep_id) = 0;

Sample Output:

 dep_id | count
--------+-------
   4001 |     0
(1 row)

Explanation:

The said query in SQL that selects the department IDs for all departments with no employees from the 'employees' table.

The left outer join combines all rows from the 'department' and the 'employees' table based on the 'dep_id' column.

The resulting table is then grouped by the 'dep_id' column, using the 'GROUP BY' clause.

The 'COUNT' function counts the number of rows in each group.

The 'HAVING' clause filters the groups to only include those with a count of zero.

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Count the number of employees performing manager duties.
Next SQL Exercise: SQL Exercises, Practice, Solution