How to join two tables with same primary key name but different values

Question

I learnt today about "exclusive entities". I have following ER diagram:

With 2 exclusive entities. When I do a physical data model via power designers creation tool it resolves to

Now I want to join both in one table and display the id and room_name

The structure I want to get is:

room_id | room_name

The room_id's in room and bedroom are different. bedroom has for example the ids 1-10 and kitchen the ids from 11-20.

I have the feeling that I might have a bad design, because the joins I tried don't get me the desired result.

My best guess is to use a natural join like*

SELECT room_id, room_name 
FROM bedroom 
NATURAL JOIN kitchen;

This returns the correct format but the results are empty.

Furthermore I'm looking to get a table in the format:

room_id | roon_name | bedCount | chairCount

Answer 1

You can do exactly what you requested with a full outer join:

select room_id, room_name, b.bedcount, k.chaircount
from   bedroom b full outer join kitchen k using (room_id, room_name)
;

This is almost equivalent to the query you attempted - but you need a natural FULL OUTER join rather than the (inner) natural join you tried. Note, however, that many (most?) practitioners view the natural join syntax with suspicion, for various reasons; the using clause as I demonstrated above seems to be accepted more easily. (Of course, even with a natural join, you might be best served still naming specifically the columns you want in the output.)

Although the more common approach for cases like this is a straightforward union all:

select room_id, room_name, bedcount, cast (null as number) as chaircount
from   bedroom
UNION ALL
select room_id, room_name, null    , chaircount
from   kitchen
;