SELECT Statements - Microsoft SQL Server Black Book

SELECT statements do not modify data in any way. They are simply a method of looking at the data stored in a single table or in many related tables. SELECT statements do not generate a transaction in Microsoft SQL Server either. We’ll use the Authors table to illustrate some SELECT statements. See Listing 5.1 for the syntax.

NOTE: I recommend spending a few minutes in Microsoft SQL Server Books Online to

become familiar with all of the clauses in a SELECT statement. This time can mean the difference between hours and minutes trying to get a query to perform the way the user needs.

There are three keywords to keep in mind when retrieving data through SQL: SELECT, FROM, and WHERE. The SELECT list is the list of columns that you wish to return from the query. Depending on your query, these columns can be from one table or multiple tables. You can return all of the column values or manipulate the column to return a computed value, or you can combine column values to create something totally new. The FROM statement specifies the table, tables, or views from which you want to retrieve the data. The WHERE clause specifies which rows to include or exclude for your query’s output.

NOTE: Omitting the WHERE clause will return or manipulate all rows in the table. Be

careful to use this feature only when you really intend to query the entire table or view. Omitting WHERE places an extra burden on the server, so in production queries, always use a WHERE clause.

Listing 5.1 SELECT statement syntax.

SELECT [ALL | DISTINCT] select_list [INTO [new_table_name]]

