Martin Dimitrov Martin Dimitrov - 1 month ago 4
SQL Question

SQL query to find locations with department names as well as locations without departments

I came across the following SQL question about the famous

HR
schema which ships with Oracle:


Write a SQL query to find all the locations and the departments for each location along with the locations that do not have department. Don't use OUTER JOIN.


With
OUTER JOIN
it is easy:

SELECT department_name, city
FROM locations
LEFT OUTER JOIN departments USING (location_id);


I get 43 results. I tried with this:

SELECT department_name, city
FROM locations l, departments d
WHERE l.location_id = d.location_id OR
l.location_id NOT IN (SELECT DISTINCT location_id FROM departments);


enter image description here

but I get only 27 rows as if I do:

SELECT department_name, city
FROM locations
JOIN departments USING (location_id);


Why does the
OR
not work? Thanks!




EDIT:

As pointed out by @mathguy, I had a department with
NULL
in the
location_id
column in the
departments
table. That is why the
NOT IN
returns no row. Otherwise I would have many more rows looking for the location id from the
departments
table.

Answer

Get the city, department_name from the inner join, then union all like so:

select city, department_name 
  from <inner join>
union all
select city, NULL
  from locations
 where location_id not in (select location_id from departments);

The second branch of union all will give you the 16 cities with no departments located there.

NOTE: The NOT IN condition will not work if there are departments with NULL in the location_id column. If that is possible, the condition can be altered to not in (select location_id from departments where location_id is not null).

Comments