1

My question is:- Let's say we have conditions like below (ConditionA OR ConditionB or ConditionC).If my conditionA becomes true will other conditions be checked or will i skip those.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
CodeOfLife
  • 295
  • 2
  • 11

2 Answers2

0

it will skip the other ones; once One of the Conditions are met!

yumi S.
  • 27
  • 7
  • 4
    That's not true, there is no guarantee in SQL that this happens. –  Feb 11 '19 at 10:47
  • @a_horse_with_no_name i didn't understand.What did you mean by that??Can you please elaborate. – CodeOfLife Feb 11 '19 at 12:49
  • @Amrendra: It's up to the DBMS which condition to check first. If you write `where 1 = 1 or 1 / 0 = 1` this can lead to a division by zero error in spite of the first condition (`1 = 1`) always being met. The two where clauses `where or ` and `where or ` are considered equivalent. – Thorsten Kettner Feb 11 '19 at 15:22
0

--UPDATE

OK - so I missed the point (see below). in TSQL the clause is evaluated in order, but once a row is considered a match , the next OR is not evaluated.

So select * from information_schema.columns where 1=1 or 1/0=10 will not fail as 1 will always equal one and the divide by zero is never carried out.

On the other hand 

select * from information_schema.columns
where COLUMN_NAME like 'keyname_%' or 1/0=10

will succeed if all column_names start with keyname but error if any columns don't start with keyname as the second criteria gets evaluated.

--OLD ANSWER Sometimes if you say logic out loud it becomes obvious...

So, if "Food = Fish or Food = Meat or Food = Veg then cook food". It's obvious that if food is meat, fish or veg then you cook it.

The bit that usually catches people out is where you also want an AND So. if "Food = Fish or Food = Meat or Food = Veg and Oven = working then cook food ". This will try to cook food if the oven is working, regardless of whether you have food.

if "(Food = Fish or Food = Meat or Food = Veg) and Oven = working then cook food" on the other hand says that you must have Fish, meat or veg AND the oven must be working. So by grouping the OR in a parenthesis we separate it from the AND.

iainc
  • 862
  • 6
  • 20
  • Seems you have misunderstood the question. The question is: with multiple `OR` and the first criteria already matching, will the other criteria be checked? Typical example: `where col = 0 or 100 / col > col2` - can this lead to a division by zero exception or not? – Thorsten Kettner Feb 11 '19 at 10:54
  • In SQL Server then all criteria are evaluated in order but once a row has been marked as a match subsequent criteria are not considered. So for example select table_name from information_schema.columns where tablename like 'a%' OR 1/0 will fail if all tables don't start with the letter a, but succeed if they do. – iainc Feb 11 '19 at 15:13
  • But as can be seen from other answers, that `where 1=1 or 1/0=10` will never fail may be the case in your version of SQL Server, but it is in no way guaranteed, neither by the SQL standard nor by Microsoft for SQL Server. – Thorsten Kettner Feb 11 '19 at 15:19