Find departments with no employees-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

You can try below query-

SELECT * 
  FROM departments
 WHERE department_id in (SELECT department_id
                           FROM departments
                          minus 
                         SELECT DISTINCT department_id
                           FROM employees
                        );

Demo.