Good, Bad or Indifferent: WHERE 1=1

Question

Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up like this:

SELECT 
     C.CompanyName
    ,O.ShippedDate
    ,OD.UnitPrice
    ,P.ProductName
FROM 
               Customers       as C
    INNER JOIN Orders          as O  ON C.CustomerID = O.CustomerID
    INNER JOIN [Order Details] as OD ON O.OrderID    = OD.OrderID
    INNER JOIN Products        as P  ON P.ProductID  = OD.ProductID
Where 1=1
--  AND O.ShippedDate Between '4/1/2008' And '4/30/2008'
    And P.productname = 'TOFU'
Order By C.CompanyName

Someone basically said that 1=1 is generally lazy and bad for performance.

Given that I don't want to "prematurely optimize" - I do want to follow good practices. I've looked at the query plans before, but generally only to find out what indexes I can add (or adjust) to make my queries run faster.

The question then really... does Where 1=1 cause bad things to happen? And if so, how can I tell?

Minor Edit: I've always 'assumed' as well that 1=1 would be optimized out, or at worst be negligible. Never hurts to question a mantra, like "Goto's are Evil" or "Premature Optimization..." or other assumed facts. Wasn't sure if 1=1 AND would realistically affect query plans or not. What about in subqueries? CTE's? Procedures?

I'm not one to optimize, unless needed... but if I'm doing something that is actually "bad", I'd like to minimize the effects or change where applicable.

Answer 1

The SQL Server parser optimizer has a feature called "Constant Folding" that eliminates tautological expressions from the query.
If you look at the execution plan, nowhere in the predicates you will see that expression appear. This implies that constant folding is performed anyway at compile time for this and other reasons and it has no effect on query performance.

See Constant Folding and Expression Evaluation During Cardinality Estimation for more info.

Answer 2

The addition of the redundant predicate can make a difference in SQL Server.

In the execution plans below notice the @1 in the first plan vs the literal 'foo' in the second plan.

This indicates that SQL Server considered the first query for simple parameterisation to promote execution plan reuse - however the comparison of two constants prevents this from happening in the second case.

A list of conditions that prevent simple parameterization (previously known as auto-parameterization) can be found in Appendix A of the Plan Caching Microsoft Technical Papers:

• Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
• Plan Caching in SQL Server 2008
• Plan Caching and Recompilation in SQL Server 2012

simple parameterisation isn't generally something that you should be relying on anyway though. It is far better to explicitly parameterise your queries.

Answer 3

In any modern RDBMS (including Oracle, Microsoft SQL Server and PostgreSQL - I'm sure about these) this will have no effect on performance.

As someone noted, this will impact only query planning phase. Hence difference will be visible only when you run thousands of iterations of a simplistic query which does not return any data, like this one:

SELECT 1 FROM empty_table; -- run this 10 000 times.

SELECT 1 FROM empty_table WHERE 1=1; -- run this 10 000 times and compare.

For me, on PostgreSQL 9.0, this is visible with only 10000 iterations:

filip@srv:~$ pgquerybench.pl -h /var/run/postgresql/ -q "select 1 from never where 1=1" -q "select 1 from never" -i 10000
Iterations: 10000
Query:   select 1 from never where 1=1
Total:   2.952 s
Average: 0.295 ms
Query:   select 1 from never
Total:   2.850 s
Average: 0.285 ms

Answer 4

This can be "problem" for Oracle when you use database parameter cursor_sharing. When this is set to "force" it will modify all SQL statements. All the "constants" in queries will be replaced by bind variables(like 1 => :SYS_0).

This option was introduced to deal with some lazy developers. On the other hand it can also harm other lazy developers. But the risk is not too high. Since 11g it has bind variable peeking feature.