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.

Operator Comparison Expression
= 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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com