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);

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.

4 Answers

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).

 select l.city, d.department_name from locations l, departments d where l.location_id=d.location_id UNION select l2.city, null department_name from locations l2
where not exists (select 1 from depertments d2 where d2.location_id=l2.location_id) 

Use a correlated subquery:

SELECT l.*, (SELECT d.department_name FROM departments d WHERE d.location_id = l.location_id ) as department_name
FROM locations l;

If there is a concern about multiple departments in a location, then just aggregate them together:

SELECT l.*, (SELECT LISTAGG(d.department_name, ', ') WITHIN GROUP (ORDER BY d.department_name) as department_names FROM departments d WHERE d.location_id = l.location_id ) as department_name
FROM locations l;

Well It's simple if you try to imagane some intermediate steps:

lets imagane cross join of table and after it filter the result with your where clause. In that rowset not any rows with not null locations and null departments will be presented. You may prove it with next query:

SELECT department_name, city
FROM locations l, departments d
WHERE d.location_id is null;
no row selected

But you specify l.location_id NOT IN (SELECT DISTINCT location_id FROM departments);. This condition not influent to cross join between table.
Because of it you should get extra rows for loction without departments.

And thats why you need left join or union clause.