Oracle SQL: joining with an empty table

Question

I have say 3 tables, which have a key that links through all three like so...

select a.call_id, b.name, c.year
from table1 a, table2 b, table3 c
where a.call_id = b.call_id
and b.call_id = c.call_id
and a.call_id = 123

The problem is table 3 doesn't have a record that matches the id of 123, thus the entire query returns 0 rows, even though there is a match in tables 1 & 2. Is there a way I can have oracle just return say null for the c.year field and still get results for a.call_id and b.name?

Without running multiple queries I may add (by multiple queries I mean running two separate select queries completely independent of each other — HelloWorld, Oct 25 '13 at 22:23
You want a LEFT OUTER JOIN. See http://stackoverflow.com/questions/6559261/left-outer-join-using-sign-in-oracle-11g — , Oct 25 '13 at 22:32

score 2 · Answer 1 · answered Oct 25 '13 at 22:27

2

I guess you just need to translate this to JOIN:

SELECT a.call_id, b.name, c.year
from table1 a
JOIN table2 b
ON a.call_id = b.call_id
LEFT JOIN table3 c
ON and b.call_id = c.call_id
WHERE a.call_id = 123

answered Oct 25 '13 at 22:27

Yochai

176
4

score 2 · Accepted Answer · answered Oct 25 '13 at 22:28

Yes, you have to use LEFT join:

select a.call_id, b.name, c.year
  from table1 a
    JOIN table2 b ON (a.call_id = b.call_id)
    LEFT JOIN table3 c ON (b.call_id = c.call_id)
  WHERE a.call_id = 123;

Read more about joins here: Visual Explanation of Joins by Jeff Atwood

score 1 · Answer 3 · answered Oct 26 '13 at 07:00

1

While you should use ANSI SQL, you could also do it the Oracle way with (+)

select a.call_id, b.name, c.year
from table1 a, table2 b, table3 c
where a.call_id = b.call_id
and b.call_id = c.call_id(+)
and a.call_id = 123

answered Oct 26 '13 at 07:00

barker

31
3

Oracle SQL: joining with an empty table

3 Answers3