Selection in SQL

Selection in SQL

The selection operator of relational algebra, and much more, is available through the WHERE clause of SQL. The expressions that may follow WHERE contain conditional expressions like those found in common languages such as C or Java.

Case Insensitivity

We may build expressions by comparing values using the six common comparison operators: =, <>, <, >, <=, and >=. These operators have the same meanings as in C, but <> is the SQL symbol for "not equal to"; it corresponds to != in C.

The values that may be compared contain constants and attributes of the relations mentioned after FROM. We may also apply the usual arithmetic operators, +, *, and so on, to numeric values before we compare them. For example, (year - 1930) * (year - 1930) < 100 is true for those years within 9 of 1930. We may apply the concatenation operator | |  to strings; for example foo | |  'bar' has value foobar.

An example comparison is

studioName = 'Disney'

in "The Database Language SQL" Example 1. The attribute studioName of the relation Movie is tested for equality against the constant 'Disney'. This constant is string-valued: strings in SQL are denoted by surrounding them with single quotes. Numeric constants, integers and reals, are also allowed, and SQL uses the common notations for reals such as -12.34 or 1.23E45.

The result of a comparison is a boolean value: either TRUE or FALSE. Boolean values may be combined by the logical operators AND, OR, and NOT, with their expected meanings. For example, we saw in "The Database Language SQL" Example 1 how two conditions could be combined by AND. The WHERE clause of this example evaluates to true if and only if both comparisons are satisfied; that is, the studio name is 'Disney' and the year is 1990. Here are some more examples of queries with complex WHERE clauses.

Example 1: The following query asks for all the movies made after 1970 that are in black-and-white.

SELECT title
FROM Movie
WHERE year > 1970 AND NOT inColor;

SQL Queries and Relational Algebra

In this condition, we again have the AND of two booleans. The first is an ordinary comparison, but the second is the attribute inColor, negated. The use of this attribute by itself makes sense, because inColor is of type boolean. Next, consider the query

SELECT title
FROM Movie
WHERE (year > 1970 OR length < 90) AND studioName = 'MGM';

This query asks for the titles of movies made by MGM Studios that either were made after 1970 or were less than 90 minutes long. Notice that comparisons can be grouped using parentheses. The parentheses are required here because the precedence of logical operators in SQL is the same as in most other languages: AND takes precedence over OR, and NOT takes precedence over both.