Re: Full outer join? Cross product? How to blend two queries into single row? - Mailing list pgsql-general

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