The secret to creating powerful SELECT statements in SQL is writing efficient WHERE clauses. This week, I’ll discuss how you can bypass open-ended searches by using some advanced tools that will precisely locate the information you need.
For subscribers only
Subscribe to Jeff Davis' Help Desk TechMail and get a bonus of Jeff's picks for the best resources on the Web.
Whither the WHERE clause?
For some queries, you don't need a WHERE clause at all. By default, SQL assumes you want to see all of the records from a table unless you use a WHERE clause to filter those records.
If you don't use a WHERE clause to filter the results returned by your SELECT statement, you could be wasting valuable system resources as well as your time. For instance, if you're looking for a particular customer's records, you could issue a command in the form
SELECT ALL FROM CUSTOMERS
The "ALL" keyword tells the SQL engine to return all of the fields in the Customers table. The absence of a WHERE clause means the SQL engine will make available all of the records in the table. You'll have to scroll through a haystack of records manually to locate a particular customer's records.
When you add a WHERE clause to your SELECT statement in the form
SELECT ALL FROM CUSTOMERS WHERE [some condition is true]
you get to drill down on information more efficiently than if you simply click through screen after screen of records.
Let's look at some of the tools available for crafting your WHERE clauses.
Read more on SQL basics
In "A crash course in SQL," I explained how to query a single table using SELECT, the most commonly used SQL command. In "Another crash course in SQL," I explained how to query two or more tables with a single statement.
Exact matches: Requesting a specific value
In its simplest form, a SELECT statement with a WHERE clause tells the SQL engine to show all the records where a certain condition is true about at least one field.
For instance, here is a sample statement that illustrates how to view records that contain exact matches with a particular value. In the WHERE clause, I’ve added a single "is equal to" condition:
SELECT FName, LName, Phone FROM Customers WHERE Zip_code="10010"
This statement returns three columns (Fname, Lname, and Phone) from the Customers table. But how many rows does the SQL engine return? The answer depends on how many records contain the entry 10010 in the Zip_code field. In this case, only exact matches count; records that contain anything but 10010 in the Zip_code field will be ignored.
Close matches: Requesting values less than or greater than a specific value
As you'd expect, SQL syntax supports basic math operators such as less than (<), greater than (>), greater than or equal to (>=), and less than or equal to (<=). You use these operators when you're looking for records with values that fall above or below a certain threshold.
Suppose you want to locate records where the value in a field equals or exceeds 100. In the statement
SELECT FName, LName, Phone FROM Customers WHERE Last_purchase >=100
the condition for the WHERE clause, Last_purchase >=100, tells the SQL engine to ignore any records in which the value stored in the Last_purchase field is less than 100.
Combining conditions with AND and OR
The next level of WHERE clause tells the SQL engine to select records based on two or more conditions. The keywords AND and OR, two of the infamous Boolean operators, let you craft queries that precisely zoom in on records.
Suppose you want to see records for customers who live in a particular ZIP code and who have spent a certain amount of money. To do so, you'd use a WHERE clause like this:
SELECT FName, LName, Phone FROM Customers WHERE Last_purchase >=100 AND Zip_code="10010"
Before the SQL engine will display a record in your result set, both conditions, Last_purchase >=100 AND Zip_code="10010", must be true. If you use the OR keyword instead of AND, you'll get records for which either or both of those conditions is true.
The AND operator is vital to extracting information from two or more tables at a time. For details, refer to "Another crash course in SQL."
Using three or more conditions
Here's the most important rule to remember when you're using three or more Boolean operators, such as AND and OR, in your WHERE clauses: Use parentheses to group together conditions that "go" together.
For instance, consider the following WHERE clause:
WHERE Last_purchase >=100 OR Credit_bal<500 AND Zip_code="10010"
If Last_purchase contains 200, Credit_bal contains 500, and Zip_code contains 10010, then the conditions would evaluate to True, False, and True, respectively. Will the record show up in the result set?
The answer depends on your version of SQL. Some versions will treat the last two conditions, the ones separated by the AND, as one logical unit, requiring both conditions to be true. The truth is, that sample statement is poorly written and may not yield the results you want, no matter which flavor of SQL you use.
Fortunately, you don't have to worry about the order in which SQL evaluates your Boolean operators if you take one simple precaution: Use parentheses to combine the conditions that you want SQL to evaluate first.
To illustrate, consider this WHERE clause:
WHERE (Last_purchase >=100 OR Credit_bal<500) AND Zip_code="10010"
In this case, all the records in the result set would have at least one thing in common: They would all contain 10010 in the Zip_code field.
Now, consider how SQL would handle the same clause if you move the parentheses like this:
WHERE Last_purchase >=100 OR (Credit_bal<500 AND Zip_code="10010")
In this case, the record set could contain records with any entry in the Zip_code field, as long as the entry in the Last_purchase field is greater than or equal to 100. Why? The requirement for the exact match in the Zip_code field is grouped with the less-than condition for the Last_purchase field, and that "grouped" expression is joined with the first condition by the OR keyword. Therefore, the requirement for the Zip_code only applies to records where the value of Credit_bal is less than 500.
Tell us what you think
To comment on these tips or to share one of your own favorite SQL tips, please post your comments or follow this link to write to Jeff Davis.