Chapter 3: How to retrieve data from a single table

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table.

How to code the SELECT clause

Figure 3-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-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.

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 3-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 3-8 and 3-9 for details.

---

Figure 3-3 How to code column specifications