Re: Full Outer Joins

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