Madison Kelly <linux@xxxxxxxxxxx> 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° -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
