Filtering Data - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

Filtering Data

So far, your queries have retrieved all the rows in the table. You also can use the SELECT statement to retrieve only data that matches specific search criteria. To do so, you must use the WHERE clause and provide a restricting condition. If a WHERE clause is present, when the SQL SELECT statement is processed, every row is evaluated against the condition. Only rows that pass the restriction are selected.

If you use a WHERE clause, it must appear after the table name. If you use both the ORDER BY and WHERE clauses, the WHERE clause must appear after the table name but before the ORDER BY clause.

Filtering on a Single Column

To demonstrate filtering, modify the SELECT statement to retrieve only movies with a RatingID of 1. Listing 6.7 contains the SELECT statement, and the resulting output appears in Figure 6.18.

Listing 6.7. SELECT with WHERE Clause

SELECT MovieTitle, PitchText, Summary
FROM Films
WHERE RatingID=1
ORDER BY MovieTitle DESC

Filtering on Multiple Columns

The WHERE clause also can take multiple conditions. To search for Ben Forta, for example, you can specify a search condition in which the first name is Ben and the last name is Forta, as shown in Listing 6.8. As Figure 6.19 shows, only Ben Forta is retrieved.

Listing 6.8. SELECT with Multiple WHERE Clauses

SELECT FirstName, LastName, Email
FROM Contacts
WHERE FirstName='Ben' AND LastName='Forta'

CAUTION

Text passed to a SQL query must be enclosed within quotation marks. If you omit the quotation marks, the SQL parser thinks that the text you specified is the name of a column, and you receive an error because that column doesn't exist. Pure SQL allows strings to be enclosed within single quotation marks ('like this') or within double quotation marks ("like this"). But when passing text in a SQL statement to an ODBC or JDBC driver, you must use single quotation marks. If you use double ones, the parser treats the first double quotation mark as a statement terminator, and ignores all text after it.

The AND and OR Operators

Multiple WHERE clauses can be evaluated as AND conditions or OR conditions. The example in Listing 6.8 is an AND condition. Only rows in which both the last name is Forta and the first name is Ben will be retrieved. If you change the clause to the following, contacts with a first name of Ben will be retrieved (regardless of last name) and contacts with a last name of Forta will be retrieved (regardless of first name):

WHERE FirstName='Ben' OR LastName='Forta'

You can combine the AND and OR operators to create any search condition you need. Listing 6.9 shows a WHERE clauses that can be used to retrieve only Ben Forta and Rick Richards.

Listing 6.9. Combining WHERE Clauses with AND and OR Operators

SELECT NameFirst, NameLast, Email
FROM Contacts
WHERE FirstName='Ben' AND LastName='Forta'
OR FirstName='Rick' AND LastName='Richards'

Evaluation Precedence

When a WHERE clause is processed, the operators are evaluated in the following order of precedence:

• Parentheses have the highest precedence.

• The AND operator has the next level of precedence.

• The OR operator has the lowest level of precedence.

What does this mean? Well, look at the WHERE clause in Listing 6.9. The clause reads WHERE FirstName='Ben' AND LastName='Forta' OR FirstName='Rick' AND LastName='Richards'. AND is evaluated before OR so this statement looks for Ben Forta and Rick Richards, which is what we wanted.

But what would be returned by a WHERE clause of WHERE FirstName='Rick' OR FirstName='Ben' AND LastName= 'Forta'? Does that statement mean anyone whose first name is either Rick or Ben, and whose last name is Forta, or does it mean anyone whose first name is Rick, and also Ben Forta? The difference is subtle, but if the former is true, then only contacts with a last name of Forta will be retrieved, whereas if the latter is true, then any Rick will be retrieved, regardless of last name.

So which is it? Because AND is evaluated first, the clause means anyone whose first name is Rick, and also Ben Forta. This might be exactly what you wantand then again, it might not.

To prevent the ambiguity created by mixing AND and OR statements, parentheses are used to group related statements. Parentheses have a higher order of evaluation than both AND and OR, so they can be used to explicitly match related clauses. Consider the following WHERE clauses:

WHERE (FirstName='Rick' OR FirstName='Ben') AND (LastName='Forta')

This clause means anyone whose first name is either Rick or Ben, and whose last name is Forta.

WHERE (FirstName='Rick') OR (FirstName='Ben' AND LastName='Forta')

This clause means anyone whose first name is Rick, and also Ben Forta.

As you can see, the exact same set of WHERE clauses can mean very different things depending on where parentheses are used.

TIP

Always using parentheses whenever you have more than one WHERE clause is good practice. They make the SQL statement easier to read and easier to debug.

WHERE Conditions

