CODEWITHSUNDEEP
sqloracle11g
user2601489
user2601489

Reputation: 11

How can I show in a single SQL query: What departments don't have any employees and what employees don't have a department (Oracle SQL)?

I've been trying to figure this out for a while and still no luck. Would I combine the following with a 'is null'?

select distinct 
       e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
from employees e 
  join departments d on (e.department_id = d.department_id)

Upvotes: 1

Views: 100

Answers (1)

StanislavL
StanislavL

Reputation: 57381

Example based on Joe's comment

select distinct 
       e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
from employees e 
  full outer join departments d on (e.department_id = d.department_id)
where e.department_id is null or d.department_id is null

Or this way with union 

select distinct 
       e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
from employees e 
  left outer join departments d on (e.department_id = d.department_id)
where d.department_id is null 
union 
select distinct 
       e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
from employees e 
  right outer join departments d on (e.department_id = d.department_id)
where e.department_id is null

Upvotes: 2

Related Questions