One of the blog followers asked this question “The code written by my colleague has WHERE 1=1. What does it mean?”
Well. You have seen people using it when they dynamically construct WHERE condition based on the input values.
Let us create this dataset
CREATE TABLE #t (emp_name VARCHAR(100), experience INT, specialist VARCHAR(100))
INSERT INTO #t
SELECT 'Pinal',12,'SQL' UNION ALL
SELECT 'John',10,'JAVA' UNION ALL
SELECT 'Sudhan',3,'SQL'
Suppose you want to use two parameters and pass values for them in such a way that if the value is null, you need to skip the comparison
Check the following code
DECLARE @experience INT, @specialist VARCHAR(100), @sql VARCHAR(100)
SET @sql=' where 1=1'
SET @sql = @sql+CASE WHEN @experience IS NULL THEN '' ELSE ' and experience='''+CAST(@experience AS VARCHAR(100))+'''' END
SET @sql = @sql+CASE WHEN @specialist IS NULL THEN '' ELSE ' and specialist='''+CAST(@specialist AS VARCHAR(100))+'''' END
SELECT @sql AS value
value --------------- where 1=1
Because no values are passed the column valu comparions are skipped and you have 1=1 in the WHERE condition
DECLARE @experience INT, @specialist VARCHAR(100), @sql VARCHAR(100)
SET @sql=' where 1=1'
SET @specialist='SQL'
SET @sql = @sql+CASE WHEN @experience IS NULL THEN '' ELSE ' and experience='''+CAST(@experience AS VARCHAR(100))+'''' END
SET @sql = @sql+CASE WHEN @specialist IS NULL THEN '' ELSE ' and specialist='''+CAST(@specialist AS VARCHAR(100))+'''' END
SELECT @sql AS value
If you execute the above , the result is
value ------------------------------- where 1=1 and specialist='SQL'
It is because the value for column specialist is passed and it is included in the WHERE clause. This way to build WHERE clause dynamically you may need to use 1=1 as the first condition.
Now you can execute the statement using
EXEC(' select * from #t'+@sql)
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I usually use “where primary key is not null”. Would 1=1 perform better?
The statement 1=1 is always true and can be given in any sql where-clause. You could also write 2=2.
You might want to test a query by outquoting statements in your where-clause. However, if you outquote the where-statement, your query won’t work. Just for ease of testing and coding, I use the 1=1 statement.
ex:
select …
from …
where 1=1
–and a = b –outquoted to test the query.
and c = d
I only do this in Dev. When promoting to Test and other environments, the 1=1 statement is removed.
You can leave the ‘1 = 1’ without any problem as no sensible query-optimizer will evaluate it at runtime. It is a constant (TRUE) and when it is on its own or is followed by ‘AND’ it will be ignoered [ TRUE AND Expr == Expr] or when it is followed by ‘OR’ the expression following the ‘OR’ will be ignored [TRUE OR Expr == TRUE).
I often use this construct when (like Pinal’s example) i construct a WHERE-clause where each part starts off with ‘AND …’. If no part applies, the (1=1) is still there…
Why not use “Where TRUE”
I have a colleague who creates sql scripts with multiple selects typically for diagnostic purposes. They usually start as low record count general selects and get more detailed further on (Some have hard-coded record ids for example that are obtained from the general selects). They all have “where 1=1”, or “where 1=11” to either turn them on or off. When F5 is pressed, only the ones with 1=1 return anything. To toggle them on or off, it’s quicker change 1 to 11 or vice versa than it is to comment/uncomment them.