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.

Answer 1

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