patternsqlMinor
MySQL Query to check at least one field is greater than zero
Viewed 0 times
fieldgreaterquerythanonemysqlleastcheckzero
Problem
I want to retrieve the row if any of the 3 integer field in a row is greater than zero. All the field has default value of zero.
Which of the following query is better?
While checking the execution time both the query gives same numbers and the result data of the query is exactly the same.
Which of the following query is better?
-- Query 1
SELECT * FROM my_table WHERE number_A > 0 OR number_B > 0 OR number_C > 0
-- Query 2
SELECT * FROM my_table WHERE (number_A + number_B + number_C) > 0While checking the execution time both the query gives same numbers and the result data of the query is exactly the same.
Solution
In addition to the other differences between the two statements
Could cause you problems if any of those three are negative. That may not be relevant in your case, but if one of these is negative the total might fail to exceed zero even if another of the values does exceed zero.
WHERE (number_A + number_B + number_C) > 0Could cause you problems if any of those three are negative. That may not be relevant in your case, but if one of these is negative the total might fail to exceed zero even if another of the values does exceed zero.
Code Snippets
WHERE (number_A + number_B + number_C) > 0Context
StackExchange Code Review Q#158178, answer score: 4
Revisions (0)
No revisions yet.