How to Retrieve Data from a Single Table - How to Code the WHERE Clause
(Page 10 of 17 )
Earlier in this chapter, I mentioned that to improve performance, you should code your SELECT statements so that they retrieve only the columns you need. That goes for retrieving rows too: The fewer rows you retrieve, the more efficient the statement will be. Because of that, you'll almost always include a WHERE clause on your SELECT statements with a search condition that filters the rows in the base table so that only the rows you need are retrieved. In the topics that follow, you'll learn a variety of ways to code this clause.
How to Use Comparison Operators
Figure 10 shows you how to use the comparison operators in the search condition of a WHERE clause. As you can see in the syntax summary at the top of this figure, you use a comparison operator to compare two expressions. If the result of the comparison is True, the row being tested is included in the query results.
The examples in this figure show how to use some of the comparison operators. The first WHERE clause, for example, uses the equal operator (=) to retrieve only those rows whose VendorState column have a value of IA. Notice that because the state code is a string literal, it must be included in single quotes. In contrast, the numeric literal used in the second WHERE clause is not enclosed in quotes. This clause uses the greater than (>) operator to retrieve only those rows that have a balance due greater than zero.
The third WHERE clause illustrates another way to retrieve all the invoices with a balance due. Like the second clause, it uses the greater than operator. Instead of comparing the balance due to a value of zero, however, it compares the invoice total to the total of the payments and credits that have been applied to the invoice.
The fourth WHERE clause illustrates how you can use comparison operators other than equal with string data. In this example, the less than operator (<) is used to compare the value of the VendorName column to a literal string that contains the letter M. That will cause the query to return all vendors with names that begin with the letters A through L.
You can also use the comparison operators with date literals, as illustrated by the fifth and sixth WHERE clauses. The fifth clause will retrieve rows with invoice dates on or before May 31, 2002, and the sixth clause will retrieve rows with invoice dates on or after May 1, 2002. Notice that, like string literals, date literals must be enclosed in single quotes. Also notice that the two literals specify the dates using different formats. You'll learn more about the acceptable date formats in chapter 8.
The last WHERE clause shows how you can test for a not equal condition. To do that, you code a less than sign followed by a greater than sign. In this case, only rows with a credit total that's not equal to zero will be retrieved.
Figure 10: The syntax of the WHERE clause with comparison operators
WHERE expression_1 operator expression_2
The comparison operators:
= Equal
> Greater than
< Less than
<= Less than or equal to
>= Greater than or equal to
<> Not equal
Examples of WHERE clauses that retrieve...
Vendors located in Iowa
WHERE VendorState = 'IA'
Invoices with a balance due (two variations)
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
WHERE InvoiceTotal > PaymentTotal + CreditTotal
Vendors with names from A to L
WHERE VendorName < 'M'
Invoices on or before a specified date
WHERE InvoiceDate <= '2002-05-31'
Invoices on or after a specified date
WHERE InvoiceDate >= '5/1/02'
Invoices with credits that don't equal zero
WHERE CreditTotal <> 0
Description
- You can use a comparison operator to compare any two expressions that result in like data types. Although unlike data types may be converted to data types that can be compared, the comparison may produce unexpected results.
- If the result of a comparison results in a True value, the row being tested is included in the result set. If it's False or Unknown, the row isn't included.
- To use a string literal or a date literal in a comparison, enclose it in quotes. To use a numeric literal, enter the number without quotes.
- Character comparisons performed on SQL Server databases are not case-sensitive. So, for example, 'CA' and 'Ca' are considered equivalent.
Whenever possible, you should compare expressions that have similar data types. If you attempt to compare expressions that have different data types, SQL Server may implicitly convert the data type for you. Often, this implicit conversion is acceptable. However, implicit conversions will occasionally yield unexpected results. In that case, you can use the CONVERT function you saw earlier in this chapter or the CAST function you'll learn about in chapter 8 to explicitly convert data types so the comparison yields the results you want.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Use the AND, OR, and NOT Logical Operators >>
More MS SQL Server Articles
More By Bryan Syverson
