| Previous | Table of Contents | Next |
Week 1
Day 3
Selecting Rows with the WHERE Clause
So far, when you have written a query against a table, you have selected all the rows in that table. Today, you learn how to limit the rows that are returned by using the WHERE clause.
The primary goal for today is to learn how to use the WHERE predicate in a SELECT statement. On your way to learning that, you’ll cover the following topics:
- • How and when to use equality and inequality conditions in a WHERE clause
- • How to apply Boolean logic in a WHERE clause
- • How to compare a row to a list of values using the IN and NOT IN keywords
- • Using BETWEEN and NOT BETWEEN to test a range of values
- • Wildcard usage with the LIKE and NOT LIKE keywords
- • What null values are and how to deal with them
- • How to apply Boolean logic in a WHERE clause
What Does the WHERE Clause Do?
The WHERE clause limits the rows returned in a result set. Yesterday, when I retrieved a list of authors from the authors table, I wrote a query like this:
select au_name “Last”, au_fname “First” from authors
Last First ------------------------------- ---------------- White Johnson Green Marjorie Carson Cheryl O’Leary Michael Straight Dean Smith Meander Bennet Abraham Dull Ann Gringlesby Burt Locksley Charlene Greene Morningstar Blotchet-Halls Reginald Yokomoto Akiko del Castillo Innes DeFrance Michel Stringer Dirk MacFeather Stearns Karsen Livia Panteley Sylvia Hunter Sheryl McBadden Heather Ringer Anne Ringer Albert
But what if I only wanted information on a subset of these authors? What if I wanted the names of all the authors who live in California? This example demonstrates how to use the WHERE clause to list the names of authors who live in California:
select au_name “Last”, au_fname “First” from authors where state = ‘CA’
Last First ------------------------------- ------------- White Johnson Green Marjorie Carson Cheryl O’Leary Michael Straight Dean Bennet Abraham Dull Ann Gringlesby Burt Locksley Charlene Yokomoto Akiko Stringer Dirk MacFeather Stearns Karsen Livia Hunter Sheryl McBadden Heather (15 row(s) affected)
The query starts out just like the first one, but with the addition of the WHERE clause, I can limit the rows to authors living in California. If the state is equal to California (the WHERE condition is true) the row is included in the result set. If the state is not equal to California (the WHERE condition is false) the row is skipped.
Equality Conditions
Oftentimes, you are looking for a single row in a table containing hundreds, thousands, or millions of rows. To find the row, you must find a way to uniquely identify the row in the table and then put this condition in a WHERE clause. Sometimes you know a little bit of information about a row in the table and must use this information to retrieve the rest of the data from that row. Both of these scenarios would require you to use an equality comparison.
Suppose you need to get the address of Sheryl Hunter, one of the authors in the authors table.
select address, city, state, zip from authors where au_lname = ‘Hunter’
address city state zip ---------------------- -------------- ---- ---- 3410 Blonde St Palo Alto CA 94301 (1 row(s) affected)
There is only one author in the authors table with the last name Hunter. The requested columns in this row are returned and all other rows are excluded.You do not need to include the tested columns in your result set. Because I already knew the author’s name, I decided not to ask for the name in the result set.
Let’s consider a different example for an equality condition. Suppose your company has an INVOICES table containing several million rows. The first column in this table is invoice_num. The invoice_num column has a unique value for each row in the table. A salesman calls you to get a copy of invoice #43241. To get this row, you might use a query like this one:
select * from invoices where invoice_num = 43241
Searching for a particular row, where a unique ID is known, can be resolved by the server very quickly when the proper index is defined on the table. Indexing is discussed on Day 15, but you shouldn’t worry about it for a while, yet. If your server is optimally configured and has the right indexes defined, this query would be answered in a subsecond time frame, even if there were millions of rows in the invoices table.
Warning: If you write SQL inside your application programs, never use * when selecting data. If you select * and the table definition changes (the table has its columns reordered or a column added or deleted) your program will be unable to deal with the change.Always name every column in your select if you are writing application programs.
| Previous | Table of Contents | Next |