What is the purpose of using WHERE 1 1 in SQL statements

Question

Possible Duplicates    Why would a sql query have  ldquo where 1   1 rdquo    Why would someone use WHERE 1 1 AND  lt conditions gt  in a SQL clause      I ve seen that a lot in different query examples and it goes to probably all SQL engines   If there is a query that has no conditions defined people  and specially ORM frameworks  often add always-true condition WHERE 1   1 or something like that   So instead of  SELECT id  name FROM users    they use  SELECT id  name FROM users WHERE 1   1    The only possible reason I could think of if you are adding conditions dynamically you don t have to worry about stripping the initial AND but still quite often this 1   1 condition is stripped if there is an actual condition in the query   Actual example from CakePHP  generated by framework     no conditions   SELECT  User   id    User   login  FROM  users  AS  User  WHERE 1   1  ORDER BY  User   id  ASC     with condition   SELECT  User   id    User   login  FROM  users  AS  User   WHERE  User   login     me example com  LIMIT 1    Is there any reason for adding that extra condition

User · Answer

Using 1 1 is actually not a very good idea as this can cause full table scans by itself   See this--  T-SQL 1 1 Performance Hit

User · Answer

People use it because they re inherently lazy when building dynamic SQL queries  If you start with a  where 1   1  then all your extra clauses just start with  and  and you don t have to figure out   Not that there s anything wrong with being inherently lazy  I ve seen doubly-linked lists where an  empty  list consists of two sentinel nodes and you start processing at the first- gt next up until last- gt prev inclusive   This actually removed all the special handling code for deleting first and last nodes  In this set-up  every node was a middle node since you weren t able to delete first or last  Two nodes were wasted but the code was simpler and  ever so slightly  faster   The only other place I ve ever seen the  1   1  construct is in BIRT  Reports often use positional parameters and are modified with Javascript to allow all values  So the query   select   from tbl where col       when the user selects     for the parameter being used for col is modified to read   select   from tbl where   col      or  1   1     This allows the new query to be used without fiddling around with the positional parameter details  There s still exactly one such parameter  Any decent DBMS  e g   DB2 z  will optimize that query to basically remove the clause entirely before trying to construct an execution plan  so there s no trade-off

User · Answer

As you said      if you are adding conditions   dynamically you don t have to worry   about stripping the initial AND   that s the only reason could be  you are right

User · Answer

It s also a common practice when people are building the sql query programmatically  it s just easier to start with  where 1 1   and then appending   and customer id  custId  depending if a customer id is provided  So you can always append the next part of the query starting with  and

User · Answer

Yeah  it s typically because it starts out as  where 1   0   to force the statement to fail   It s a more naive way of wrapping it up in a transaction and not committing it at the end  to test your query   This is the preferred method

User · Answer

The 1 1 is ignored by always all rdbms  There is no tradeoff executing a query with WHERE 1 1   Building dynamic WHERE conditions  like ORM frameworks or other do very often  it is easier to append the real where conditions because you avoid checking for prepending an AND to the current condition   stmt     WHERE 1 1   if  v    null       stmt       AND col       v ToString         This is how it looks like without 1 1   var firstCondition   true      if  v    null       if   firstCondition          stmt      AND            else          stmt      WHERE           firstCondition   false          stmt     col       v ToString

[sql] What is the purpose of using WHERE 1=1 in SQL statements?

Examples related to sql

Examples related to conditional-statements

Examples related to where