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:

               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.

  • 2
    No, it wouldn't. Apart from a few microseconds for the optimizer to remove the redundant condition. You better focus on your date literals not being ambiguous. – ypercubeᵀᴹ Feb 1 '13 at 0:01
  • As @ypercube said, it makes no difference. The query optimiser would have to be a piece of **** for such a thing to make any difference ;) – Philᵀᴹ Feb 1 '13 at 0:03
  • 4
    Don't believe everything you read on reddit. Please. – Aaron Bertrand Feb 1 '13 at 0:10
  • 1
    @AaronBertrand I take everything with a grain of salt, until I've experienced it first hand. I'll still take a question that sounds plausible and see if there is any truth to it, especially when it affects my day to day job. – WernerCD Feb 1 '13 at 0:16
  • 4
    There's grains of salt, then there's the salt content of an entire ocean dumped on top of your office building :P – Philᵀᴹ Feb 1 '13 at 0:20

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.

  • It's probably compiled away because this is a known pattern to do concatenation of fields. – jcolebrand Feb 1 '13 at 18:06
  • No, it is compiled away because it's tautological. It would work in the same way with 2736 = 2736, which is not just as usual as 1 = 1. The same applies to contradictions. In that case the feature is called "Contradiction detection". – spaghettidba Feb 1 '13 at 18:08
  • Which part of "known pattern" meant "has to be 1=1"? – jcolebrand Feb 1 '13 at 18:18

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.

enter image description here

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:

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


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

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.

  • Can you clarify what "Since 11g it has bind variable peeking feature." means? – ypercubeᵀᴹ Feb 1 '13 at 14:26
  • @ypercube "Bind variable peeking" means that the optimizer will observe the actual values of bind variables and use data statistics to re-evaluate and possibly regenerate the query execution plan. I doubt peeking will have any effect on the construct that is being discussed though, as it does not depend on data statistics. – mustaccio Feb 4 '14 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.