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

Re: Full Outer Joins

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Taylor <postgres(at)jtresponse(dot)co(dot)uk>
Cc: "Joel Burton" <joel(at)joelburton(dot)com>,"PgSQL Novice ML" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Full Outer Joins
Date: 2002-05-27 18:10:46
Message-ID: 14832.1022523046@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
John Taylor <postgres(at)jtresponse(dot)co(dot)uk> writes:
> Postgres doesn't seem to like that form with full outer joins ...

> SELECT o.stock,o.ordercurrent,o.type,s.stock,s.quantity
> FROM orderlines o FULL OUTER JOIN standingorders s ON (s.stock=o.stock AND  s.account=' 15048' and s.dayno=2 )
> WHERE o.theorder='  4494' AND (o.type='P' OR o.type='T')

> ERROR:  FULL JOIN is only supported with mergejoinable join conditions

While that implementation limitation is annoying (it's partly fixed in
development sources, FWIW), I really wonder why you'd want to do the
above.  With a FULL JOIN, you are going to get a lot of dummy rows out:
every s row *not* satisfying account=' 15048' and dayno=2 will still
generate a joined row (with nulls for the o columns) and also every o
row that doesn't join to an s row with account=' 15048' and dayno=2 will
generate a joined row (with nulls for the s columns).  It seems unlikely
that that's what you wanted.  I have a hard time envisioning a use for
FULL JOIN with join conditions that restrict only one of the tables;
seems like the restrictions ought to be in WHERE, instead.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: John TaylorDate: 2002-05-27 18:31:37
Subject: Re: Full Outer Joins
Previous:From: John TaylorDate: 2002-05-27 14:54:57
Subject: Re: Full Outer Joins

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