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

On Thu, Sep 04, 2008 at 02:58:39PM -0700, Richard Broersma wrote:
> On Thu, Sep 4, 2008 at 2:54 PM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> > SELECT * FROM foo FULL OUTER CROSS JOIN bar;
> >
> > Admittedly, there aren't too many use cases for this! But it would make
> > things a bit more regular.
>
> SELECT * FROM foo, bar;
>
> Isn't this have the same results, but with out the cross join?

I hope not! Try running:

SELECT * FROM
(SELECT 1 AS a WHERE FALSE) f,
(SELECT 2 AS b ) b;

and then try:

SELECT * FROM
(SELECT 1 AS a WHERE FALSE) f FULL OUTER JOIN
(SELECT 2 AS b ) b ON TRUE;

I hope you get zero rows back from the first query, and a single row
back from the second with a NULL value in the "a" column.

Sam