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:26:39PM -0600, Scott Marlowe wrote:
> On Thu, Sep 4, 2008 at 3:22 PM, Sam Mason <sam@samason.me.uk> wrote:
> > 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;
>
> can't you just drop the on clause altogether?

I'm pretty sure you can't, what would be the point of a "CROSS JOIN"
otherwise?  This does raise the point that the SQL standard's CROSS
JOIN is somewhat limited.  It seems to make sense to allow the CROSS
somewhere in any join type and meaning that an ON or USING isn't
expected, making it valid to say:

  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.


  Sam