0

In SQL Server if I have an AND statement with 2 conditions case when x>0 and y>5 then do something.

If first x>0 condition if false, is it going to execute the second condition or it would stop at the first one since for "and" statement both conditions need to be true in order for the whole statement to be true.

select case when (ord_qty > 5 and shipped_qty > 1) then 'partially_shipped' else 'in_process' end as order_status
from order
Dale K
  • 25,246
  • 15
  • 42
  • 71
rma
  • 51
  • 1
  • 3
  • Can you post your code? – LSM07 Mar 05 '21 at 17:40
  • https://stackoverflow.com/questions/60691186/if-the-first-condition-is-false-then-the-second-condition-is-checked-in-sql-serv – Raseena Abdul Mar 05 '21 at 17:43
  • Does this answer your question? [If the first condition is FALSE then the second condition is checked in SQL Server?](https://stackoverflow.com/questions/60691186/if-the-first-condition-is-false-then-the-second-condition-is-checked-in-sql-serv) – Dale K Mar 05 '21 at 22:39

1 Answers1

0

SQL statements describe the results produced. They do not describe the methods for achieving those results.

SQL has many different ways that it can approach such a query. Even if the engine is doing a full table scan and evaluating each condition, then it can evaluate the conditions in whatever order it things is best. In general, SQL does do short-circuiting, meaning that if the first evaluated condition is "false" or NULL, then there is no reason to evaluate the second.

SQL Server is actually executing something called a directed acyclic graph (DAG). The compiler and optimizer are responsible for transforming the query into a DAG. And those components determine how the query gets executed.

Note that for your example, SQL might be using indexes or partitions to resolve one or more of conditions -- and that looks quite different from a filtering comparison on each row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786