Thread: Returning null for joined tables when one column non existant

Hi all,

   I've got a query that crosses a few tables. For example:

SELECT
      a.foo, b.bar, c.baz
FROM
      aaa a, bbb b, ccc c
WHERE
      a.a_id=b.b_a_id AND a.a_id=c.c_a_id AND a.a_id=1;

   Obviously, if there is no match in 'bbb' or 'ccc' then nothing will
be returned, even if there is a match in one or both of the other
tables. Is there a way to say something like 'b.bar OR NULL' to make
sure that the matching columns with data still show and ones without a
match return NULL (or some string)?

Thanks!

Madison Kelly <linux@alteeve.com> wrote:

> Hi all,
>
>   I've got a query that crosses a few tables. For example:
>
> SELECT
>      a.foo, b.bar, c.baz
> FROM
>      aaa a, bbb b, ccc c
> WHERE
>      a.a_id=b.b_a_id AND a.a_id=c.c_a_id AND a.a_id=1;
>
>   Obviously, if there is no match in 'bbb' or 'ccc' then nothing will be
> returned, even if there is a match in one or both of the other tables. Is
> there a way to say something like 'b.bar OR NULL' to make sure that the
> matching columns with data still show and ones without a match return
> NULL (or some string)?

I think, you are looking for left join:

test=# create table a(id int, foo text);
CREATE TABLE
Zeit: 101,738 ms
test=*# create table b(id int, bar text);
CREATE TABLE
Zeit: 74,751 ms
test=*# create table c(id int, batz text);
CREATE TABLE
Zeit: 7,827 ms
test=*# commit;
COMMIT
Zeit: 4,193 ms
test=# select a.foo, b.bar, c.batz from a left join b on (a.id=b.id)
left join c on (a.id=c.id);
 foo | bar | batz
-----+-----+------
(0 Zeilen)

Zeit: 1,074 ms
test=*# insert into a values (1,'foo');
INSERT 0 1
Zeit: 0,469 ms
test=*# insert into b values (1,'bar');
INSERT 0 1
Zeit: 0,490 ms
test=*# insert into c values (1,'batz');
INSERT 0 1
Zeit: 0,733 ms
test=*# insert into a values (2,'foo');
INSERT 0 1
Zeit: 0,328 ms
test=*# select a.foo, b.bar, c.batz from a left join b on (a.id=b.id)
left join c on (a.id=c.id);
 foo | bar | batz
-----+-----+------
 foo | bar | batz
 foo |     |
(2 Zeilen)

Zeit: 0,595 ms
test=*# insert into c values (2,'bla');
INSERT 0 1
Zeit: 0,410 ms
test=*# select a.foo, b.bar, c.batz from a left join b on (a.id=b.id)
left join c on (a.id=c.id);
 foo | bar | batz
-----+-----+------
 foo | bar | batz
 foo |     | bla
(2 Zeilen)

Zeit: 0,615 ms




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°