Fill a select with null when join isn't possible

Question

I'm trying to do a select in n tables and show a few columns of each, but sometimes I can't match some columns and instead of getting a line with "null" the entire line is omitted.

For example:

table_a

id	...
1
2
3

table_b

id	name	...
1	a1	...
2	b2	...
3	c3	...

table_c

name	...
a1	...

And then I do the following select:

select
   a.id,
   c.name
from
   table_a a,
   table_b b,
   table_c
where
   ( 1 = 1 )
   and a.id = b.id
   and b.name = c.name

I'm geting:

id	name	...
1	a1	...

I'm looking for:

id	name	...
1	a1	...
2	null	...
3	null	...

How do I do that? I checked a few answers around including this one but I didn't get how to solve it.

You are using an ancient join syntax. Use proper joins, in this case a LEFT OUTER JOIN or FULL OUTER JOIN. Your current syntax is an implicit INNER JOIN which is obviously insufficient for your requirements. — JNevill, Oct 18 '22 at 17:09

score 0 · Answer 1 · answered Oct 18 '22 at 17:09

0

You should use a left join, not sure on oracle specifically but it would look something like:

select
   a.id,
   c.name
from
   table_a a
   LEFT JOIN table_b b ON (a.id = b.id)
   LEFT JOIN table_c c ON (b.name = c.name)

answered Oct 18 '22 at 17:09

dave

62,300
5
72
93

Dave, i tried this one but the output still the same, only the id 1. – culpinha Oct 18 '22 at 17:23

score 0 · Accepted Answer · answered Oct 18 '22 at 17:14

You can use an OUTER JOIN:

SELECT a.id,
       c.name
FROM   table_a a
       LEFT OUTER JOIN table_b b
       ON (a.id = b.id)
       LEFT OUTER JOIN table_c c
       ON (b.name = c.name)

or, depending on precedence of the joins:

SELECT a.id,
       c.name
FROM   table_a a
       LEFT OUTER JOIN (
         table_b b
         INNER JOIN table_c c
         ON (b.name = c.name)
       )
       ON (a.id = b.id)

Which, for the sample data:

CREATE TABLE table_a (id) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;

CREATE TABLE table_b (id, name) AS
SELECT 1, 'a1' FROM DUAL UNION ALL
SELECT 2, 'b1' FROM DUAL UNION ALL
SELECT 3, 'c1' FROM DUAL;

CREATE TABLE table_c (name) AS
SELECT 'a1' FROM DUAL;

Would both output:

ID	NAME
1	a1
2	null
3	null

fiddle

MT0, first i thank you for organizing my question (since i'm kinda new here, i don't know how to edit it properly). Second, i want to thank you for your answer, it worked pretty well i just had to edit the order from each join as you did show. — culpinha, Oct 18 '22 at 17:34

Fill a select with null when join isn't possible

2 Answers2