Where is my WHERE? 20188367001

Where is my WHERE?

December 21, 2007 Database, Oracle, PL/SQL 10 Comments

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

select ...
  from ...
 where 1=1
    and ...

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.*
  from t
 where col1 = 'value'
   and id = 3
;

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> select t.*
  2    from t
  3     and t.id = 3
  4  /
   and t.id = 3
   *
ERROR at line 3:
ORA-00933: SQL command not properly ended

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
as
select rownum id
  from all_objects
 where rownum <= 10;

create table s
as
select *
  from t;

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

select t.*
     , s.*
  from t
  join s
    on (t.id = s.id)

And add a couple of predicates.

 where t.id = 3
   and s.id = 3

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

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

        ID         ID
---------- ----------
         3          3

SQL>

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> select
  2       *
  3    from t
  4    join s
  5      USING (id) --Notice the difference here
  6  —- where t.id = 3
  7     and s.id = 3
  8  /

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> with t as
  2  (select rownum id
  3    from all_objects
  4   where rownum <= 10
  5  )
  6  , s as
  7  (select *
  8     from t
  9  )
 10  select *
 11  from t
 12  join s
 13  USING (id) ----Notice the difference here
 14   where t.id = 3
 15  and s.id = 3
 16  /
and s.id = 3
    *
ERROR at line 15:
ORA-25154: column part of USING clause cannot have qualifier


SQL> 
SQL> ed
Wrote file afiedt.buf

  1  with t as
  2  (select rownum id
  3    from all_objects
  4   where rownum <= 10
  5  )
  6  , s as
  7  (select *
  8     from t
  9  )
 10  select *
 11  from t
 12  join s
 13  USING (id) ----Notice the difference here
 14  ---- where t.id = 3
 15* and s.id = 3
SQL> /
and s.id = 3
*
ERROR at line 15:
ORA-00933: SQL command not properly ended

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.

Incidentally many sql generators – programs that generate sql based on forms etc – will start the where with 1=1 so they don’t have to work out which is the first condition.

Related Posts

About The Author

Alex Nuijten

10 Comments

  1. Steve Pratt July 1, 2008

    Back in the ‘old’ days of RDBMSes, sometimes it was necessary to fool the optimizer in order to force a specific access path. And one technique was to include an absolute truth in the WHERE clause. (this was obviously before the advent of optimizer HINTS.)

  2. Alex Nuijten December 23, 2007

    Thank you for the link, Rob. Very enlightening….

  3. Borkur Steingrimsson December 21, 2007

    well, the error you get

    ERROR at line 15:
    ORA-25154: column part of USING clause cannot have qualifier

    is exactly that. You can not put anywhere in your statement things like “t.id =3” (in the SELECT or the WHERE clause) as the error message suggests. Instead you would have to put just “id = 3″, without the table alias (qualifier). This also applies to doing something like ” SELECT S.* from …” and then ANSI JOIN with the USING keyword. It would result in the same error being raised. If you alter your example to reflect this, you will then see that the USING part does not extend outside of the parentheses and the WHERE keyword must follow before you put other predicates.

  4. Rob van Wijk December 21, 2007

    Alex,

    As said, you converted a predicate to a join condition. This can be done safely for inner joins, i.e. it doesn’t affect the result of your query, but if you do the same for an outer join the result set may change. See this article from Jonathan Gennick about this behaviour: http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html

    Regards,
    Rob.

  5. Alex Nuijten December 21, 2007

    Thank you all for your comments. I took the liberty to place them into the main section of the post, as the layout in the comments section is less than ideal. 🙂

  6. Niall Litchfield December 21, 2007

    OK lets see if the pre tag is accepted so you can see what I mean

    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

  7. Niall Litchfield December 21, 2007

    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.

    Incidentally many sql generators – programs that generate sql based on forms etc – will start the where with 1=1 so they don’t have to work out which is the first condition.

  8. Borkur Steingrimsson December 21, 2007

    Well, this is for sure not a bug. This is really normal and expected behavior. The ON clause extends to line 7 in your last example, since you don’t have to use parenthesis. If you had written something like

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

    Then your query would fail.

    Regarding leaving a “1=1” in production could, whilst not being very clean or pretty, I would not find it likely to cause a lot overhead 🙂 I wonder how many queries per second you would need to be able to measure the effect of it ..

  9. Dominic Brooks December 21, 2007

    ‘where 1=1’ is often also used by code that dynamically generates queries depending on inputs, presumably so it doesn’t need to figure out whether a dynamically generated predicate should use ‘where’ or ‘and’.

  10. Marco Gralike December 21, 2007

    Ehhh… “where 1=1”, if it remains in the production code, isn’t it also a performance degradation issue (doing an unneeded check that costs at least CPU time?)