In SQL, does a BETWEEN statement have to be of the form BETWEEN lowest AND highest?

Question

Hi,

For example, if I did:

SELECT * FROM Users WHERE UserId BETWEEN 100 AND 1

what would the results be?

Edit: Sorry, you're right, I should have specified. I didn't want to know the exact number of rows that would return, I just wanted to know if it would return rows that were between 1 and 100 or if it would return rows from min(UserId) to 1 and 100 to max(UserId).

Answer 1

This probably depends on your RDBMS, but why not just try it out? On MySQL, I get no rows from a BETWEEN 100 AND 1 query whereas I do get rows from asking for BETWEEN 1 and 100.

Answer 2

a BETWEEN b AND c

is shorthand for

b <= a and a <= c

So BETWEEN 100 and 1 won't match anything.