How to Use SQL Predicates
Some Data Manipulation Language (DML) statements read like ordinary English-language sentences and are easy to understand. Predicates are SQL equivalents of logical propositions. The following statement is an example of a proposition:
“The student is a senior.”
In a table containing information about students, the domain of the CLASS column may be SENIOR, JUNIOR, SOPHOMORE, FRESHMAN, or NULL. You can use the predicate CLASS = SENIOR to filter out rows for which the predicate is False, retaining only those for which the predicate is True.
Sometimes the value of a predicate in a row is Unknown (NULL). In those cases, you may choose either to discard the row or to retain it. (After all, the student could be a senior.) The correct course of action depends on the situation.
Class = SENIOR is an example of a comparison predicate. SQL has six comparison operators. A simple comparison predicate uses one of these operators. Table 3-3 shows the comparison predicates and some legitimate as well as bogus examples of their use.
|=||Equal to||Class = SENIOR|
|<>||Not equal to||Class <> SENIOR|
|<||Less than||Class < SENIOR|
|>||Greater than||Class > SENIOR|
|<=||Less than or equal to||Class <= SENIOR|
|>=||Greater than or equal to||Class >= SENIOR|
In the preceding example, only the first two entries in Table 3-3 (Class = SENIOR and Class <> SENIOR) make sense. SOPHOMORE is considered greater than SENIOR because SO comes after SE in the default collation sequence, which sorts in ascending alphabetical order. This interpretation, however, is probably not the one you want.