1

if we have multiple AND conditions in where clause of a SQL query and if first condition found false for a records then will it goes to check for another AND condition or not.

in programming we use && operator to check the multiple condition where if first condition found false then it does't goes for another condition, So my query is is it also applies in SQL conditions?

Example:

Select * from tablename where column is not null and column > 0 and column <> 1
jarlh
  • 42,561
  • 8
  • 45
  • 63
Praveen Rawat
  • 724
  • 2
  • 12
  • 29
  • why my answer is downvote? is it false? – CompEng Oct 27 '16 at 07:23
  • 1
    @ersin it does not answer the question – Bee157 Oct 27 '16 at 07:24
  • 1
    @Bee157 exactly it is the answer – CompEng Oct 27 '16 at 07:25
  • i didnt downvote your answer and yes this it not what i am asking above, i am upvoting your answer. – Praveen Rawat Oct 27 '16 at 07:26
  • 5
    You say "in programming..." as if a) writing SQL *isn't* programming (it is) and b) Short-circuit evaluation is *universal* to all other prgramming languages (it isn't) – Damien_The_Unbeliever Oct 27 '16 at 07:26
  • 1
    multiple where statements are optimized, and during execution MySQL stops when 1 is found false. I'm looking for a reference. – Bee157 Oct 27 '16 at 07:26
  • 1
    @PraveenRawat you sayed : **Does second conditions check, if first condition found false in multiple AND-Conditions ** so it must be – CompEng Oct 27 '16 at 07:27
  • 4
    See http://stackoverflow.com/questions/11436469/does-order-of-where-clauses-matter-in-sql Simply put query optimizer is free to check conditions in any order. It will stop checking other conditions when it finds first FALSE, but the order of evaluation is unpredictable – Serg Oct 27 '16 at 07:32
  • 1
    @Bee157, but you don't know in which order the conditions are evaluated. – jarlh Oct 27 '16 at 07:36
  • This depends highly on the DBMS used. Detecting conditions that can never true is the job of the query optimizer. I would say most modern optimizers would catch the situation that the condition `column is not null` is redundant in your query. But in general SQL usually doesn't short-circuit conditions. –  Oct 27 '16 at 08:28
  • The real question is: why do you care? –  Oct 27 '16 at 08:33

2 Answers2

4

SQL is declarative. Not procedural.

The optimiser may use short circuit evaluation or may not.

It also may convert that to something different. If column is integer type all three conditions collapse to 

WHERE column > 1

Similarly the optimiser might find some conditions are guaranteed to be true or false by check constraints or other predicates in the query and don't need evaluating at all. Or use an access path (such as filtered index) that guarantees all rows match a part of the predicate so no run time evaluation is needed.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
-3

It will looks like this

SELECT * FROM tablename 

WHERE (column != null AND column  <> 1) AND (column   > 0)
Shah Hassan
  • 155
  • 7