Coding the SELECT Statement

In this third part of a nine-part article series on working with the SELECT statement, you'll learn how to code the SELECT clause, how to name the column in a results set, and more. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

By: Murach Publishing

Poor Best

Rating:

/ 0

July 21, 2011

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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 moment. But first, 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.

The expanded syntax of the SELECT clause

SELECT [ALL|DISTINCT] 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 concatenation	String expression (see figure 3-5)
	Result of a calculation	Arithmetic expression (see figure 3-6)
	Result of a scalar function	Scalar 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 vendor_name, vendor_city, vendor_state

Column specifications that use calculated values

An arithmetic expression is used to calculate balance_due

SELECT invoice_number, invoice_total - payment_total - credit_total AS balance_due

A string expression is used to derive full_name

SELECT first_name || ' ' || last_name AS full_name

Description

Use SELECT * only when you need to retrieve all 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 DISTINCT keyword to eliminate duplicate rows. See figure 3-9 for details.

--------------------------------------------Figure 3-3 How to code column specifications

By default, a column in a result set is given the same name as the column in the base table. You can specify a different name, however, 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 coding technique specified by the American National Standards Institute (ANSI, pronounced ‘ann-see’), and it’s illustrated by the first example in this figure.

The second technique is to code the column specification followed by a space and the column alias. This coding technique is illustrated by the second example. Whenever possible, though, you should use the first technique since the AS keyword makes it easier to identify the alias for the column, which makes your SQL statement easier to read and maintain.

When you code an alias, you must enclose the alias in double quotes if the alias contains a space or is a keyword that’s reserved by Oracle. In this figure, the first two examples specify an alias for the invoice_number column that uses two words with a space between them.

In addition, these two examples specify an alias for the invoice_date column that uses a keyword that’s reserved by Oracle: the DATE keyword. If you don’t enclose this keyword in double quotes, you will get an error when you attempt to execute either of these SQL statements. When you enter a statement into SQL Developer, it boldfaces keywords that are reserved by Oracle. This makes it easy to identify Oracle keywords when you’re writing SQL statements.

When you enclose an alias in double quotes, the result set uses the capitalization specified by the alias. Otherwise, the result set capitalizes all letters in the column name. In this figure, for instance, the first two columns in the first result set use the capitalization specified by the aliases. However, since no alias is specified for the third column, all letters in the name of this column are capitalized.

When you code a column that contains a calculated value, it’s a good practice to specify an alias for the calculated column. If you don’t, Oracle will assign the entire calculation as the name, which can be unwieldy, as shown in the third example. As a result, you usually assign a name to any column that’s calculated from other columns in the base table.

Two SELECT statements that name the columns in the result set

A SELECT statement that uses the AS keyword
-- DATE is a reserved keyword.
-- As a result, it must be enclosed in quotations.
SELECT invoice_number AS "Invoice Number", invoice_date AS "Date",
invoice_total AS total
FROM invoices

A SELECT statement that omits the AS keyword

SELECT invoice_number Invoice Number;, invoice_date "Date,
invoice_total total
FROM invoices

The result set for both SELECT statements

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

SELECT invoice_number, invoice_date, invoice_total,
invoice_total - payment_total - credit_total
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 for the column.
One way to name a column is to use the AS keyword as shown in the first example above. Although the AS keyword is optional, it enhances readability.
If an alias includes spaces or an Oracle reserved keyword, you must enclose it in double quotes.
When you enclose an alias in quotes, the result set uses the capitalization specified by the alias. Otherwise, the result set capitalizes all letters in the column name.

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

Please check back for the next part of this series.