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

Question

Possible Duplicates:
Why would a sql query have “where 1 = 1”
Why would someone use WHERE 1=1 AND <conditions> 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` = '[email protected]' LIMIT 1; 

Is there any reason for adding that extra condition?

Answer 1

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 ...'.

Answer 2

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