How to Retrieve Data from a Single Table
(Page 1 of 17 )
In this chapter, "How to Retrieve Data from a Single Table", you’ll learn how to code SELECT statements that retrieve data from a single table. You should realize, though, that the skills covered here are the essential ones that apply to any SELECT statement you code…no matter how many tables it operates on, no matter how complex the retrieval. So you’ll want to be sure you have a good understanding of the material in this chapter before you go on to the chapters that follow. This comes from Chapter 3 by Bryan Syverson in
Murach's SQL for SQL Server (Murach, ISBN 1-890774-16-2, 2002).
An Introduction to the SELECT Statement
To help you learn to code SELECT statements, this chapter starts by presenting its basic syntax. Next, it presents several examples that will give you an idea of what you can do with this statement. Then, the rest of this chapter will teach you the details of coding this statement.
The Basic Syntax of the SELECT Statement
Figure 1 presents the basic syntax of the SELECT statement. The syntax summary at the top of this figure uses conventions that are similar to those used in other programming manuals. Capitalized words are keywords that you have to type exactly as shown. In contrast, you have to provide replacements for the lowercase words. For example, you can enter a list of columns in place of select_list, and you can enter a table name in place of table_source.
Figure 1: The Basic Syntax of the SELECT Statement
SELECT select_list
FROM table_source
[WHERE search_condition]
[ORDER BY order_by_list]
Beyond that, you can choose between the items in a syntax summary that are separated by pipes (|) and enclosed in braces ({}) or brackets ([]). And you can omit items enclosed in brackets. If you have a choice between two or more optional items, the default item is underlined. And if an element can be coded multiple times in a statement, it's followed by an ellipsis (...). You'll see examples of pipes, braces, default values, and ellipses in syntax summaries later in this chapter. For now, if you compare the syntax in this figure with the coding examples in the next figure, you should easily see how the two are related.
The syntax summary in this figure has been simplified so that you can focus on the four main clauses of the SELECT statement: SELECT, FROM, WHERE, and ORDER BY. Most of the SELECT statements you code will contain all four of these clauses. However, only the SELECT and FROM clauses are required.
The SELECT clause is always the first clause in a SELECT statement. It identifies the columns that will be included in the result set. These columns are retrieved from the base tables named in the FROM clause. Since this chapter focuses on retrieving data from a single table, the FROM clauses in all of the statements you'll see in this chapter name a single base table. In the next chapter, though, you'll learn how to retrieve data from two or more tables.
The WHERE and ORDER BY clauses are optional. The ORDER BY clause determines how the rows in the result set are sorted, and the WHERE clause determines which rows in the base table are included in the result set. The WHERE clause specifies a search condition that's used to filter the rows in the base table. This search condition can consist of one or more Boolean expressions, or predicates. A Boolean expression is an expression that results in a value of True, False, or Unknown. When all the Boolean expressions in a search condition are true, the row is included in the result set.
In this book, I won't use the term "predicate" because I don't think it clearly describes the content of the WHERE clause. Instead, I'll use the term "search condition" or "Boolean expression" to refer to an expression that results in a True or False value.
The four clauses of the SELECT statement
| Clause | Description |
| SELECT | Describes the columns that will be included in the result set. |
| FROM | Names the table from which the query will retrieve the data. |
| WHERE | Specifies the conditions that must be met for a row to be included in the result set. This clause is optional. |
| ORDER BY | Specifies how the rows in the result set will be sorted. This clause is optional. |
Description
- You use the basic SELECT statement shown above to retrieve the columns specified in the SELECT clause from the base table specified in the FROM clause and store them in a result set.
- The WHERE clause is used to filter the rows in the base table so that only those rows that match the search condition are included in the result set. If you omit the WHERE clause, all of the rows in the base table are included.
- The search condition of a WHERE clause consists of one or more Boolean expressions, or predicates, that result in a value of True, False, or Unknown. If the combination of all the expressions is True, the row being tested is included in the result set. Otherwise, it's not.
- If you include the ORDER BY clause, the rows in the result set are sorted in the specified sequence. Otherwise, the rows are returned in the same order as they appear in the base table. In most cases, that means that they're returned in primary key sequence.
Note: The syntax shown above does not include all of the clauses of the SELECT statement. You'll learn about the other clauses later in this book.
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: SELECT Statement Examples >>
More MS SQL Server Articles
More By Bryan Syverson
