Why would a sql query have "where 1 = 1"

Question

I was going through a few queries I am maintaining, and a programmer had put in the queries "where 1=1" to me that always seems to evaluate to true.

Are there benefits to this?

Duplicate: Why would someone use WHERE 1=1 AND in a SQL clause?

That question isn't an answer to this question.

Where-clause:

select * from table where 1=1 and sStatus not in ('status1','status2','status3')

No programming or if statements to push an and in there. A straight query.

If you could un-close this, I would like to know whether there is a purpose so that I may rewrite and remove the 1=1 if it is unnecessary.

Answer 1

Was it dynamic queries? Sometimes that's helpful when building dynamic queries based on parameters that are optional.

Answer 2

I've seen two reasons for this, when you always want a true result, or when there is going to be an arbitrary number of "and condition = value" appended to the statement

Answer 3

I use this for dynamic where clauses when I'm doing some lazy programming and don't want to always check if the clause is empty to determine if I now need an "AND" between my dynamic clauses.

Answer 4

This really only makes sense in dynamic queries. If you are adding parameters in a loop instead of having to check if there is a WHERE already you can just append AND Column = Value every time.

Answer 5

If you are dynamically building a where clause, you can be a bit lazy and assume that every clause you add can be prefixed with "AND", e.g.

$str="select foo from bar where 1=1";

if ($filter1)
{
    $str.=" and col1='frobozz'";
}

Answer 6

That is very interesting... The WHERE clause contains nothing but 1=1? I have seen this frequently in SQL injection attempts in which the WHERE clause is set to xyz="something" OR 1=1; as a means to always return a list of results.

Can you tell us more about what is going on with this query so we might be able to answer the question better?

• Nicholas

Answer 7

If you automatically want to add restrictions to your query, it makes your live easier:

string sql = "SELECT * FROM table WHERE 1=1";

if (someflag) {
  sql += " AND valid = 1";
}

if (someotherflag) {
  sql += " AND special = 1";
}

execute(sql);

Without "WHERE 1 = 1" you would in each case have to check if it's the first restriction you add (and then use "WHERE ...") or if you already added some other restriction before (and then add "AND ...").