The WHERE Clause Operators
The first WHERE clause we looked at tests for equality—determining if a column contains a specific value. SQL supports a whole range of conditional operators as listed in Table 4.1.
Caution
Operator Compatibility Some of the operators listed in Table 4.1 are redundant (for example, <> is the same as !=. !< (not less than) accomplishes the same effect as >= (greater than or equal to). Not all of these operators are supported by all DBMSs. Refer to your DBMS documentation to determine exactly what it supports.
Checking Against a Single Value
We have already seen an example of testing for equality. Let's take a look at a few examples to demonstrate the use of other operators.
This first example lists all products that cost less than $10:
SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;
prod_name prod_price ------------------- ---------- Fish bean bag toy 3.4900 Bird bean bag toy 3.4900 Rabbit bean bag toy 3.4900 8 inch teddy bear 5.9900 12 inch teddy bear 8.9900 Raggedy Ann 4.9900
Checking For Nonmatches
This next example lists all products not made by vendor DLL01:
SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01';
vend_id prod_name ---------- ------------------ BRS01 8 inch teddy bear BRS01 12 inch teddy bear BRS01 18 inch teddy bear
Tip
When to Use Quotes If you look closely at the conditions used in the above WHERE clauses, you will notice that some values are enclosed within single quotes, and others are not. The single quotes are used to delimit a string. If you are comparing a value against a column that is a string datatype, the delimiting quotes are required. Quotes are not used to delimit values used with numeric columns.
Checking for a Range of Values
To check for a range of values, you can use the BETWEEN operator. Its syntax is a little different from other WHERE clause operators because it requires two values: the beginning and end of the range. The BETWEEN operator can be used, for example, to check for all products that cost between $5 and $10 or for all dates that fall between specified start and end dates.
The following example demonstrates the use of the BETWEEN operator by retrieving all products with a price between $5 and $10:
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
prod_name prod_price ------------------ ---------- 8 inch teddy bear 5.9900 12 inch teddy bear 8.9900
As seen in this example, when BETWEEN is used, two values must be specified—the low end and high end of the desired range. The two values must also be separated by the AND keyword. BETWEEN matches all the values in the range, including the specified start and end values.
Checking for No Value
When a table is created, the table designer can specify whether or not individual columns can contain no value. When a column contains no value, it is said to contain a NULL value.
The SELECT statement has a special WHERE clause that can be used to check for columns with NULL values—the IS NULL clause. The syntax looks like this:
SELECT prod_name FROM Products WHERE prod_price IS NULL;
This statement returns a list of all products that have no price (an empty prod_price field, not a price of 0), and because there are none, no data is returned.
Tip
DBMS Specific Operators Many DBMSs extend the standard set of operators, providing advanced filtering options. Refer to your DBMS documentation for more information.