Skip site navigation (1) Skip section navigation (2)

Re: Returning null for joined tables when one column nonexistant

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Returning null for joined tables when one column nonexistant
Date: 2009-02-26 19:23:45
Message-ID: 20090226192345.GA10110@tux (view raw or flat)
Thread:
Lists: pgsql-general
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°

In response to

pgsql-general by date

Next:From: Lennin CaroDate: 2009-02-26 19:38:26
Subject: Re: Problem setting up PostgreSQL
Previous:From: Andrew GouldDate: 2009-02-26 19:04:26
Subject: Re: Off Topic: ICD-10 codes in a database table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group