How to Retrieve Data from a Single Table - How to Code the SELECT Clause
(Page 3 of 17 )
Figure 3 presents an expanded syntax for the SELECT clause. The keywords shown in the first line allow you to restrict the rows that are returned by a query. You'll learn how to code them in a few minutes. First, though, you'll learn various techniques for identifying which columns are to be included in a result set.
How to Code Column Specifications
Figure 3 summarizes the techniques you can use to code column specifications. You saw how to use some of these techniques in the previous figure. For example, you can code an asterisk in the SELECT clause to retrieve all of the columns in the base table, and you can code a list of column names separated by commas. Note that when you code an asterisk, the columns are returned in the order that they occur in the base table.
You can also code a column specification as an expression. For example, you can use an arithmetic expression to perform a calculation on two or more columns in the base table, and you can use a string expression to combine two or more string values. An expression can also include one or more functions. You'll learn more about each of these techniques in the topics that follow.
But first, you should know that when you code the SELECT clause, you should include only the columns you need. For example, you shouldn't code an asterisk to retrieve all the columns unless you need all the columns. That's because the amount of data that's retrieved can affect system performance. This is particularly important if you're developing SQL statements that will be used by application programs.
Figure 3: The expanded syntax of the SELECT clause
SELECT [ALL|DISTINCT] [TOP n [PERCENT] [WITH TIES]]
column_specification [[AS] result_column]
[, column_specification [[AS] result_column]] ...
Five ways to code column specifications
| Source | Option | Syntax |
| Base table value | All columns | * |
| Column name | column_name |
| Calculated value | Result of a calculation | Arithmetic expression (see figure 3-6) |
| Result of a concatenation | String expression (see figure 3-5) |
| Result of a function | Function (see figure 3-7) |
Column specifications that use base table values:
The * is used to retrieve all columns
SELECT *
Column names are used to retrieve specific columns
SELECT VendorName, VendorCity, VendorState
Column specifications that use calculated values
An arithmetic expression is used to calculate BalanceDue
SELECT InvoiceNumber,
InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue
A string expression is used to calculate FullName
SELECT VendorContactFName + ' ' + VendorContactLName AS FullName
A function is used to calculate CurrentDate
SELECT InvoiceNumber, InvoiceDate,
GETDATE() AS CurrentDate
Description
- Use SELECT * only when you need to retrieve all of the columns from a table. Otherwise, list the names of the columns you need.
- An expression is a combination of column names and operators that evaluate to a single value. In the SELECT clause, you can code arithmetic expressions, string expressions, and expressions that include one or more functions.
- After each column specification, you can code an AS clause to specify the name for the column in the result set. See figure 4 for details.
Note: The other elements shown in the syntax summary above let you control the number of rows that are returned by a query. You can use the ALL and DISTINCT keywords to determine whether or not duplicate rows are returned. And you can use the TOP clause to retrieve a specific number or percent of rows. See figures 8 and 9 for details.
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: How to Name the Columns in a Result Set >>
More MS SQL Server Articles
More By Bryan Syverson
