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

Sam Mason <sam@xxxxxxxxxxxxx> · Thu, 4 Sep 2008 23:06:18 +0100

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@xxxxxxxxxxxxx> 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