- 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
This chapter is from the book
This chapter is from the book
This chapter is from the book
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
|
CONDITION |
SQL OPERATORS |
|
Comparison |
=, <>, <, <=, >, >= |
|
Pattern matching |
LIKE |
|
Range filtering |
BETWEEN |
|
List filtering |
IN |
|
Null testing |
IS NULL |
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
OPERATOR
DESCRIPTION
=
Equal to
<>
Not equal to
<
Less than
<=
Less than or equal to
>
Greater than
>=
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
or
Compare the values unequally and return a result with no rows
or
- 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:
Type:
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
Tips
- Place the WHERE clause before the ORDER BY clause in a SELECT statement in which both appear.
- A null represents the unknown and won’t match anything, not even another null. Rows in which nulls are involved in comparisons won’t be in the result. To compare nulls, use WHERE test_column IS NULL (WHERE test_column = NULL is incorrect); see “Testing for Nulls with IS NULL” later in this chapter. See also “Nulls” in Chapter 3.
The right and left sides of the comparison can be more complex than I described. The general form of a comparison is:
expr1 op expr2
expr1 and expr2 are expressions. An expression is any valid combination of column names, literals, functions, and operators that resolves to a single value (per row). Chapter 5 covers expressions in more detail (Listing 4.21 and Figure 4.21).
Listing 4.21. List the titles that generated more than $1 million in revenue. This search condition uses an arithmetic expression. See Figure 4.21 for the result.
SELECT title_name, price * sales AS "Revenue" FROM titles WHERE price * sales > 1000000;Figure 4.21 Result of Listing 4.21.
title_name Revenue ----------------------------- ----------- Ask Your System Administrator 1025396.65 Exchange of Platitudes 1400008.00 I Blame My Mother 35929790.00 Spontaneous, Not Annoying 1299012.99
For speed, fold your constants into a minimal number of expressions. For example, change
WHERE col1 + 2 <= 10
to
WHERE col1 <= 8
The best practice is to put only simple column references to the left of the = and more-complex expressions to the right.
- In general, the fastest comparison is for equality (=), following by the inequalities (<, <=, >, >=). The slowest is not-equal (<>). If possible, express your conditions by using faster comparisons.
- You can’t use an aggregate function such as SUM() or COUNT() in a WHERE clause; see Chapter 6.
- An operation that selects certain rows from a table is called a restriction.
Your DBMS’s collation determines whether string comparisons are case insensitive (‘A’ = ‘a’) or case sensitive (‘A’ ≠ ‘a’). Microsoft Access, Microsoft SQL Server, DB2, and MySQL perform case-insensitive comparisons by default. Oracle and PostgreSQL perform case-sensitive comparisons by default. In general, case-sensitive comparisons are slightly faster than case-insensitive ones. See also “Changing String Case with UPPER() and LOWER()” in Chapter 5.Case sensitivity can vary by context. MySQL comparisons are case insensitive in WHERE comparisons but are case sensitive in string-related functions, for example.
In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 4.20, change the date in the WHERE clause to #2001-01-01#.
In Microsoft SQL Server and DB2 date literals, omit the DATE keyword. To run Listing 4.20, change the date in the WHERE clause to '2001-01-01'.
In older PostgreSQL versions, to compare a value in a NUMERIC or DECIMAL column with a real (floating-point) number, convert the real number to NUMERIC or DECIMAL explicitly. See “Converting Data Types with CAST()” in Chapter 5.
Some DBMSs support the comparison operator != as a synonym for <> (not equal). You should use <> to keep your code portable.