Where is my WHERE?

Sometimes I encounter query which have a WHERE clause that starts with:

select ...<br />  from ...<br /> where 1=1<br />    and ...<br />

When asked the reason for this predicate, most of the time (if not all) "Because it’s easy if you want to comment out some of the conditions". Say you have a query that looks like this:

select t.*<br />  from t<br /> where col1 = 'value'<br />   and id = 3<br />;<br />

And you want to comment out the first condition (col1 = ‘value’), you would have to rearrange the whole query. Simply commenting out the WHERE condition would lead to this statement and this is not valid syntax:

SQL&gt; select t.*<br />  2    from t<br />  3     and t.id = 3<br />  4  /<br />   and t.id = 3<br />   *<br />ERROR at line 3:<br />ORA-00933: SQL command not properly ended<br />

ANSI JOIN

The other day I noticed something weird with Joins, ANSI style. Let me show you what I found.

First off, let’s create a couple of simple tables. Both of them have a single column with values ranging from 1 through 10.

create table t<br />as<br />select rownum id<br />  from all_objects<br /> where rownum &lt;= 10;<br /><br />create table s<br />as<br />select *<br />  from t;<br />

Now lets join these two tables using the ANSI style join syntax:

select t.*<br />     , s.*<br />  from t<br />  join s<br />    on (t.id = s.id)<br />

And add a couple of predicates.

 where t.id = 3<br />   and s.id = 3<br />

Now lets comment out the WHERE clause, and lo and behold… the query still runs.

SQL&gt; select t.*<br />  2       , s.*<br />  3    from t<br />  4    join s<br />  5      on (t.id = s.id)<br />  6  ---- where t.id = 3<br />  7     and s.id = 3<br />  8  /<br /><br />        ID         ID<br />---------- ----------<br />         3          3<br /><br />SQL&gt;<br />

So, even if the WHERE keyword is missing, it doesn’t seem to be a problem. At least not on Oracle 9iR2 or Oracle 10gR2.

Whether this is a bug or a feaure, I don’t know…

Reponse to Borkur Steingrimsson

Well, I sure didn’t expect it But it makes sense that the ON-condition is extended on the next line, now that you mention it.
Just to reproduce your query (as it doesn’t look that great in the comment section)

SQL&gt; select<br />  2       *<br />  3    from t<br />  4    join s<br />  5      USING (id) --Notice the difference here<br />  6  —- where t.id = 3<br />  7     and s.id = 3<br />  8  /<br />

I just tried out this query, with and without the WHERE clause, but both failed. Appearently the USING clause also extends to the next line, outside of the parenthesis.

SQL&gt; with t as<br />  2  (select rownum id<br />  3    from all_objects<br />  4   where rownum &lt;= 10<br />  5  )<br />  6  , s as<br />  7  (select *<br />  8     from t<br />  9  )<br /> 10  select *<br /> 11  from t<br /> 12  join s<br /> 13  USING (id) ----Notice the difference here<br /> 14   where t.id = 3<br /> 15  and s.id = 3<br /> 16  /<br />and s.id = 3<br />    *<br />ERROR at line 15:<br />ORA-25154: column part of USING clause cannot have qualifier<br /><br /><br />SQL&gt; <br />SQL&gt; ed<br />Wrote file afiedt.buf<br /><br />  1  with t as<br />  2  (select rownum id<br />  3    from all_objects<br />  4   where rownum &lt;= 10<br />  5  )<br />  6  , s as<br />  7  (select *<br />  8     from t<br />  9  )<br /> 10  select *<br /> 11  from t<br /> 12  join s<br /> 13  USING (id) ----Notice the difference here<br /> 14  ---- where t.id = 3<br /> 15* and s.id = 3<br />SQL&gt; /<br />and s.id = 3<br />*<br />ERROR at line 15:<br />ORA-00933: SQL command not properly ended<br />

Comment by Niall Litchfield (but nicely formatted)

The comment out issue is one reason why I use the writing style

SELECT

      first_col

,     second_col

...

...

,     last_col

FROM

      first_tab join second_tab

      on (join clause)

WHERE

      first_condition

and second_condition

and ...

GROUP BY

      first groupby

,     second groupby

...

HAVING

     first having

,    second having

...

ORDER BY

     first_col

,    second_col

i.e KEYWORDS on their own, start lines with a , (if appropriate) I also find it more readable.