Re: Full Outer Joins

On Monday 27 May 2002 19:10, Tom Lane wrote:
> 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.

Yes, I WANT to restrict both tables, but I can't figure out how ?
Where do I put the conditionals for each table ?

I have two tables of orders, temporary, and permanent.
For each day there are a number of orders to be delivered.
Each order may have any entry in the temporary AND/OR the permanent.

I want to get all orders that are in either table.
For each order, I need to know what table it is in (and if it is in both), and also join to the stockitems table to get the description.

I can do it all like this:

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

How do I do it without the subselects ?

Thanks
JohnT