Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
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
  • + Share This
  • 🔖 Save To Your Account