In the examples so far, you have used only the = (equal to) operator. You filtered rows based on their being equal to a specific value. Many other operators and conditions can be used with the WHERE clause; they're listed in Table 6.2.

Feel free to experiment with different SELECT statements, using any of the WHERE clauses listed here. The SQL Query tool is safe. By default, it won't update or modify data (by default), so there's no harm in using it to play around with statements and clauses.

Testing for Equality: =

You use the = operator to test for value equality. The following example retrieves only contacts whose last name is Smith:

WHERE LastName = 'Smith'

Testing for Inequality: <>

You use the <> operator to test for value inequality. The following example retrieves only contacts whose first name is not Kim:

WHERE FirstName <> 'Kim'

Testing for Less Than: <

By using the < operator, you can test that the value on the left is less than the value on the right. The following example retrieves only contacts whose last name is less than C, meaning that their last name begins with an A or a B:

WHERE LastName < 'C'

Testing for Less Than or Equal To: <=

By using the <= operator, you can test that the value on the left is less than or equal to the value on the right. The following example retrieves actors aged 21 or less:

WHERE Age <= 21

Testing for Greater Than: >

You use the > operator to test that the value on the left is greater than the value on the right. The following example retrieves only movies with a rating of 3 or higher (greater than 2):

WHERE RatingID > 2

Testing for Greater Than or Equal To: >=

You use the >= operator to test that the value on the right is greater than or equal to the value on the left. The following example retrieves only contacts whose first name begins with the letter J or higher:

WHERE FirstName >= 'J'

BETWEEN

Using the BETWEEN condition, you can test whether a value falls into the range between two other values. The following example retrieves only actors aged 20 to 30. Because the test is inclusive, ages 20 and 30 are also retrieved:

WHERE Age BETWEEN 20 AND 30

The BETWEEN condition is actually nothing more than a convenient way of combining the >= and <= conditions. You also could specify the preceding example as follows:

WHERE Age >= 20 AND Age <= 30

Using the BETWEEN condition makes the statement easier to read.

EXISTS

Using the EXISTS condition, you can check whether a subquery returns any rows.

Subqueries are explained in Chapter 30, "More About SQL and Queries."

IN

You can use the IN condition to test whether a value is part of a specific set. The set of values must be surrounded by parentheses and separated by commas. The following example retrieves contacts whose last name is Black, Jones, or Smith:

WHERE LastName IN ('Black', 'Jones', 'Smith')

The preceding example is actually the same as the following:

WHERE LastName = 'Black' OR LastName = 'Jones' OR LastName = 'Smith'

Using the IN condition has two advantages. First, it makes the statement easier to read. Second, and more importantly, you can use the IN condition to test whether a value is within the results of another SELECT statement (providing a complete SELECT statement in between ( and ) so as to match whatever that statement returned).

IS NULL and IS NOT NULL

A NULL value is the value of a column that is empty. The IS NULL condition tests for rows that have a NULL value; that is, the rows have no value at all in the specified column. IS NOT NULL tests for rows that have a value in a specified column.

The following example retrieves all contacts whose Email column is empty:

WHERE Email IS NULL

To retrieve only the contacts who don't have an email address, use the following example:

WHERE Email IS NOT NULL

LIKE

Using the LIKE condition, you can test for string pattern matches using wildcards. Two wildcard types are supported. The % character means that anything from that position on is considered a match. You also can use [] to create a wildcard for a specific character.

The following example retrieves actors whose last name begins with the letter S. To match the pattern, a last name must have an S as the first character.

WHERE LastName LIKE 'S%'

To retrieve actors with an S anywhere in their last names, you can use the following:

WHERE LastName LIKE '%S%'

You also can retrieve just actors whose last name ends with S, as follows:

WHERE LastName LIKE '%S'

The LIKE condition can be negated with the NOT operator. The following example retrieves only actors whose last name doesn't begin with S:

WHERE LastName NOT LIKE 'S%'

Using the LIKE condition, you also can specify a wildcard on a single character. If you want to find all actors named Smith but aren't sure whether the one you want spells his or her name Smyth, you can use the following:

WHERE LastName LIKE 'Sm[iy]th'

This example retrieves only names that start with Sm, then have an i or a y, and then a final th. As long as the first two characters are Sm and the last two are th, and as long as the middle character is i or y, the name is considered a match.

TIP

Using the powerful LIKE condition, you can retrieve data in many ways. But everything has its price, and the price here is performance. Generally, LIKE conditions take far longer to process than other search conditions, especially if you use wildcards at the beginning of the pattern. As a rule, use LIKE and wildcards only when absolutely necessary.

For even more powerful searching, LIKE may be combined with other clauses using AND and OR. And you may even include multiple LIKE clauses in a single WHERE clause.