Linux DevCenter    
 Published on Linux DevCenter (http://www.linuxdevcenter.com/)
 See this if you're having trouble printing code examples


aboutSQL

Where to Use WHERE

01/05/2001

Previously in aboutSQL:

What's the Big Deal about SQL?

Getting Started with SQL

Introducing SELECT

Filtering SELECTed Data with WHERE

INSERTing Data

UPDATE that Data!

DELETE Tips and Tricks

Using Functions

Aggregate Functions

Controlling Data Display with ORDER BY

Group By

The "Key" to Good SQL

Good Relations

Types of Relationships

JOINed at the Hip

More on JOINS

Types of JOINs

The Outer Limits of SQL JOINs

Self-Inflicted SQL

SQL Subqueries

Introducing SQL Sets

Data Definition Language

Working With Tables

SQL Data Types

Several weeks ago, I introduced the SQL WHERE clause as we started discussing the uses of the SELECT statement; this week we're going to drill a little deeper into the capabilities of the WHERE clause to see what it can really do. It is especially relevant now since we've discussed the UPDATE and DELETE statements in the past couple of columns, which can (and generally should) also use the WHERE clause. And as we'll see in future columns, some of the operators of the WHERE clause can be used to do cool things with other SQL clauses. So with no further ado, I give you:

the WHERE clause

The SQL WHERE clause accepts a number of comparison operators (Table 1) and Boolean operators (Table 2) in virtually any combination. They include the standard equality operators as well as more specialized operators to make comparisons faster and easier, but if you've done any sort of scripting or programming, they should be fairly straightforward.

Table 1: SQL Comparison Operators

Operator

Meaning

<>

non-equality

=

equality

<

less than

<=

less than or equal

>

greater than

>=

greater than or equal

BETWEEN

is in a range of values

IN

is in a set of values

LIKE

is similar to a string value


Table 2: SQL Boolean Operators

Operator

Meaning

AND

combines two conditions

OR

either or both of two conditions

NOT

removes occurances of condition from query

IS

equivalent (equal) to some condition

These operators provide a surprising rich ability to filter virtually any kind of SQL data before a statement is executed. They can be used to find a set of data with the SELECT command, to UPDATE rows with a certain set of characteristics, or even DELETE entire sets of similar records.

WHERE in action

The most typical use of the WHERE clause is to find a specific record in the database so it can be displayed or modified, so starting with our MusicCollection database,

MusicCollection
ID Title Artist Year
1 Pet Sounds The Beach Boys 1966
2 Security Peter Gabriel 1990
3 The Way It Is Bruce Hornsby 1986
4 Joshua Judges Ruth Lyle Lovett 1992

we can use the WHERE clause to filter for the album title with an ID value of 3

SELECT Title FROM MusicCollection WHERE ID=3;
Title
--------------------
The Way It Is

which is pretty much the same activity we discussed in the earlier article on SELECT. But all that's review, John Paul -- what's new this week? This week the focus is on the more unusual operators and how to combine them to achieve more sophisticated data filtering.

The simplest way to get more sophisticated filtering is to combine selection criteria using Boolean operators. For example, to find all albums produced in the 1980's by Peter Gabriel that are in our database, I could use the following SQL statement:

SELECT Title FROM MusicCollection WHERE Artist='Peter Gabriel' AND Year>=1980 AND Year<=1989;

But what if I also want to include the early albums he did with Genesis (in the pre-Phil Colins days?) I could use the following SQL statement:

SELECT Title FROM MusicCollection WHERE Artist='Peter Gabriel' OR Artist='Genesis' AND Year>=1980 AND Year<=1989;

which is far more complex that it first appears. Here's where we get to talk about operator precedence, or more importantly, where I implore you to use parentheses so we don't even have to have the discussion. For the record, parentheses are evaluated with higher precedence than AND which has higher precedence than OR. But why fight it? A better implementation of the previous statement is:

SELECT Title FROM MusicCollection WHERE (Artist='Peter Gabriel' OR Artist='Genesis') AND Year>=1980 AND Year<=1989;

or even:

SELECT Title FROM MusicCollection WHERE (Artist='Peter Gabriel' OR Artist='Genesis') AND (Year>=1980 AND Year<=1989);

which makes everything crystal clear. Using parentheses is not a sign of weakness! Plus it makes your code much easier to read.

Now for the more fun comparison operators. It's probably pretty evident how to use the BETWEEN operator, which will certainly improve our Peter Gabriel filter's readability

SELECT Title FROM MusicCollection WHERE (Artist='Peter Gabriel' OR Artist='Genesis') AND (Year BETWEEN 1980 AND 1989);

The BETWEEN operator filters a column based on whether the value is between the high and low values including the high and low values themselves.

The IN operator in many ways is a shortcut for an OR operation, but is much more readable. Again, going back to our Peter Gabriel filter,

SELECT Title FROM MusicCollection WHERE Artist IN ('Peter Gabriel','Genesis') AND (Year>=1980 AND Year<=1989);

As you can probably see, if we had a list of 5 or 100 artists, the IN syntax is much more readable than multiple OR comparisons.

Finally, the LIKE operator is used for text pattern matching. It acceptsthe '_' (underscore) character to indicate matching any single letter, the '%' wildcard to match any number of characters, and the '[abc...]' wildcard which indicates that any of the specified characters can be matched at that location. I think a few examples would make things easier, so Table 3 offers a few ways to use the LIKE operator.

Table 3: The LIKE operator examples
Operator Example Matches
'_' Item LIKE '_ inch gold necklace'

1 inch gold necklace
7 inch gold necklace
etc.

'%' Name LIKE 'Peter G%' Peter Gabriel
Peter G4
Peter G
Peter G.
etc.
'[]' ProductName LIKE '[XZ]101' X101
Z101
combination ProductName LIKE '[XZ]%' X101
Z345
etc.

Note that the wildcard goes inside the single quotes for text.

All of these operators can be used in tandem, so we could turn our Peter Gabriel filter into a pretty easy-to-read statement using a combination of these comparison operators:

SELECT Title FROM MusicCollection WHERE (Artist LIKE '%Gabriel%' OR Artist='Genesis') AND (Year BETWEEN 1980 AND 1989);

as one possible formulation.

Next Steps

This week we covered the comparison operators for the WHERE clause in more detail. This clause is used to filter the database before applying a SQL statement such as SELECT, UPDATE or DELETE. Next week, we'll take a different tack a look at the functions available in SQL to process data before using a comparison operator as we continue in our quest to learn more aboutSQL.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.


Read more aboutSQL columns.

Discuss this article in the O'Reilly Network Linux Forum.

Return to the Linux DevCenter.

Copyright © 2009 O'Reilly Media, Inc.