CODEWITHSUNDEEP
sqlsql-server
SomeGuy
SomeGuy

Reputation: 495

Can the order of individual operands affect whether a SQL expression is sargable?

A colleague of mine who is generally well-versed in SQL told me that the order of operands in a > or = expression could determine whether or not the expression was sargable. In particular, with a query whose case statement included:

CASE
when (select count(i.id)
            from inventory i
            inner join orders o on o.idinventory = i.idInventory
            where o.idOrder = @order) > 1 THEN 2
            ELSE 1

and was told to reverse the order of the operands to the equivalent 

CASE
when 1 < (select count(i.id)
            from inventory i
            inner join orders o on o.idinventory = i.idInventory
            where o.idOrder = @order) THEN 2
            ELSE 1

for sargability concerns. I found no difference in query plans, though ultimately I made the change for the sake of sticking to team coding standards. Is what my co-worker said true in some cases? Does the order of operands in an expression have potential impact on its execution time? This doesn't mesh with how I understand sargability to work.

Upvotes: 4

Views: 128

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659017

For Postgres, the answer is definitely: "No." ( was added later.)

The query planner can flip around left and right operands of an operator as long as a COMMUTATOR is defined, which is the case for all instance of < and >. (Operators are actually defined by the operator itself and their accepted operands.) And the query planner will do so to make an expression "sargable". Related answer with detailed explanation:

It's different for other operators without COMMUTATOR. Example for ~~ (LIKE):

Upvotes: 1

Pavel Smirnov
Pavel Smirnov

Reputation: 4809

If you're talking about the most popular modern databases like Microsoft SQL, Oracle, Postgres, MySql, Teradata, the answer is definitely NO.


What is a SARGable query?

A SARGable query is the one that strive to narrow the number of rows a database has to process in order to return you the expected result. What I mean, for example:

Consider this query:

select * from table where column1 <> 'some_value';

Obviously, using an index in this case is useless, because a database most certainly would have to look through all rows in a table to give you expected rows.

But what if we change the operator?

select * from table where column1 = 'some_value';

In this case an index can give good performance and return expected rows almost in a flash.

SARGable operators are: =, <, >, <= ,>=, LIKE (without %), BETWEEN
Non-SARGable operators are: <>, IN, OR

Now, back to your case.
Your problem is simple. You have X and you have Y. X > Y or Y < X - in both cases you have to determine the values of both variables, so this switching gives you nothing.

P.S. Of course, I concede, there could be databases with very poor optimizers where this kind of swithing could play role. But, as I said before, in modern databases you should not worry about it.

Upvotes: 1

Related Questions