# Find departments with no employees

We were given an assignment in class to provide at the minimum two different solutions to find departments, which don’t have any employees. As you can see from below I completed the task successfully.

An additional solution is extra credit, which I like to get. Unfortunately, I can’t think of a third solution and was hoping someone can point me in the right direction. Would something with a MINUS function work?

Below is my setup and 2 working test cases. Any suggestions and help would be greatly appreciated.

```CREATE TABLE departments
(
department_id,
department_name
) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 3, 'Sales' FROM DUAL UNION ALL
SELECT 2, 'DBA' FROM DUAL;

CREATE TABLE employees
(
employee_id,
first_name, last_name,
hire_date,
salary,
department_id
) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 100000, 1 FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04', 50000, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60000, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70000,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 88000,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10', 66666, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL;

/* departments with no employees */

select
d.department_id,
d.department_name
from
employees e
right join
departments d on e.department_id = d.department_id
group by
d.department_id, d.department_name
having
count(e.employee_id) = 0;
```

Output:

```DEPARTMENT_ID    DEPARTMENT_NAME
--------------------------------
3          Sales
```
```SELECT
d.department_id,
d.department_name
FROM
departments d
WHERE
NOT EXISTS (SELECT * FROM employees e
WHERE d.department_id = e.department_id)
```

Output:

```DEPARTMENT_ID    DEPARTMENT_NAME
--------------------------------
3          Sales
```

You may also use left join as the following:

```select
d.department_id,
d.department_name
from departments d left join employees e
on e.department_id=d.department_id
where e.employee_id is null
```

Also, you may use a sub query as the following:

```select department_id, department_name
from departments
where department_id not in (select department_id from employees)
```

And with `minus` you may try:

```select department_id, department_name
from departments
minus
select
d.department_id,
d.department_name
from departments d join employees e
on e.department_id=d.department_id
```

See a demo.

User contributions licensed under: CC BY-SA
8 People found this is helpful