0

If I do something like this in SQL Server...

select *
from mytable
where 1=1

...does the 1=1 condition get optimised out, or is it actually evaluated for each row?

I'd also be interested in knowing how other DBMSes behave in this regard, particularly MySQL.

Gigi
  • 28,163
  • 29
  • 106
  • 188
  • possible duplicate of [T-SQL 1=1 Performance Hit](http://stackoverflow.com/questions/1049512/t-sql-1-1-performance-hit) – David Manheim Mar 26 '14 at 18:46

2 Answers2

2

SQL Server:

First example:

1=1 predicate is automatically removed by SQL Server Query Optimizer because it's always true: enter image description here

Second example:

Because 1=2 predicate is always false, the execution plan don't includes a data access operator (Table / Index Scan / Seek) to read data from dbo.Customer table. Instead, the execution plan includes an Constant Scan operator which will return the list of columns from dbo.Customer table without any row. This is contradiction detection in action :-)

enter image description here

Note: I used SQL Server 2012 Developer Edition.

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
0

Test it with EXPLAIN PLAN for MySQL and SET SHOWPLAN_ALL ON for SQL Server

Mikpa
  • 1,912
  • 15
  • 20