How to Receive Data from a Single Table - How to code the SELECT clause

(Page 3 of 6 )


 
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. 

Figure 3-3.   How to code column specifications

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  

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

1. Use SELECT * only when you need to retrieve all of the columns from a table. Otherwise, list the names of the columns you need.
   
2. 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.
   
3. 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.

Next: How to name the columns in a result set >>
 

More MS SQL Server Articles
More By Murach Publishing

Comments On: How to Receive Data from a Single Table

· This article is an excerpt from the book "Murach's SQL for SQL Server," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162). Check it out today at your favorite bookstore. Buy this book now.