Why would someone use WHERE 1=1 AND i

Question

Why would someone use WHERE 1=1 AND <conditions> in a SQL clause (Either SQL obtained through concatenated strings, either view definition)

I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.

If there is injection WHERE 1 = 1 AND injected OR 1=1 would have the same result as injected OR 1=1.

Later edit: What about the usage in a view definition?

---

Thank you for your answers.

Still, I don't understand why would someone use this construction for defining a view, or use it inside a stored procedure.

Take this for example:

CREATE VIEW vTest AS
SELECT FROM Table WHERE 1=1 AND table.Field=Value

Answer 1

Using a predicate like 1=1 is a normal hint sometimes used to force the access plan to use or not use an index scan. The reason why this is used is when you are using a multi-nested joined query with many predicates in the where clause where sometimes even using all of the indexes causes the access plan to read each table - a full table scan. This is just 1 of many hints used by DBAs to trick a dbms into using a more efficient path. Just don't throw one in; you need a dba to analyze the query since it doesn't always work.

Answer 2

Seems like a lazy way to always know that your WHERE clause is already defined and allow you to keep adding conditions without having to check if it is the first one.

Answer 3

Just adding a example code to Greg's answer:

dim sqlstmt as new StringBuilder
sqlstmt.add("SELECT * FROM Products")
sqlstmt.add(" WHERE 1=1") 

''// From now on you don't have to worry if you must 
''// append AND or WHERE because you know the WHERE is there
If ProductCategoryID <> 0 then
  sqlstmt.AppendFormat(" AND ProductCategoryID = {0}", trim(ProductCategoryID))
end if
If MinimunPrice > 0 then
  sqlstmt.AppendFormat(" AND Price >= {0}", trim(MinimunPrice))
end if

Answer 4

Indirectly Relevant: when 1=2 is used:

CREATE TABLE New_table_name as select * FROM Old_table_name WHERE 1 = 2;

this will create a new table with same schema as old table. (Very handy if you want to load some data for compares)

Answer 5

Here's a closely related example: using a SQL MERGE statement to update the target tabled using all values from the source table where there is no common attribute on which to join on e.g.

MERGE INTO Circles
   USING 
      (
        SELECT pi
         FROM Constants
      ) AS SourceTable
   ON 1 = 1
WHEN MATCHED THEN 
  UPDATE
     SET circumference = 2 * SourceTable.pi * radius;

Answer 6

Why would someone use WHERE 1=1 AND <proper conditions>

I've seen homespun frameworks do stuff like this (blush), as this allows lazy parsing practices to be applied to both the WHERE and AND Sql keywords.

For example (I'm using C# as an example here), consider the conditional parsing of the following predicates in a Sql query string builder:

var sqlQuery = "SELECT * FROM FOOS WHERE 1 = 1"
if (shouldFilterForBars)
{
    sqlQuery = sqlQuery + " AND Bars > 3";
}
if (shouldFilterForBaz)
{
    sqlQuery = sqlQuery + " AND Baz < 12";
}

The "benefit" of WHERE 1 = 1 means that no special code is needed:

• For AND - whether zero, one or both predicates (Bars and Baz's) should be applied, which would determine whether the first AND is required. Since we already have at least one predicate with the 1 = 1, it means AND is always OK.
• For no predicates at all - In the case where there are ZERO predicates, then the WHERE must be dropped. But again, we can be lazy, because we are again guarantee of at least one predicate.

This is obviously a bad idea and would recommend using an established data access framework or ORM for parsing optional and conditional predicates in this way.