CODEWITHSUNDEEP
sqloracle-database
Martin Dimitrov
Martin Dimitrov

Reputation: 4966

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.

Upvotes: 0

Views: 4440

Answers (4)

user5683823
user5683823

Reputation:

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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;

Upvotes: 1

Michael Piankov
Michael Piankov

Reputation: 1997

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.

Upvotes: 0

Massimo Petrus
Massimo Petrus

Reputation: 1891

 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)

Upvotes: 2

Related Questions