Martin Dimitrov Martin Dimitrov - 1 year ago 65
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

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.

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
not work? Thanks!


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

Answer Source

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