Why use WHERE 1 = 1 in an SQL query?

Question

During the maintenance of a legacy system I found the following Procedure:

DECLARE @sql AS varchar(MAX);
DECLARE @param as varchar(50);

SET @sql = 'SELECT * FROM Destinatario where 1 = 1';

IF(@param IS NOT NULL)
    SET @sql = (@sql + ' AND Nome = ''' + @param + '''');
EXEC(@sql)

The first thing that caught my attention was the condition Where 1 = 1

• Why the programmer who wrote this project used this condition?

Answer 1

This condition in principle does not appear to be right, because removing it gives in the same.

However, when Where conditions are generated dynamically, and the programmer adds them with AND inside ifs, because these depend on other factors of the program that generates SQL, it is clear its intention:

If there is no additional condition, the query remains so ...

SELECT * FROM Destinatario where 1 = 1

... but in the case, if the condition is true right after your example, it can stay:

SELECT * FROM Destinatario where 1 = 1 AND Nome = "Roberto"

Note that if there was no WHERE 1=1, it would stay that way, and it wouldn’t work:

SELECT * FROM Destinatario AND Nome = "Roberto" 
  -- temos um erro aqui --^

For one condition AND only, the author could have put the WHERE within the condition, but if there are various conditions, the WHERE 1=1 would allow several separate conditions, mounted by ifs different:

SELECT * FROM Destinatario where 1 = 1
SELECT * FROM Destinatario where 1 = 1 AND Nome = "Roberto"
SELECT * FROM Destinatario where 1 = 1 AND Cidade = "Santos"
SELECT * FROM Destinatario where 1 = 1 AND Nome = "Roberto" AND Cidade = "Santos"

Thus the AND Nome could be in a if and the AND Cidade in another, and all would function independently of having to know if there is already a if previous in the code generating the SQL.

As mentioned by @bfavaretto, Cakephp, for example, uses this technique.

In particular, I find it better to use cleaner ways than how it was used, such as concatenating with AND only when it comes to the second condition onwards, but each has its own criteria, and at each moment a particular technique may be better than another.

Leaving this specific example of use, it may even happen that someone uses a WHERE "neutral" when you want to test some condition during code development, eliminating the original condition (equivalent to putting WHERE true, which would also serve for the initial explanation), but this is already another story.

In our specific case, the IF this row confirms the initial hypothesis of the optional parameter with AND:

IF(@param IS NOT NULL) -- <== Aqui 
    SET @sql = (@sql + ' AND Nome = ''' + @param + '''');

Answer 2

There are other interesting points to be raised for this solution with WHERE 1 = 1.

Code clarity

One advantage of this technique is that the code ends up staying cleaner, so there need not be control of the presence of the WHERE vs AND/OR, keeping independent the order of the filters that will enter the query and if any filter will enter the query.

Impact on performance

There is a recurring doubt regarding the impact of performance in the database of the presence of WHERE 1 = 1, whether there are other filters or not. What can I say to most databases does not suffer any performance impact when using this technique.

Doing an impact analysis on the Sql Server execution plan, with 10,000 records in a table, no change was noticed between the plan containing WHERE 1 = 1 and the plan without the same.

Other option

Some databases also support WHERE 1, although this is not the recommended option because it is not part of the ANSI standard.