Re: Full outer join? Cross product? How to blend two queries into single row?

On Thu, Sep 04, 2008 at 03:43:33PM -0500, D. Dante Lorenso wrote:
> I came up with this query that works, but seems overly complicated:
>
> SELECT a.col1, a.col2, b.col3, b.col4
> FROM
> (SELECT col1, col3, TRUE AS join_column
> FROM mytable
> WHERE uid = 'abc') a
> FULL OUTER JOIN
> (SELECT col3, col4, TRUE AS join_column
> FROM mytable
> WHERE uid = 'def') b
> ON (a.join_column = b.join_column);
>
> Is this how to do it, or is there a simpler syntax I'm missing?

The "ON" clause is just a normal expression, so you can just put a
"TRUE" in there if you want a cross join. I.e. the following is a
minimal full outer cross join:

SELECT * FROM foo FULL OUTER JOIN bar ON TRUE;

This still seems a little nasty and I'd prefer to do something like:

SELECT
((SELECT ROW(a,b) FROM foo)).*,
((SELECT ROW(c,d) FROM bar)).*;

And have it do the same thing (if you have more than one row returned
you'd get a nice error message and everything). But I can't seem to get
the syntax right, anyone got a cluebat?

Sam