Why or condition works differently with Java and SQL

Question

In Java,

int a = 10, b = 10;

if (a == 10 || b==10) { // first condition (a==10) is true, so it wont check further } 

But in SQL,

 select * from my table where a = 10 or b = 10; --As per my understanding, It should return data only based on a. --But it returns both entries. 

Why is this?

Answer 1

You describe early termination - this means that the second statement is executed only if the answer is not yet known, but it does not change the result (if you do not execute the expression in the second expression).

So a == 10 || b == 10 a == 10 || b == 10 will lead to something where a, or b is equal to 10, or where a and b are equal to 10. Or, more precisely, ...

 a = 10 b = 10 

or

 a = 10 b = 0 

or

 a = 0 b = 10 

If a turns out to be 10, you really don't need to check b - but some languages ​​still do.

Answer 2

This is not a good comparison. However, this does not necessarily work. If you want to add

 if (a == 10 || b==10) { // first condition (a==10) is true, so it wont check further // return a or b } 

It will be returned every time a or b is 10. Almost the same logic underlying the SQL expression.

The problem is how you misinterpret WHERE , it actually returns every record where a or b is 10.

Answer 3

The difference is that in Java we test conditional logic, not aggregation, as SQL does.

Java makes a short circuit, meaning that if the first operand checked is true, it will not execute other operands.

In SQL, WHERE statements look for all conditions a and conditions b independently, since you use or

Answer 4

 --As per my understanding, It should return data only based on a. 

Your understanding is wrong. The request is internally optimized, and you do not know why it first checks. But he checks any condition, and then another, if necessary, so that it is true.

Answer 5

SQL does not shorten logical expressions. When you ask for records WHERE A = 10 OR B = 10 , you ask for both - you are not asking for those in which the first case was satisfied.

If you want to emulate this short circuit selection logic, you can use this instead:

 Select * From Table Where A = 10 Or ( A <> 10 And B = 10 )