All geek questions in one place

Several mysql WHERE between points

MySql programmer novice thanks for your patience.

Im trying to keep track of the identifier number in a table where 3 different conditions are met, but this is what Iv received, however the query dispenser returns any results when the table clearly shows the matches. Thoughts?

SELECT * FROM `table` WHERE `x` BETWEEN 80 AND 20 AND `y` BETWEEN 120 AND 20 AND `z` BETWEEN 40 AND 10 LIMIT 0 , 30

Is it theoretically possible to think that this should work?

+4
source share
3 answers

Close but no cigar. :)

 SELECT * FROM table WHERE (x BETWEEN 20 AND 80) AND (y BETWEEN 20 AND 120) AND (z BETWEEN 10 AND 40) LIMIT 0 , 30

To explain, SQL servers usually evaluate x BETWEEN val1 AND val2 in the same way as x >= val1 AND x <= val2 . The way your initial request was written, the first condition would be x >= 120 AND x <= 20) , which was clearly not what you planned.

The parentheses around the various conditions make sure that each of them is fully evaluated before considering I. This plays most of the time in SQL, and even when it is not recommended to use them, so that your intentions are clear after 6 months, when you (or anyone else) will have to search again for the request.

+7
source 
 SELECT * FROM table WHERE (x BETWEEN 20 AND 80) AND (y BETWEEN 20 AND 120) AND (z BETWEEN 10 AND 40) LIMIT 0 , 30
+2
source

I think the range should be the other way around:

 SELECT * FROM table WHERE x BETWEEN 20 AND 80 AND y BETWEEN 20 AND 120 AND z BETWEEN 10 AND 40 LIMIT 0 , 30
+1
source

Source: https://habr.com/ru/post/1343273/

More articles:


All Articles