88

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?

Improve this question
1

6 Answers 6

Reset to default
77

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

56

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

5 Comments

Actually, I tend to do something like: cmd = "select ..."; sep = " where "; foreach (cond) { cmd += sep + cond; sep = " and "; }
Which looks a little cleaner.
Good idea! I keep it in mind for further programming.
while this explains perfectly why my collogues are doing this, i keep wondering if this style of coding is good or not
WHERE 1=1 is not "ignored" by database systems - 1=1 always evaluates to TRUE so this is the syntactic equivalent of WHERE TRUE. The database always evaluates all WHERE clause conditions, adding WHERE 1=1 may make writing an adhoc query slightly easier but including this in production systems is just laziness.
32

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

Improve this answer

Comments

20

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

Improve this answer

1 Comment

There can be more than one answer; and in this case the OP was specific that it wasn't just for building dynamic queries (and honestly, if you're building dynamic queries in these days of ORMs, take a good hard look at yourself).
3

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.

Improve this answer

Comments

1

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

Improve this answer

2 Comments

Only on the crappiest of DBMS'.
All the answeres in the question you linked to said 1=1 has no impact.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.