Why (a x b) produces a result other than ((a x) and (x b))?

Question

Why (a x b) produces a result other than ((a x) and (x b))?

Navigation

#1 by (9 votes)

6

List all fields of all products whose cost plus 30% is greater than 8 and less than 10.

That was my answer:

SELECT * FROM 'produtos' WHERE (8 < (pcusto*1.3) < 10);

This is the template:

SELECT * FROM 'produtos' WHERE 8 < (pcusto*1.3) AND (pcusto*1.3) < 10;

Although mathematical reasoning is correct, PhpMyAdmin identifies very different results. In the first case it returns more than 1000 records, already in the template response it returns only 60.

What is the primary difference between each line of code?

sql

asked by anonymous 14.09.2018 / 18:00

1 answer

Create an "opaque stripe" on top of a tr Stored Procedure in the Entity Framework without model edmx

score 9 · Accepted Answer

It turns out that SQL will perform something other than what you imagine with the syntax you used:

WHERE a < x < b

This expression could be written as (a < x) < b producing the same result, but leaving a little more explicit what happens. First the first part of the comparison will be evaluated, a < x , and the result of this expression will be compared with the last value.

If x is greater than a , the a < x comparison will return true, true , which will be evaluated as integer 1, thus comparing 1 < b . In your case, b is 10, then 1 < b is true, thus returning all records that have pcusto * 1.3 greater than 8.

If x is less than a , comparison a < x will return false, false , which will be evaluated as integer 0, thus comparing 0 < b . In your case, b is 10, then 1 < b is true, thus returning all records that have pcusto * 1.3 less than 8.

That is, basically your 8 < pcusto*1.3 < 10 parison will always be true, and all the records in the table are returned in the query.

When you separate into two independent comparisons, you will logically evaluate the result of both, obtaining the expected result.