Fill a select with null when join isn't possible-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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)