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

Madison Kelly <linux(at)alteeve(dot)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°