Difference between not equal and greater than for a number

Question

I have a query:

select *
from randomtable
where randomnumber <> 0


The column "random_number" will never be a negative number.

So, if I write the query as:

select *
from randomtable
where randomnumber > 0


Is there any major difference?

Answer 1

If it will never be less than zero, then NO

Answer 2

What does 'greater than' mean? It means 'not equal' AND 'not less than'. In this case, if the number cannot be less than zero, 'greater than' is equivalent to 'not equal'.

Answer 3

No, as it will always be above 0.

Answer 4

No, there is no difference at all (in your specific situation). All numeric comparisons take the same time.

What's done at the lowest level is that zero is subtracted from randomnumber, and then the result is examined. The > operator looks for a positive non-zero result while the <> operator looks for a non-zero result. Those comparisons are trivial, and take the same amount of time to perform.

Answer 5

The important thing is to determine how you know that random_number will never be a negative number. Is there a constraint that guarantees it? If not, what do you want your code to do if a bug somewhere else causes it to be negative?

Answer 6

The result set should never be different. The query path might be as the second might choose an index range scan starting at randomnumber=0 and looking for the records in sequence. As such the order of the results may differ.

If the order of the results is important, then put in an ORDER BY