Dummy where clauses effects on performance

Question

What is the effect of having WHERE 1=1 if you write this request in a script (pseudocode) :

sql = "SELECT f1,f2,f3 FROM t
       WHERE 1=1" ++ restOfTheClause

Where restOfTheClause can be a list of field-names/operators/values concatenated with a AND :

restOfTheClause = [('f4','>',5), ('f5','IN(1,2,3)'), ('f10','=',1)].map(writeWherePart).join(' AND ')

Writing WHERE 1=1 allows to write simpler code because you don't have to check if your restOfTheClause is empty or not, etc.

What is the effect on performance with this clause ? What is the difference between having WHERE 1=1 and having no where clause at all ?

I work on Oracle since a couple of months and i found i have to put WHERE 1=1 but other RDBMS accept WHERE true. As = is an operator, is there any performance differences between oracle and other RDBMS in using dummy clauses ?

Thank you

Answer 1

SQL Server and oracle are sophisticated enough to completely delete this clause. It is deleted as a simplification step. It has no perf impact whatsoever. I don't know about other RDBMS'es.

Answer 2

The optimiser is smart enough to realize that 1=1 needs to be computed only once, so the performance impact is negligible.