How To Receive Data from a Single Table, continued
If you have ever wondered how to code SELECT statements, this article is for you. It is the second 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-7 introduces you to functions and illustrates how you use them in column specifications. A function performs an operation and returns a value. For now, don’t worry about the details of how the functions shown here work. You’ll learn more about all of these functions in chapter 8. Instead, just focus on how they’re used in column specifications.
To code a function, you begin by entering its name followed by a set of parentheses. If the function requires one or more parameters, you enter them within the parentheses and separate them with commas. When you enter a parameter, you need to be sure it has the correct data type. You’ll learn more about that in chapter 8.
The first example in this figure shows how to use the LEFT function to extract the first character of the VendorContactFName and VendorContactLName columns. The first parameter of this function specifies the string value, and the second parameter specifies the number of characters to return. The results of the two functions are then concatenated to form initials as shown in the result set for this statement.
The second example shows how to use the CONVERT function to change the data type of a value. This function requires two parameters. The first parameter specifies the new data type, and the second parameter specifies the value to convert. In addition, this function accepts an optional third parameter that specifies the format of the returned value. The first CONVERT function shown here, for example, converts the PaymentDate column to a character value with the format mm/dd/yy. And the second CONVERT function converts the PaymentTotal column to a variable-length character value that’s formatted with commas. These functions are included in a string expression that concatenates their return values with the InvoiceNumber column and three literal values.
The third example uses two functions that work with dates. The first one, GETDATE, returns the current date. Notice that although this function doesn’t accept any parameters, the parentheses are still included. The second function, DATEDIFF, gets the difference between two date values. This function requires three parameters. The first one specifies the units in which the result will be expressed. In this example, the function will return the number of days between the two dates. The second and third parameters specify the start date and the end date. Here, the second parameter is the invoice date and the third parameter is the current date, which is obtained using the GETDATE function.
Figure 3-7.How to use functions
A SELECT statement that uses the LEFT function
SELECT VendorContactFName, VendorContactLName, LEFT(VendorContactFName, 1) + LEFT(VendorContactLName, 1) AS Initials FROM Vendors
A SELECT statement that computes the age of an invoice
SELECT InvoiceDate, GETDATE() AS 'Today''s Date', DATEDIFF(day, InvoiceDate, GETDATE()) AS Age FROM Invoices
Description
An expression can include any of the functions that are supported by SQL Server. A function performs an operation and returns a value.
A function consists of the function name, followed by a set of parentheses that contains any parameters, or arguments, required by the function. If a function requires two or more arguments, you separate them with commas.
For more information on using functions, see chapter 8.
By default, all of the rows in the base table that satisfy the search condition you specify in the WHERE clause are included in the result set. In some cases, though, that means that the result set will contain duplicate rows, or rows whose column values are identical. If that’s not what you want, you can include the DISTINCT keyword in the SELECT clause to eliminate the duplicate rows.
Figure 3-8 illustrates how this works. Here, both SELECT statements retrieve the VendorCity and VendorState columns from the Vendors table. The first statement, however, doesn’t include the DISTINCT keyword. Because of that, the same city and state can appear in the result set multiple times. In the results shown in this figure, for example, you can see that Anaheim CA occurs twice and Boston MA occurs three times. In contrast, the second statement includes the DISTINCT keyword, so each city/state combination is included only once.
Figure 3-8. How to eliminate duplicate rows
A SELECT statement that returns all rows
SELECT VendorCity, VendorState FROM Vendors ORDER BY VendorCity
(121 rows)
A SELECT statement that eliminates duplicate rows
SELECT DISTINCT VendorCity, VendorState FROM Vendors
(53 rows)
Description
The DISTINCT keyword prevents duplicate (identical) rows from being included in the result set. It also causes the result set to be sorted by its first column.
The ALL keyword causes all rows matching the search condition to be included in the result set, regardless of whether rows are duplicated. Since this is the default, you’ll usually omit it.
To use the DISTINCT or ALL keyword, code it immediately after the SELECT keyword as shown above.
In addition to eliminating duplicate rows, you can limit the number of rows that are retrieved by a SELECT statement. To do that, you use the TOP clause. Figure 3-9 shows you how.
You can use the TOP clause in one of two ways. First, you can use it to retrieve a specific number of rows from the beginning, or top, of the result set. To do that, you code the TOP keyword followed by an integer value that specifies the number of rows to be returned. This is illustrated in the first example in this figure. Here, only five rows are returned. Notice that this statement also includes an ORDER BY clause that sorts the rows by the InvoiceTotal column in descending sequence. That way, the invoices with the highest invoice totals will be returned.
You can also use the TOP clause to retrieve a specific percent of the rows in the result set. To do that, you include the PERCENT keyword as shown in the second example. In this case, the result set includes six rows, which is five percent of the total of 122 rows.
By default, the TOP clause causes the exact number or percent of rows you specify to be retrieved. However, if additional rows match the values in the last row, you can include those additional rows by including WITH TIES in the TOP clause. This is illustrated in the third example in this figure. Here, the SELECT statement says to retrieve the top five rows from a result set that includes the VendorID and InvoiceDate columns sorted by the InvoiceDate column in descending sequence. As you can see, however, the result set includes six rows instead of five. That’s because WITH TIES is included in the TOP clause, and the columns in the sixth row have the same values as the columns in the fifth row.
Figure 3-9. How to return a subset of selected rows
A SELECT statement with a TOP clause
SELECT TOP 5 VendorID, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal DESC
A SELECT statement with a TOP clause and the PERCENT keyword
SELECT TOP 5 PERCENT VendorID, InvoiceTotal FROM Invoices ORDER BY InvoiceTotal DESC
A SELECT statement with a TOP clause and the WITH TIES keyword
SELECT TOP 5 WITH TIES VendorID, InvoiceDate FROM Invoices ORDER BY InvoiceDate DESC
Description
You can use the TOP clause within a SELECT clause to limit the number of rows included in the result set. When you use this clause, the first n rows that meet the search condition are included, where n is an integer.
If you include PERCENT, the first n percent of the selected rows are included in the result set.
If you include WITH TIES, additional rows will be included if their values match, or tie, the values of the last row.
You should include an ORDER BY clause whenever you use the TOP keyword. Otherwise, the rows in the result set will be in no particular sequence.
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 3-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 3-10. How to use the comparison operators
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.
Figure 3-11 shows how to use logical operators in a WHERE clause. You can use the AND and OR operators to combine two or more search conditions into a compound condition. And you can use the NOT operator to negate a search condition. The examples in this figure illustrate how these operators work.
The first two examples illustrate the difference between the AND and OR operators. When you use the AND operator, both conditions must be true. So, in the first example, only those vendors in New Jersey whose year-to-date purchases are greater than 200 are retrieved from the Vendors table (2 rows). When you use the OR operator, though, only one of the conditions must be true. So, in the second example, all the vendors from New Jersey and all the vendors whose year-to-date purchases are greater than 200 are retrieved (76 rows).
The third example shows a compound condition that uses two NOT operators. As you can see, this expression is somewhat difficult to understand. Because of that, and because using the NOT operator can reduce system performance, you should avoid using this operator. The fourth example in this figure, for instance, shows how the search condition in the third example can be rephrased to eliminate the NOT operator. Notice that the condition in the fourth example is much easier to understand.
The last two examples in this figure show how the order of precedence for the logical operators and the use of parentheses affect the result of a search condition. By default, the NOT operator is evaluated first, followed by AND and then OR. However, you can use parentheses to override the order of precedence or to clarify a logical expression, just as you can with arithmetic expressions. In the next to last example, for instance, no parentheses are used, so the two conditions connected by the AND operator are evaluated first. In the last example, though, parentheses are used so that the two conditions connected by the OR operator are evaluated first. If you take a minute to review the results shown in this figure, you should quickly see how these two conditions differ.
Figure 3-11. How to use the AND, OR, and NOT logical operators
The syntax of the WHERE clause with logical operators
WHERE [NOT] search_condition_1 {AND|OR} [NOT] search_condition_2 ...
Examples of queries using logical operators
A search condition that uses the AND operator
WHERE VendorState = 'NJ' AND YTDPurchases > 200
A search condition that uses the OR operator
WHERE VendorState = 'NJ' OR YTDPurchases > 200
A search condition that uses the NOT operator
WHERE NOT (InvoiceTotal >= 5000 OR NOT InvoiceDate <= '2002-07-01')
The same condition rephrased to eliminate the NOT operator
WHERE InvoiceTotal < 5000 AND InvoiceDate <= '2002-07-01'
A compound condition without parentheses
WHERE InvoiceDate > '05/01/2002' OR InvoiceTotal > 500 AND InvoiceTotal - PaymentTotal - CreditTotal > 0
(91 rows)
The same compound condition with parentheses
WHERE (InvoiceDate > '05/01/2002' OR InvoiceTotal > 500) AND InvoiceTotal - PaymentTotal - CreditTotal > 0
(39 rows)
Description
You can use the AND and OR logical operators to create compound conditions that consist of two or more conditions. You use the AND operator to specify that the search must satisfy both of the conditions, and you use the OR operator to specify that the search must satisfy at least one of the conditions.
You can use the NOT operator to negate a condition. Because this operator can make the search condition unclear, you should rephrase the condition if possible so it doesn’t use NOT.
When SQL Server evaluates a compound condition, it evaluates the operators in this sequence: (1) NOT, (2) AND, and (3) OR. You can use parentheses to override this order of precedence or to clarify the sequence in which the operations will be evaluated.
Figure 3-12 shows how to code a WHERE clause that uses the IN operator. When you use this operator, the value of the test expression is compared with the list of expressions in the IN phrase. If the test expression is equal to one of the expressions in the list, the row is included in the query results. This is illustrated by the first example in this figure, which will return all rows whose TermsID column is equal to 1, 3, or 4.
You can also use the NOT operator with the IN phrase to test for a value that’s not in a list of expressions. This is illustrated by the second example in this figure. In this case, only those vendors who are not in California, Nevada, or Oregon are retrieved.
If you look at the syntax of the IN phrase shown at the top of this figure, you’ll see that you can code a subquery in place of a list of expressions. Subqueries are a powerful tool that you’ll learn about in detail in chapter 6. For now, though, you should know that a subquery is simply a SELECT statement within another statement. In the third example in this figure, for instance, a subquery is used to return a list of VendorID values for vendors who have invoices dated May 1, 2002. Then, the WHERE clause retrieves a vendor row only if the vendor is in that list. Note that for this to work, the subquery must return a single column, in this case, VendorID.
Figure 3-12. How to use the IN operator
The syntax of the WHERE clause with an IN phrase
WHERE test_expression [NOT] IN ({subquery|expression_1 [, expression_2]...})
Examples of the IN phrase
An IN phrase with a list of numeric literals
WHERE TermsID IN (1, 3, 4)
An IN phrase preceded by NOT
WHERE VendorState NOT IN ('CA', 'NV', 'OR')
An IN phrase with a subquery
WHERE VendorID IN (SELECT VendorID FROM Invoices WHERE InvoiceDate = '2002-05-01')
Description
You can use the IN phrase to test whether an expression is equal to a value in a list of expressions. Each of the expressions in the list must evaluate to the same type of data as the test expression.
The list of expressions can be coded in any order without affecting the order of the rows in the result set.
You can use the NOT operator to test for an expression that’s not in the list of expressions.
You can also compare the test expression to the items in a list returned by a subquery as illustrated by the third example above. You’ll learn more about coding subqueries in chapter 6.
MS SQL Server 
/ 6 
How to Retrieve Data from a Single Table
