- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
Filtering Rows with WHERE
The result of each SELECT statement so far has included every row in the table (for the specified columns). You can use the WHERE clause to filter unwanted rows from the result. This filtering capability gives the SELECT statement its real power. In a WHERE clause, you specify a search condition that has one or more conditions that need to be satisfied by the rows of a table. A condition, or predicate, is a logical expression that evaluates to true, false, or unknown. Rows for which the condition is true are included in the result; rows for which the condition is false or unknown are excluded. (An unknown result, which arises from nulls, is described in the next section.) SQL provides operators that express different types of conditions (Table 4.1). Operators are symbols or keywords that specify actions to perform on values or other elements.
Table 4.1. Types of Conditions
=, <>, <, <=, >, >=
SQL’s comparison operators compare two values and evaluate to true, false, or unknown (Table 4.2). The data type determines how values are compared:
- Character strings are compared lexicographically. < means precedes, and > means follows. See “Data Types” in Chapter 3 and “Sorting Rows with ORDER BY” earlier in this chapter.
- Numbers are compared arithmetically. < means smaller, and > means larger.
Datetimes are compared chronologically. < means earlier, and > means later. Datetimes must have the same fields (year, month, day, hour, and so on) to be compared meaningfully.
Table 4.2. Comparison Operators
Not equal to
Less than or equal to
Greater than or equal to
Compare only identical or similar data types. If you try to compare values that have different data types, your DBMS might:
Return an error
Compare the values unequally and return a result with no rows
- Attempt to convert the values to a common type and compare them if successful or return an error if unsuccessful
To filter rows by making a comparison:
SELECT columns FROM table WHERE test_column op value;
columns is one or more comma-separated column names, and table is the name of the table that contains columns.
In the search condition, test_column is the name of a column in table. (test_column doesn’t have to be listed in columns.) op is one of the comparison operators listed in Table 4.2, and value is a value that’s compared with the value in test_column (Listings 4.18 through 4.20, Figures 4.18 through 4.20).
Listing 4.18. List the authors whose last name is not Hull. See Figure 4.18 for the result.
SELECT au_id, au_fname, au_lname FROM authors WHERE au_lname <> 'Hull';
Figure 4.18 Result of Listing 4.18.
au_id au_fname au_lname ----- --------- ----------- A01 Sarah Buchman A02 Wendy Heydemark A05 Christian Kells A06 Kellsey A07 Paddy O'Furniture
Listing 4.19. List the titles for which there is no signed contract. See Figure 4.19 for the result.
SELECT title_name, contract FROM titles WHERE contract = 0;
Figure 4.19 Result of Listing 4.19.
title_name contract -------------------------- -------- Not Without My Faberge Egg 0
Listing 4.20. List the titles published in 2001 and later. See Figure 4.20 for the result.
SELECT title_name, pubdate FROM titles WHERE pubdate >= DATE '2001-01-01';
Figure 4.20 Result of Listing 4.20.
title_name pubdate ---------------------------- ---------- Exchange of Platitudes 2001-01-01 Just Wait Until After School 2001-06-01 Kiss My Boo-Boo 2002-05-31