[FROM {table_name | view_name}[(optimizer_hints)] [[, {table_name2 | view_name2}[(optimizer_hints)] […, {table_name16 | view_name16}[(optimizer_hints)]]] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [COMPUTE clause] [FOR BROWSE]

Listing 5.2 shows some basic queries. The first four example queries return the same columns and number of rows. As shown in Query 5, I can change the order of the columns or which columns are included to affect what the query returns. I prefer to use some formatting to help keep things clear and easy to change. With each column specified in the SELECT list, I can easily change the column order or add and remove columns with little problem. Notice in Query 2 that the long line of code, even with this basic query, is harder to read than the stacked code in Query 4. Use formatting to clarify your code.

Listing 5.2 Basic SQL queries.

/* Query 1 */

SELECT * FROM authors /* Query 2 */

SELECT au_id, au_lname, au_fname, address, city, state, zip, phone, contract FROM authors

/* Query 3 (Formatted) */ SELECT *

FROM authors

/* Query 4 (Formatted) */ SELECT au_id, au_lname, au_fname, address, city, state, zip, phone, contract FROM authors /* Query 5 (Formatted) */ SELECT au_fname, au_lname, address, city, state, zip FROM authors

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

I can use some string functions to manipulate the output to read exactly the way I need it to. For example, Listing 5.3 shows a basic query (Query 1) that will return the first name, last name, and phone number for each of our authors. Let’s apply some string functions and formatting to make this list a bit easier to read. For the sake of this example, we’ll assume that we want the list to show first initial and last name, with the area code of the phone number in parentheses.

Listing 5.3 Phone list example.

/* Query 1 (name and phone number) */ SELECT au_fname,

au_lname, phone FROM authors

/* Query 2 (first initial, name, phone) */ SELECT UPPER(SUBSTRING(au_fname,1,1)) + '.', au_lname,

phone FROM authors

/* Query 3 (first initial, name, formatted phone number) */ SELECT UPPER(SUBSTRING(au_fname,1,1)) + '.',

au_lname,

'(' + SUBSTRING(phone,1,3) + ') ' + SUBSTRING(phone,5,8) FROM authors

/* Query 4 (finished output with formatting and titles) */ SELECT 'Name' = UPPER(SUBSTRING(au_fname,1,1)) + '. ' + SUBSTRING(au_lname,1, 15),

'Phone' = '(' + SUBSTRING(phone,1,3) + ') ' + SUBSTRING(phone,5,8) FROM authors

Query 2 of Listing 5.3 shows the syntax. The UPPER function will convert the text to uppercase and the SUBSTRING function will return text beginning with the first character of the first name and including only one character. Notice the period after the first initial is added by using the string concatenation operator ‘+’. This allows for good formatting and user-friendly output. We are still only returning three columns’ worth of

http://www.itknowledge.com/reference/standard/1576101495/ch05/145-149.html (1 of 4) [1/27/2000 6:16:05 PM] Go!

Keyword

---

data to the client.

NOTE: For more detailed explanation of these or any other functions, check Microsoft SQL Server Books

Online.

Our next task is to format the phone number. This is accomplished the same way as for the first name, with a bit more string manipulation. Query 3 in Listing 5.3 shows the phone number formatting required. Now we’ll tell SQL Server that we wish to combine the first two columns into one column and restrict the length to a good value to limit wasted white space and change the titles displayed for each column to a user-friendly title instead of the column name in the table.

Functions and string manipulations are common in SQL code. These functions are very fast and make the output easy to read. See Listing 5.4 for the sample outputs for Queries 1 and 4 from Listing 5.3 to compare the reports. You can copy these queries and run them on your system to make sure the results match.

Listing 5.4 Sample output.

Query 1

au_fname au_lname phone

--- --- --- Johnson White 408 496-7223 Marjorie Green 415 986-7020 Cheryl Carson 415 548-7723 Michael O'Leary 408 286-2428 Dean Straight 415 834-2919 Query 4 Name Phone --- --- J. White (408) 496-7223 M. Green (415) 986-7020 C. Carson (415) 548-7723 M. O'Leary (408) 286-2428 D. Straight (415) 834-2919 M. Smith (913) 843-0462 A. Bennet (415) 658-9932

WHERE Clause

Staying with the same example data, we’ll now look at restricting the number of rows returned by our queries. This horizontal partitioning is done with the WHERE clause. Let’s assume for the sake of this example that we want to see only the authors that live in the 415 area code. As shown in Query 1 of Listing 5.5, we would add a WHERE clause to our query.

WHERE clauses use comparison operators to check the value of a column against another value to determine if it should be returned in the query. Using negative operators should be avoided since the Query Optimizer has a difficult time knowing if a record is not something. NOT is not optimizable! Most queries can be rewritten to take advantage of equalities rather than nonequalities.

Notice the same SUBSTRING() function is used, along with an equal sign and 415 in quotation marks. We could use a column name instead of the string function if we only wanted the authors in California. Or we could use Query 2 of Listing 5.5 to return just those records where the author lives in California. Notice in Query 2 that the WHERE clause uses a column that does not exist in the SELECT list. Listing the column in the SELECT list is not always required in your queries. However, it is required when using aggregate functions; we’ll cover these functions later in this chapter.

Listing 5.5 Horizontal partitioning of data.

/* Query 1 (only 415 area code) */

SELECT 'Name' = UPPER(SUBSTRING(au_fname,1,1)) + '. ' + SUBSTRING(au_lname,1, 15), 'Phone' = '(' + SUBSTRING(phone,1,3) + ') ' + SUBSTRING(phone,5,8) FROM authors WHERE SUBSTRING(phone,1,3) = '415'

/* Query 2 (California authors) */

SELECT 'Name' = UPPER(SUBSTRING(au_fname,1,1)) + '. ' + SUBSTRING(au_lname,1, 15), 'Phone' = '(' + SUBSTRING(phone,1,3) + ') ' + SUBSTRING(phone,5,8) FROM authors

WHERE state = 'CA'

When you execute these queries, you will notice different records returned that are still formatted by the functions in the SELECT list. These interchangeable WHERE clauses allow you to view the same data from a table, while restricting the row-matching and shortening the list considerably. See Listing 5.6 for the results of these two queries.

Listing 5.6 State and area code query sample output.

Query 1 Name Phone --- --- M. Green (415) 986-7020 C. Carson (415) 548-7723 D. Straight (415) 834-2919 A. Bennet (415) 658-9932 A. Dull (415) 836-7128 C. Locksley (415) 585-4620 A. Yokomoto (415) 935-4228 D. Stringer (415) 843-2991 S. MacFeather (415) 354-7128 L. Karsen (415) 534-9219 S. Hunter (415) 836-7128 (11 row(s) affected) Query 2 Name Phone --- --- J. White (408) 496-7223 M. Green (415) 986-7020 C. Carson (415) 548-7723 M. O'Leary (408) 286-2428 D. Straight (415) 834-2919 A. Bennet (415) 658-9932 A. Dull (415) 836-7128 B. Gringlesby (707) 938-6445 C. Locksley (415) 585-4620 A. Yokomoto (415) 935-4228 D. Stringer (415) 843-2991 S. MacFeather (415) 354-7128 L. Karsen (415) 534-9219 S. Hunter (415) 836-7128 H. McBadden (707) 448-4982 (15 row(s) affected) http://www.itknowledge.com/reference/standard/1576101495/ch05/145-149.html (3 of 4) [1/27/2000 6:16:05 PM]

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

With these simple queries, there are no real mysteries as to what should be indexed on the table. If you were selecting only certain states, you would want to place an index on the state column to get the fastest results. I will cover index strategies in Chapter 11. WHERE clauses are one of two critical areas for indexes. Depending on your WHERE clause, the Microsoft SQL Server Query Optimizer may decide that an indexed search for matching records would be faster; it would then create a temporary table to support your query. When your query is finished, Microsoft SQL Server will drop the temporary table because it is no longer needed.

You can imagine what happens to servers without proper indexes when a query is run over and over again. This is where performance typically is affected the most. The second and most critical area for index placement with regard to SELECT statements is the ORDER BY clause.

In document Microsoft SQL Server Black Book (Page 127-134)