How to Receive Data from a Single Table

If you have ever wondered how to code SELECT statements, this article is for you. It is the first of three parts, excerpted from chapter three of the book Murach’s SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162).

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

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.

Figure 3-1.   The basic syntax of the SELECT statement

The basic syntax of the SELECT statement

  SELECT select_list
  FROM table_source
  [WHERE search_condition]
  [ORDER BY order_by_list]

The four clauses of the SELECT statement

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.

{mospagebreak title=SELECT statement examples}
 
Figure 3-2 presents five SELECT statement examples. All of these statements retrieve data from the Invoices table. If you aren’t already familiar with this table, you should use the Enterprise Manager as described in the last chapter to review its definition.

The first statement in this figure retrieves all of the rows and columns from the Invoices table. Here, an asterisk (*) is used as a shorthand to indicate that all of the columns should be retrieved, and the WHERE clause is omitted so that there are no conditions on the rows that are retrieved. Notice that this statement doesn’t include an ORDER BY clause, so the rows are in primary key sequence. You can see the results following this statement as they’re displayed by the Query Analyzer. Notice that both horizontal and vertical scroll bars are displayed, indicating that the result set contains more rows and columns than can be displayed on the screen at one time.

The second statement retrieves selected columns from the Invoices table. As you can see, the columns to be retrieved are listed in the SELECT clause. Like the first statement, this statement doesn’t include a WHERE clause, so all the rows are retrieved. Then, the ORDER BY clause causes the rows to be sorted by the InvoiceTotal column in descending sequence.

The third statement also lists the columns to be retrieved. In this case, though, the last column is calculated from two columns in the base table, CreditTotal and PaymentTotal, and the resulting column is given the name TotalCredits. In addition, the WHERE clause specifies that only the invoice whose InvoiceID column has a value of 17 should be retrieved.

The fourth SELECT statement includes a WHERE clause whose condition specifies a range of values. In this case, only invoices with invoice dates between 05/01/2002 and 05/31/2002 will be retrieved. In addition, the rows in the result set will be sorted by invoice date.

The last statement in this figure shows another variation of the WHERE clause. In this case, only those rows with invoice totals greater than 50,000 are retrieved. Notice that since none of the rows in the Invoices table satisfy this condition, the result set is empty.

Figure 3-2.   SELECT statement examples

A SELECT statement that retrieves all the data from the Invoices table

  SELECT *
 FROM Invoices
 


  (114 rows)

A SELECT statement that retrieves three columns from each row, sorted in descending sequence by invoice total

  SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
  FROM Invoices
  ORDER BY InvoiceTotal DESC


  (114 rows)

A SELECT statement that retrieves two columns and a calculated value for a specific invoice

  SELECT InvoiceID, InvoiceTotal, CreditTotal + PaymentTotal AS TotalCredits
  FROM Invoices


  WHERE InvoiceID = 17

A SELECT statement that retrieves all invoices between given dates

  SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
  FROM Invoices
  WHERE InvoiceDate BETWEEN ‘2002-05-01’ AND ‘2002-05-31’
  ORDER BY InvoiceDate


  (70 rows)

A SELECT statement that returns an empty result set

  SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
  FROM Invoices
  WHERE InvoiceTotal > 50000

