Results 1 to 3 of 3

Thread: where 1=1!

  1. #1
    Join Date
    Apr 2011

    Unanswered: where 1=1!

    Hello All,

    A query has come in for execution which goes like this:

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

    I am not sure what the actual use of the part "where 1=1" is in a query, be it simple or complex.
    Do we really have advantages when using this where clause?
    Does it make a visible difference in the response we get for the query?
    Or is it just a bit of sloppy coding?

    Would like to know your opinions.


    And a good day to all!!

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    as you guessed, the 1=1 condition does not alter the results of the query

    (in fact, the optimizer will likely remove it anyway)

    however, it is ~very~ useful when building a query

    consider this scenario -- a web form has a number of form fields, and each form field can either have a value in it, or, if left blank, represents a column that is not to be searched

    of course, if all the fields are left blank, the search is supposed to return all rows

    so as the application logic processes the form fields, it must decide which is the first non-empty field, and this becomes the first condition in the WHERE clause

    thus, it has to decide when to generate the WHERE keyword, and when to generate the AND keyword for a subsequent condition

    and of course, if all the fields are left blank, then the WHERE clause isn't really needed

    if you've ever built up a query string like this, you know that the code to decide whether to use WHERE or AND in front of each form fields gets complex and repetitive

    resulting in application code bloat

    now consider a query that has WHERE 1=1 already built into it

    for each non-empty form field, you just append another AND

    at the end, if all the form fields were empty, the entire WHERE clause is 1=1, which is always true for all rows, returning everything, as expected

    make more sense now? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Yes, that makes sense.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts