The BETWEEN keyword provides the ability to compare a value to a range of values. For instance, Example 4-41 shows a SELECT statement which looks for author names between 200 and 250:
Example 4-41. Using BETWEEN
SELECT lastname, firstname FROM author WHERE a_id BETWEEN 200 AND 250;
The output is:
----------+------------ Williams | Margary Alcott | Luoisa May Williams | Margary Alcott | Luoisa May (4 rows)
You can return the exact same output using the less-than operator ( < ) in conjunction with the greater-than ( > ) operator, as you can see in Example 4-42 .
Example 4-42. Operator Equivalents to BETWEEN
SELECT lastname, firstname FROM author WHERE a_id >= 200 AND a_id <= 250;
Using the BETWEEN keyword and a combination of less-than/greater-than operators will output the same result, because when PostgreSQL interprets BETWEEN , it converts it into the less-than/greater-than form. Both forms are valid, and as far as PostgreSQL is concerned, it's really just a matter of user preference.
If a table has NULL values in it, comparison operators can be used to display NULL valued rows:
Example 4-43. Comparisons Using IS NULL
booktown=# SELECT last_name, first_name booktown-# FROM author booktown-# WHERE first_name IS NULL;
Examining Example 4-43 and Example 4-44 , you might think that the syntax in the two statements provided are identical.
Example 4-44. Comparisons Equal to NULL
booktown=# SELECT last_name, first_name booktown-# FROM authors booktown-# WHERE first_name = NULL;
When comparing a value to NULL in an expression, it is a good idea to be in the habit of using the IS NULL and IS NOT NULL keyword operators. You should avoid using the expression = NULL , because it prompts the parser to look for a value that is literally equal to NULL . If NULL is an unknown value, this kind of comparison is ambiguous.
PostgreSQL provides a translation from = NULL to IS NULL , and likewise for the != NULL operation. This is provided only for compatibility with client applications (such as Microsoft Access), but be aware that this functionality may be discontinued in the future. The appropriate choice is to use IS NULL or IS NOT NULL .