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

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

Its also a common practice when people are building the sql query programmatically, its 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 ….

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());
}

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

People use it because theyre 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 dont have to figure out.

Not that theres anything wrong with being inherently lazy. Ive seen doubly-linked lists where an empty list consists of two sentinel nodes and you start processing at the first->next up until last->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 werent able to delete first or last. Two nodes were wasted but the code was simpler and (ever so slightly) faster.

The only other place Ive 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. Theres 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 theres no trade-off.