SQL SERVER – What Does WHERE 1=1 Mean in the Code?

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)

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.