SQL Conditions
Common conditional logic statements for SQL
| = | Equal to. Tests for equality |
| <> | Not equal to. Tests for inequality |
| < | Less than. Tests that the value on the left is less than the value on the right |
| <= | Less than or equal to. Tests that the value on the left is less than or equal to the value on the right. |
| > | Greater than. Tests that the value on the left is greater than the value on the right. |
| >= | Greater than or equal to. Tests that the value on the right is greater than or equal to the value on the left |
| BETWEEN | Tests that a value is in the range between two values; the range is inclusive |
| EXISTS | Tests for the existence of rows returned by a subquery |
| IN | Tests to see whether a value is contained within a list of values. |
| IS NULL | Tests to see whetehr a column contains a NULL value. |
| IS NOT NULL | Tests to see whether a column contains a non-NULL value. |
| LIKE | Tests to see whether a value matches a specified pattern |
| NOT | Negates any test |
Extract taken from 'ColdFusion Web Application Contruction Kit' book
That's funny you mention that because dates was exactly the use-case that I was thinking about when considering the non-inclusive one-end :) I guess its just how someone comes down to using it. I find that I most often write my queries like this:
date >= X
AND
date < Y
For example, find "All XYZ that ocurred in a single week". This is inclusive on one end only:
>= Sunday 12:00 AM
< Sunday (week later) 12:00 AM
This is the majority of the date ranges that I use.
Nabbed this off the w3c site :
----------
<i>Note: The BETWEEN operator is treated differently in different databases.
In some databases a person with the LastName of "Hansen" or "Pettersen" will not be listed (BETWEEN only selects fields that are between and excluding the test values).
In other databases a person with the last name of "Hansen" or "Pettersen" will be listed (BETWEEN selects fields that are between and including the test values).
And in other databases a person with the last name of "Hansen" will be listed, but "Pettersen" will not be listed (BETWEEN selects fields between the test values, including the first test value and excluding the last test value).
Therefore: Check how your database treats the BETWEEN operator.</i>
-----------
According to their site, MS SQL uses inclusive, but mySQL only states that the function "Check[s] whether a value is <strong>within</strong> a range of values"
I guess its all down to user preference, and as you stated there is the < and > alternatives :) (I dont think i'll ever use BETWEEN either due to the variation between databases)