How To Receive Data from a Single Table, concluded
If you have ever wondered how to code SELECT statements, this article is for you. It is the third of three parts, excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162).
Figure 3-13 shows how to use the BETWEEN operator in a WHERE clause. When you use this operator, the value of a test expression is compared to the range of values specified in the BETWEEN phrase. If the value falls within this range, the row is included in the query results.
The first example in this figure shows a simple WHERE clause that uses the BETWEEN operator. It retrieves invoices with invoice dates between May 1, 2002 and May 31, 2002. Note that the range is inclusive, so invoices with invoice dates of May 1 and May 31 are included in the results.
The second example shows how to use the NOT operator to select rows that are not within a given range. In this case, vendors with zip codes that aren’t between 93600 and 93799 are included in the results.
The third example shows how you can use a calculated value in the test expression. Here, the PaymentTotal and CreditTotal columns are subtracted from the InvoiceTotal column to give the balance due. Then, this value is compared to the range specified in the BETWEEN phrase.
The last example shows how you can use calculated values in the BETWEEN phrase. Here, the first value is the result of the GETDATE function, and the second value is the result of the GETDATE function plus 30 days. So the query results will include all those invoices that are due between the current date and 30 days from the current date.
Figure 3-13. How to use the BETWEEN operator
The syntax of the WHERE clause with a BETWEEN phrase
WHERE test_expression [NOT] BETWEEN begin_expression AND end_expression
Examples of the BETWEEN phrase
A BETWEEN phrase with literal values
WHERE InvoiceDate BETWEEN '2002-05-01' AND '2002-05-31'
A BETWEEN phrase preceded by NOT
WHERE VendorZipCode NOT BETWEEN 93600 AND 93799
A BETWEEN phrase with a test expression coded as a calculated value
WHERE InvoiceTotal – PaymentTotal – CreditTotal BETWEEN 200 AND 500
A BETWEEN phrase with the upper and lower limits coded as calculated values
WHERE InvoiceDueDate BETWEEN GetDate() AND GetDate() + 30
Description
You can use the BETWEEN phrase to test whether an expression falls within a range of values. The lower limit must be coded as the first expression, and the upper limit must be coded as the second expression. Otherwise, the result set will be empty.
The two expressions used in the BETWEEN phrase for the range of values are inclusive. That is, the result set will include values that are equal to the upper or lower limit.
You can use the NOT operator to test for an expression that’s not within the given range.
One final operator you can use in a search condition is the LIKE operator shown in figure 3-14. You use this operator along with the wildcards shown at the top of this figure to specify a string pattern, or mask, you want to match. The examples shown in this figure illustrate how this works.
In the first example, the LIKE phrase specifies that all vendors in cities that start with the letters SAN should be included in the query results. Here, the percent sign (%) indicates that any characters can follow these three letters. So San Diego and Santa Ana are both included in the results.
The second example selects all vendors whose vendor name starts with the letters COMPU, followed by any one character, the letters ER, and any characters after that. Two vendor names that match that pattern are Compuserve and Computerworld.
The third example searches the values in the VendorContactLName column for a name that can be spelled two different ways: Damien or Damion. To do that, the mask specifies the two possible characters in the fifth position, E and O, within brackets.
The fourth example uses brackets to specify a range of values. In this case, the VendorState column is searched for values that start with the letter N and end with any letter from A to J. That excludes states like Nevada (NV) and New York (NY).
The fifth example shows how to use the caret (^) to exclude one or more characters from the pattern. Here, the pattern says that the value in the VendorState column must start with the letter N, but must not end with the letters K through Y. This produces the same result as the previous statement.
The last example in this figure shows how to use the NOT operator with a LIKE phrase. The condition in this example tests the VendorZipCode column for values that don’t start with the numbers 1 through 9. The result is all zip codes that start with the number 0.
The LIKE operator provides a powerful technique for finding information in a database that can’t be found using any other technique. Keep in mind, however, that this technique requires a lot of overhead, so it can reduce system performance. For this reason, you should avoid using the LIKE operator in production SQL code whenever possible.
Figure 3-14. How to use the LIKE operator
The syntax of the WHERE clause with a LIKE phrase
WHERE match_expression [NOT] LIKE pattern
Wildcard symbols
WHERE clauses that use the LIKE operator
Description
You use the LIKE operator to retrieve rows that match a string pattern, called a mask. Within the mask, you can use special characters, called wildcards, that determine which values in the column satisfy the condition.
You can use the NOT keyword before the LIKE keyword. Then, only those rows with values that don’t match the string pattern will be included in the result set.
Most LIKE phrases will significantly degrade performance compared to other types of searches, so use them only when necessary.
In chapter 1, you learned that a column can contain a null value. A null isn’t the same as zero, a blank string that contains one or more spaces ( ' ' ), or an empty string ( ' ' ). Instead, a null value indicates that the information is not applicable, not available, or unknown. When you allow null values in one or more columns, you’ll need to know how to test for them in search conditions. To do that, you use the IS NULL clause as shown in figure 3-15.
This figure uses a table named NullSample to illustrate how to search for null values. This table contains two columns. The first column, InvoiceID, is an identity column. The second column, InvoiceTotal, contains the total for the invoice, which can be a null value. As you can see in the first example, the invoice with InvoiceID 3 contains a null value.
The second example in this figure shows what happens when you retrieve all the invoices with invoice totals equal to zero. Notice that the row that has a null invoice total isn’t included in the result set. Likewise, it isn’t included in the result set that contains all the invoices with invoices totals that aren’t equal to zero, as illustrated by the third example. Instead, you have to use the IS NULL clause to retrieve rows with null values, as shown in the fourth example.
You can also use the NOT operator with the IS NULL clause as illustrated in the last example in this figure. When you use this operator, all of the rows that don’t contain null values are included in the query results.
Figure 3-15. How to use the IS NULL clause
The syntax of the WHERE clause with the IS NULL clause
WHERE expression IS [NOT] NULL
The contents of the NullSample table
SELECT * FROM NullSample
A SELECT statement that retrieves rows with zero values
SELECT * FROM NullSample WHERE InvoiceTotal = 0
A SELECT statement that retrieves rows with non-zero values
SELECT * FROM NullSample WHERE InvoiceTotal <> 0
A SELECT statement that retrieves rows with null values
SELECT * FROM NullSample WHERE InvoiceTotal IS NULL
A SELECT statement that retrieves rows without null values
SELECT * FROM NullSample WHERE InvoiceTotal IS NOT NULL
Description
A null value represents a value that’s unknown, unavailable, or not applicable. It isn’t the same as a zero, a blank space ( ' ' ), or an empty string ( ' ' ).
To test for a null value, you can use the IS NULL clause. You can also use the NOT keyword with this clause to test for values that aren’t null.
The definition of each column in a table indicates whether or not it can store null values. Before you work with a table, you should identify those columns that allow null values so you can accommodate them in your queries.
Note
SQL Server provides an extension that lets you use = NULL to test for null values. For this to work, however, the ANSI_NULLS system option must be set to OFF. For more information on this option, see Books Online.
The ORDER BY clause specifies the sort order for the rows in a result set. In most cases, you’ll use column names from the base table to specify the sort order as you saw in some of the examples earlier in this chapter. However, you can also use other techniques to sort the rows in a result set, as described in the topics that follow.
How to sort a result set by a column name
Figure 3-16 presents the expanded syntax of the ORDER BY clause. As you can see, you can sort by one or more expressions in either ascending or descending sequence. This is illustrated by the three examples in this figure.
The first two examples show how to sort the rows in a result set by a single column. In the first example, the rows in the Vendors table are sorted in ascending sequence by the VendorName column. Notice that since ascending is the default sequence, the ASC keyword is omitted. In the second example, the rows are sorted by the VendorName column in descending sequence.
To sort by more then one column, you simply list the names in the ORDER BY clause separated by commas as shown in the third example. Here, the rows in the Vendors table are first sorted by the VendorState column in ascending sequence. Then, within each state, the rows are sorted by the VendorCity column in ascending sequence. Finally, within each city, the rows are sorted by the VendorName column in ascending sequence. This can be referred to as a nested sort because one sort is nested within another.
Although all of the columns in this example are sorted in ascending sequence, you should know that doesn’t have to be the case. For example, I could have sorted by the VendorName column in descending sequence like this:
ORDER BY VendorState, VendorCity, VendorName DESC
Note that the DESC keyword in this example applies only to the VendorName column. The VendorState and VendorCity columns are still sorted in ascending sequence.
Figure 3-16.How to sort a result set by a column name
The expanded syntax of the ORDER BY clause
ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]] ...
An ORDER BY clause that sorts by one column in ascending sequence
SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY VendorName
An ORDER BY clause that sorts by one column in descending sequence
SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY VendorName DESC
An ORDER BY clause that sorts by three columns
SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY VendorState, VendorCity, VendorName
Description
The ORDER BY clause specifies how you want the rows in the result set sorted. You can sort by one or more columns, and you can sort each column in either ascending (ASC) or descending (DESC) sequence. ASC is the default.
By default, in an ascending sort, nulls appear first in the sort sequence, followed by special characters, then numbers, then letters. Although you can change this sequence, that’s beyond the scope of this book.
You can sort by any column in the base table regardless of whether it’s included in the SELECT clause. The exception is if the query includes the DISTINCT keyword. Then, you can only sort by columns included in the SELECT clause.
Figure 3-17 presents three more techniques you can use to specify sort columns. First, you can use a column alias that’s defined in the SELECT clause. The first SELECT statement in this figure, for example, sorts by a column named Address, which is an alias for the concatenation of the VendorCity, VendorState, and VendorZipCode columns. Notice that within the Address column, the result set is also sorted by the VendorName column.
You can also use an arithmetic or string expression in the ORDER BY clause, as illustrated by the second example in this figure. Here, the expression consists of the VendorContactLName column concatenated with the VendorContactFName column. Notice that neither of these columns is included in the SELECT clause. Although SQL Server allows this seldom-used coding technique, many other systems do not.
The last example in this figure shows how you can use column numbers to specify a sort order. To use this technique, you code the number that corresponds to the column of the result set, where 1 is the first column, 2 is the second column, and so on. In this example, the ORDER BY clause sorts the result set by the second column, which contains the concatenated address, then by the first column, which contains the vendor name. The result set returned by this statement is the same as the result set returned by the first statement. Notice, however, that the statement that uses column numbers is more difficult to read because you have to look at the SELECT clause to see what columns the numbers refer to. In addition, if you add or remove columns from the SELECT clause, you may also have to change the ORDER BY clause to reflect the new column positions. As a result, you should avoid using this technique.
Figure 3-17. How to sort a result set by an alias, an expression, or a column number
An ORDER BY clause that uses an alias
SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY Address, VendorName
An ORDER BY clause that uses an expression
SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY VendorContactLName + VendorContactFName
An ORDER BY clause that uses column positions
SELECT VendorName, VendorCity + ', ' + VendorState + ' ' + VendorZipCode AS Address FROM Vendors ORDER BY 2, 1
Description
The ORDER BY clause can include a column alias that’s specified in the SELECT clause.
The ORDER BY clause can include any valid expression. The expression can refer to any column in the base table, even if it isn’t included in the result set.
The ORDER BY clause can use numbers to specify the columns to use for sorting. In that case, 1 represents the first column in the result set, 2 represents the second column, and so on.
Perspective
The goal of this chapter has been to teach you the basic skills for coding SELECT statements. You’ll use these skills in almost every SELECT statement you code. As you’ll see in the chapters that follow, however, there’s a lot more to coding SELECT statements than what’s presented here. In the next three chapters, then, you’ll learn additional skills for coding SELECT statements. When you complete those chapters, you’ll know everything you need to know about retrieving data from a SQL Server database.
MS SQL Server 
/ 12 
How to Retrieve Data from a Single Table
