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; 

NULL Equivalence

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 .