Specifies which columns to retrieve by the SELECT statement.

Syntax

[ ALL | DISTINCT ]
  { * |{table_name|alias.}* [ , {table_name.|alias.} * ]...
       |expr[[ AS ][ ' ]column_title[ ' ]]
             [, expr[[ AS ][ ' ]column_alias[ ' ]]]...
       |[table|alias.]column_name , ...]
  }

Parameters

[ ALL | DISTINCT ]

Indicates whether a result table omits duplicate rows. ALL is the default and specifies that the result table includes all rows. DISTINCT specifies that a table omits duplicate rows.

* |{table_name. |alias. } *

Specifies that the result table includes all columns from all tables named in the FROM clause.

* expr[[ AS ][ ' ]column_alias[ ' ]]

Specifies a list of expressions, called a select list, whose results will form columns of the result table. Typically, the expression is a column name from a table named in the FROM clause. The expression can also be any supported mathematical expression, scalar function, or aggregate function that returns a value.

The optional column_alias argument specifies a new heading for the associated column in the result table. You can also use the column_title in an ORDER BY clause. Enclose the new title in single or double quotation marks if it contains spaces or other special characters, including hyphens.

Note: A table alias cannot be used to qualify a column alias. A column alias can only be used without a qualifier because it is not a part of any table definition.

[ table | alias.]column_name , ...]

Specifies a list columns from a particular table or alias.

Example

Both these statements return all the columns in the customer table to the select list:

SELECT * FROM Customer;
SELECT Customer.* FROM Customer;

The table_name.* syntax is useful when the select list refers to columns in multiple tables and you want to specify all the columns in one of those tables. For example:

SELECT Customer.CustNum, Customer.Name, Invoice.*
     FROM Customer, Invoice ;

The following example illustrates using the column_alias option to change the name of the column:

-- Illustrate optional 'column_title' syntax
SELECT
        FirstName AS 'First Name',
        LastName AS 'Last Name', 
        state AS 'New England State' 
FROM Employee
        WHERE state = 'NH' OR state = 'ME' OR state = 'MA'
OR state = 'VT' OR state = 'CT' OR state = 'RI';
 First Name       Last Name        New England State  
---------------- ---------------- ------------------ 
Justine          Smith            MA
Andy             Davis            MA
Marcy            Adams            MA
Larry            Dawsen           MA
John             Burton           NH
Mark             Hall             NH
Stacey           Smith            MA
Scott            Abbott           MA
Meredith         White            NH
Heather          White            NH 

You must qualify a column name if it occurs in more than one table specified in the FROM clause, as shown:

SELECT Customer.CustNum FROM Customer;

-- Table name qualifier required
-- Customer table has city and state columns
-- Billto table has city and state columns
 SELECT
        Customer.CustNum,
        Customer.City AS 'Customer City',
        Customer.State AS 'Customer State',
        Billto.City AS 'Bill City',
        Billto.State AS 'Bill State'
FROM Customer, Billto
        WHERE Customer.City = 'Clinton';
  CustNum Customer City    Customer State   Bill City        Bill State       
-------- ---------------- ---------------- ------------------------------- 
  1272     Clinton          MS               Montgomery         AL              
  1272     Clinton          MS               Atlanta            GA 
  1421     Clinton          SC               Montgomery         AL 
  1421     Clinton          SC               Atlanta            GA 
  1489     Clinton          OK               Montgomery         AL 
  1489     Clinton          OK               Atlanta            GA

When there is a conflict between a SELECT list alias and an actual database column, OpenEdge SQL interprets the reference as the database column. Note the following example:

SELECT substring (state, 1, 2) state, sum (balance)
FROM pub.customer
GROUP BY state;

In the above query, state is ambiguous because it can refer to either database column pub.customer.state or the result of the substring scalar function in the SELECT list. The ANSI standard requires that state refers unambiguously to the database column, therefore, the query groups the result by the database column. The same principle holds true for ambiguous references that appear in WHERE, ON, and HAVING clauses.