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

Question

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.

(1) Tag the question with the database you are using. (2) I would be really surprised in any database. — Gordon Linoff, Mar 25 '19 at 17:51
In general, I would say "no", the order does not affect the query, since SQL by definition is a declarative language. Well... unless the SQL query optimizer is subpar. MySQL, for example, gets confused sometimes, and produces quite different plans for seemingly identical queries. — The Impaler, Mar 25 '19 at 18:04

Erwin Brandstetter · Accepted Answer · 2019-03-25T18:35:28.007

For Postgres, the answer is definitely: "No." (sql-server 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:

Can PostgreSQL index array columns?

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

LATERAL JOIN not using trigram index

score 1 · Answer 2 · answered Mar 25 '19 at 18:28

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.

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

2 Answers2