The SELECT Statement

The SELECT statement is the most frequently used SQL command and is the fundamental way to query data. The syntax is intuitive ”at least in its simplest forms ”and resembles how you might state a request in English. As its name implies, however, SQL is not only intuitive but also structured and precise. But even though a SELECT statement can be intuitive, it can also be obscure or even tricky. Brainteasers showing how to write a SELECT statement to perform some tricky task often appear in database publications , and as long as the query represented is syntactically correct, you'll get a result. However, it might not be the result you really want ”you might get the answer to a different question than the one you thought you posed! Incorrectly stated queries (queries with incorrect semantics) commonly cause bugs in applications, so you should understand proper query formulation.

The basic form of SELECT, using brackets ([]) to identify optional items, appears below.

 SELECT [DISTINCT][TOP  n  ] <columns to be chosen, optionally                           eliminating duplicate rows from result set or                           limiting number of rows to be returned> [FROM] <table names> [WHERE] <criteria that must be true for a row to be chosen> [GROUP BY] <columns for grouping aggregate functions> [HAVING] <criteria that must be met for aggregate functions> [ORDER BY] <optional specification of how the results should be             sorted> 

Note that the only clause that must always be present is the verb SELECT; the other clauses are optional. For example, if the entire table is needed, data doesn't need to be restricted to certain criteria, so the WHERE clause isn't needed.

Here's a simple query that retrieves all columns of all rows from the authors table in the pubs sample database:

 select * from authors 

Figure 7-1 shows the output.

Using tools such as OSQL and Query Analyzer, you can issue queries interactively. You can also, of course, build queries into applications. The calling application determines the formatting of the data output. SQL Server returns the data back to the calling application, which then works with the data. In the case of an ad hoc query tool, it displays the output on a monitor.

Figure 7-1. The authors table in the pubs sample database.

More Information About the SQL Language

In general, this book concentrates on topics specific to Microsoft SQL Server, but it wouldn't be complete without some discussion of the SQL language. Because the treatment of generic SQL in this book is, by design, far from complete, here's a list of some of our favorite books about SQL. (For complete publication details and more references, see the "Suggested Reading" section at the end of this book.)

Using SQL, by James Groff and Paul Weinberg, is excellent for new SQL users looking for a good primer. Experienced users will probably want to use one of the other books mentioned here instead.

Understanding the New SQL: A Complete Guide, by Jim Melton and Alan R. Simon, is an excellent reference for ANSI SQL-92 semantics and conformance issues. (Jim Melton is an active participant in the SQL standards work and editor of the ANSI SQL-92 standard.) Although you can get the ANSI SQL-92 specification directly from ANSI, this book translates the standard into understandable English.

A Guide to the SQL Standard, by C. J. Date with Hugh Darwin, is similar in purpose and focus to the Melton and Simon book; however, this book's coverage is more compact, gives some additional insight into why something is the way it is, and provides more discussion of semantics. It's an especially good reference for issues about the use of NULL. You can use this book hand-in-hand with the Melton and Simon book, and you'll find that their differences complement each other well.

SQL for Smarties, by Joe Celko, is the book to consult for insight into subtle but powerful ways to write queries that are nonintuitive. In places, this one is truly the SQL book for the Mensa crowd ”it has many mind-bending puzzles about how to write an SQL query to perform some obscure task. It's loaded with examples, and you can often find a solution to a problem similar in scope to one you might face. (Note that the answers to the problems are nearly all formulated using only ANSI-standard SQL. In some cases, a SQL Server_specific extension would provide a more intuitive or more efficient solution.) Although it focuses on advanced topics, Celko's book also provides a comprehensive treatment of many query topics in an easy-to-read, conversational style. This book is underrated , but it's well worth a spot on the shelf of any SQL database developer.

The power of the SQL language begins to reveal itself when you limit the information to be returned to specified ranges. For example, you could specify that a query from one table return only certain columns or rows that meet your stated criteria. In the select list, you would specify the exact columns you want, and then in the WHERE clause, you would specify the criteria that determine whether a row should be included in the answer set. Still using the pubs sample database, suppose that we want to find the first name and the city, state, and zip code of residence for authors whose last name is Ringer :

 SELECT au_lname, au_fname, city, state, zip FROM authors WHERE au_lname='Ringer' 

Here's the output:

 au_lname    au_fname    city              state    zip --------    --------    --------------    -----    ----- Ringer      Albert      Salt Lake City    UT       84152 Ringer      Anne        Salt Lake City    UT       84152 

The results of this query tell us that two authors are named Ringer. But we're interested only in Anne. Retrieving only Anne Ringer's data requires an additional expression that is combined (AND'ed) with the original. In addition, we'd like the output to have more intuitive names for some columns, so we respecify the query:

 SELECT 'Last Name'=au_lname, 'First'=au_fname, city, state, zip FROM authors WHERE au_lname='Ringer' and au_fname='Anne' 

Here's the output, just as we wanted it:

 Last Name    First    city              state    zip ---------    -----    --------------    -----    ----- Ringer       Anne     Salt Lake City    UT       84152