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)

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – What is Windows Internal Database Edition?
Next Post
SQL SERVER – Three Simple Guidelines for System Maintenance – Notes from the Field #098

Related Posts

No results found.

6 Comments. Leave new

  • I usually use “where primary key is not null”. Would 1=1 perform better?

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

    Reply
    • 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…

      Reply
  • Why not use “Where TRUE”

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

    Reply

Leave a Reply

Menu