Does SQL Server optimise constant expressions?

问题:

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.

回答1:

SQL Server:

First example:

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

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 :-)

Note: I used SQL Server 2012 Developer Edition.

回答2:

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