{mospagebreak title=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. 

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.

{mospagebreak title=How to name the columns in a result set}
 
By default, a column in a result set is given the same name as the column in the base table. However, you can specify a different name if you need to. You can also name a column that contains a calculated value. When you do that, the new column name is called a column alias. Figure 3-4 presents two techniques for creating column aliases.

The first technique is to code the column specification followed by the AS keyword and the column alias. This is the ANSI-standard coding technique, and it’s illustrated by the first example in this figure. Here, a space is added between the two words in the name of the InvoiceNumber column, the InvoiceDate column is changed to just Date, and the InvoiceTotal column is changed to Total. Notice that because a space is included in the name of the first column, it’s enclosed in brackets ([]). As you’ll learn in chapter 10, any name that doesn’t follow SQL Server’s rules for naming objects must be enclosed in either brackets or double quotes. Column aliases can also be enclosed in single quotes.

The second example in this figure illustrates another technique for creating a column alias. Here, the column is assigned to an alias using an equal sign. This technique is available only on SQL Server and is included for compatibility with earlier versions of SQL Server. So although you may see this technique used in older code, I don’t recommend it for new statements you write.

The third example in this figure illustrates what happens when you don’t assign an alias to a calculated column. As you can see, no name is assigned to the column, which usually isn’t what you want. So you should always be sure to assign a name to any column that’s calculated from other columns in the base table.

Figure 3-4.  How to name the columns in a result set

Two SELECT statements that name the columns in the result set

A SELECT statement that uses the AS keyword (the preferred technique)

  SELECT InvoiceNumber AS [Invoice Number], InvoiceDate AS Date,
      InvoiceTotal AS Total
 
FROM Invoices

A SELECT statement that uses the equal operator (an older technique)

  SELECT [Invoice Number] = InvoiceNumber, Date = InvoiceDate, 
      Total = InvoiceTotal
 
FROM Invoices

The result set for both SELECT statements

A SELECT statement that doesn’t provide a name for a calculated column

  SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
      InvoiceTotal – PaymentTotal – CreditTotal
  FROM Invoices

Description

  • By default, a column in the result set is given the same name as the column in the base table. If that’s not what you want, you can specify a column alias or substitute name for the column.
  • One way to name a column is to use the AS phrase as shown in the first example above. Although the AS keyword is optional, I recommend you code it for readability.
  • Another way to name a column is to code the name followed by an equal sign and the column specification as shown in the second example above. This syntax is unique to Transact-SQL.
  • You should always specify an alias for a column that contains a calculated value. If you don’t, no name is assigned to it as shown in the third example above.
  • If an alias includes spaces or special characters, you must enclose it in double quotes or brackets ([]). That’s true of all names you use in Transact-SQL. SQL Server also lets you enclose column aliases in single quotes for compatibility with earlier releases.

{mospagebreak title=How to code string expressions}
 
A string expression consists of a combination of one or more character columns and literal values. To combine, or concatenate, the columns and values, you use the concatenation operator (+). This is illustrated by the examples in figure 3-5.

The first example shows how to concatenate the VendorCity and VendorState columns in the Vendors table. Notice that because no alias is assigned to this column, it doesn’t have a name in the result set. Also notice that the data in the VendorState column appears immediately after the data in the VendorCity column in the results. That’s because of the way VendorCity is defined in the database. Because it’s defined as a variable-length column (the varchar data type), only the actual data in the column is included in the result. In contrast, if the column had been defined with a fixed length, any spaces following the name would have been included in the result. You’ll learn about data types and how they affect the data in your result set in chapter 8.

The second example shows how to format a string expression by adding spaces and punctuation. Here, the VendorCity column is concatenated with a string literal, or string constant, that contains a comma and a space. Then, the VendorState column is concatenated with that result, followed by a string literal that contains a single space and the VendorZipCode column.

Occasionally, you may need to include a single quotation mark or an apostrophe within a literal string. If you simply type a single quote, however, the system will misinterpret it as the end of the literal string. As a result, you must code two quotation marks in a row. This is illustrated by the third example in this figure.

How to concatenate string data

Figure 3-5.   How to code string expressions

  SELECT VendorCity, VendorState, VendorCity + VendorState
  FROM Vendors

How to format string data using literal values

  SELECT VendorName,
     
VendorCity + ‘, ‘ + VendorState + ‘ ‘ + VendorZipCode AS Address
  FROM Vendors

How to include apostrophes in literal values

  SELECT VendorName + ”’s Address: ‘,
     
VendorCity + ‘, ‘ + VendorState + ‘ ‘ + VendorZipCode
  FROM Vendors

Description

  • A string expression can consist of one or more character columns, one or more literal values, or a combination of character columns and literal values.
  • The columns specified in a string expression must contain string data (that means they’re defined with the char or varchar data type).
  • The literal values in a string expression also contain string data, so they can be called string literals or string constants. To create a literal value, enclose one or more characters within single quotation marks ( ’ ).
  • You can use the concatenation operator (+) to combine columns and literals in a string expression.
  • You can include a single quote within a literal value by coding two single quotation marks as shown in the third example above.

{mospagebreak title=How to code arithmetic expressions}
 
Figure 3-6 shows how to code arithmetic expressions. To start, it summarizes the five arithmetic operators you can use in this type of expression. Then, it presents three examples that illustrate how you use these operators.

The SELECT statement in the first example includes an arithmetic expression that calculates the balance due for an invoice. This expression subtracts the PaymentTotal and CreditTotal columns from the InvoiceTotal column. The resulting column is given the name BalanceDue.

When SQL Server evaluates an arithmetic expression, it performs the operations from left to right based on the order of precedence. This order says that multiplication, division, and modulo operations are done first, followed by addition and subtraction. If that’s not what you want, you can use parentheses to specify how you want an expression evaluated. Then, the expressions in the innermost sets of parentheses are evaluated first, followed by the expressions in outer sets of parentheses. Within each set of parentheses, the expression is evaluated from left to right in the order of precedence. Of course, you can also use parentheses to clarify an expression even if they’re not needed for the expression to be evaluated properly.

To illustrate how parentheses and the order of precedence affect the evaluation of an expression, consider the second example in this figure. Here, the expressions in the second and third columns both perform the same operations. When SQL Server evaluates the expression in the second column, it performs the multiplication operation before the addition operation because multiplication comes before addition in the order of precedence. When SQL Server evaluates the expression in the third column, however, it performs the addition operation first because it’s enclosed in parentheses. As you can see in the result set shown here, these two expressions result in different values.

Although you’re probably familiar with the addition, subtraction, multiplication, and division operators, you may not be familiar with the modulo operator. This operator returns the remainder of a division of two integers. This is illustrated in the third example in this figure. Here, the second column contains an expression that returns the quotient of a division operation. Note that the result of the division of two integers is always an integer. You’ll learn more about that in chapter 8. The third column contains an expression that returns the remainder of the division operation. If you study this example for a minute, you should quickly see how this works.

The arithmetic operators in order of precedence

Figure 3-6.   How to code arithmetic  expressions

*          Multiplication
/           Division
%         Modulo (Remainder)
+        Addition
–        Subtraction

A SELECT statement that calculates the balance due

  SELECT InvoiceTotal, PaymentTotal, CreditTotal,
      InvoiceTotal – PaymentTotal – CreditTotal AS BalanceDue
  FROM Invoices

A SELECT statement that uses parentheses to control the sequence of operations

  SELECT InvoiceID,
      InvoiceID + 7 * 3 AS OrderOfPrecedence,
      (InvoiceID + 7) * 3 AS AddFirst
  FROM Invoices

A SELECT statement that uses the modulo operator

  SELECT InvoiceID,
      InvoiceID / 10 AS Quotient,
      InvoiceID % 10 AS Remainder
 
FROM Invoices

Description

  • Unless parentheses are used, the operations in an expression take place from left to right in the order of precedence. For arithmetic expressions, multiplication, division, and modulo operations are done first, followed by addition and subtraction.
  • Whenever necessary, you can use parentheses to clarify or override the sequence of operations. Then, the operations in the innermost sets of parentheses are done first, followed by the operations in the next sets, and so on.

One thought on “How to Receive Data from a Single Table

  1. This article is an excerpt from the book “Murach’s SQL for SQL Server,” published by Murach. We hope you found it to be enjoyable and educational. Please let us know what you thought of it, and if you would like to see more content of this nature.

[gp-comments width="770" linklove="off" ]