Date:         Wed, 10 Jan 2007 15:05:04 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: SQL with 1=1 statement after join clause
Comments: To: Irin later <irinfigvam@yahoo.com>,
          "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
In-Reply-To:  <201145.46678.qm@web34109.mail.mud.yahoo.com>
Content-Type: text/plain; charset="iso-8859-1"

Apologies for jumping in on this discussion .... We have tested the JOIN ... ON conditions recently and found:

The ON condition does not have to include anything other then an expression that evaluates to a Boolean Value, 1 or 0. In fact, 'On 1' works as well as an equality or an other theta condition.

The WHERE condition restricts the a and b in 'a JOIN b ON 1' to rows that meet the WHERE condition. It does not construct a Cartesian product and then subset on the WHERE conditions. It likely works the same way in a CROSSJOIN query (though I have not tested this).
Sig

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Irin later
Sent: Wednesday, January 10, 2007 2:18 PM
To: Howard Schreier <hs AT dc-sug DOT org>; SAS-L@LISTSERV.UGA.EDU
Subject: Re: SQL with 1=1 statement after join clause


Howard,
  My understanding is that CROSS JOIN creates a Cartesian product of the rows in both tables, just like the WHERE clause is forgotten (in case there would be no other conditions).
  I mean ... joining should be implemented by some particular field.

  I just wonder if it possible that there is any OTHER reason in this particular case  why such a Cartesian approach 1=1 (if it is Cartesian) was accepted in the production code?

  Thank you in advance,

  Irin



"Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> wrote:  On Wed, 10 Jan 2007 09:46:36 -0800, Irin later wrote:

>I review somebody�s code and I do not understand why statement 1=1 is
used. What does it really do? I really have no idea
>
> select distinct *
> from c
> join P
> ON 1=1
> where c.Tag = 'ME'
> and P.Tag = 'PR'
>
> Could you please explain me ?
>
> Thanks,
>
> Irin

With the chosen syntax, the ON clause is required. The condition "1=1" is always true, so the join is actually unrestricted.

You can code "cross join" instead of "join" and omit the ON clause. That's more straightforward.


 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around http://mail.yahoo.com