Sometimes when you look at other people’s SQL code, you’ll see
WHERE 1=1
as part of the code. If you haven’t seen this before, you might be wondering why people do this. After all, 1=1 always evaluates to TRUE, so that apparently makes this line of code redundant. As it turns out, there are two reasons why this is done:
Reason 1: Testing
The first reason is to make it easy to test SQL statements. When a developer is working on building a SQL statement, often there is a lot of trial and error that happens. By having the WHERE 1=1 clause, this makes the testing of the different WHERE conditions easier.
WHERE 1=1
--and gender='F'
and product_id=135209
--and credit_card='Visa'
By having each condition on a different line, it’s easy to comment out different conditions using two dashes to explore the various possibilities.
Reason 2: Programmatic
The second use case for using WHERE 1=1 is when you want to general SQL in a programmatic fashion. For example, you might have a selection box for users to select what category of product they want to view. In SQL, this will be implemented as part of the WHERE clause. If the code already has
WHERE 1=1
then the program will only need to add an additional “AND [new condition]” clause after this, and not have to worry about putting in logic to determine whether this is the first condition that needs to be added (i.e., need to add the WHERE clause) or there is already a WHERE clause and you simply need to add an AND clause.
We’ve talked about two reasons, and we will also mention one supposed reason that actually isn’t true. Some people believe that WHERE 1=1 will speed up the performance of the SQL query. This is not the case, as modern SQL optimizers simply ignore this clause.
Personally I do not put in WHERE 1=1 in my SQL because to me this requires an extra effort and accomplishes no real goal. On the other hand we all have our programming habits, so there are occasions when I see a code that contains WHERE 1=1. When I get this, I simply leave it there for the same reason (there is no real benefit in taking the time to change it).
So next time you see WHERE 1=1, you understand why people use it. Whether you decide to adopt this pattern is entirely up to you.