As shown in the previous section,
the table expression in the SELECT command
constructs an intermediate virtual table by possibly combining
tables, views, eliminating rows, grouping, etc. This table is
finally passed on to processing by the select list. The select
list determines which columns of the
intermediate table are actually output.
The simplest kind of select list is * which
emits all columns that the table expression produces. Otherwise,
a select list is a comma-separated list of value expressions (as
defined in Section 3.2). For instance, it
could be a list of column names:
SELECT a, b, c FROM ...
The columns names a, b, and c
are either the actual names of the columns of tables referenced
in the FROM clause, or the aliases given to them as
explained in Section 6.2.1.2. The name
space available in the select list is the same as in the
WHERE clause, unless grouping is used, in which case
it is the same as in the HAVING clause.
If more than one table has a column of the same name, the table
name must also be given, as in
SELECT tbl1.a, tbl2.a, tbl1.b FROM ...
When working with multiple tables, it can also be useful to ask for
all the columns of a particular table:
SELECT tbl1.*, tbl2.a FROM ...
(See also Section 6.2.2.)
If an arbitrary value expression is used in the select list, it
conceptually adds a new virtual column to the returned table. The
value expression is evaluated once for each result row, with
the row's values substituted for any column references. But the
expressions in the select list do not have to reference any
columns in the table expression of the FROM clause;
they could be constant arithmetic expressions as well, for
instance.
The entries in the select list can be assigned names for further
processing. The "further processing" in this case is
an optional sort specification and the client application (e.g.,
column headers for display). For example:
SELECT a AS value, b + c AS sum FROM ...
If no output column name is specified using AS, the system assigns a
default name. For simple column references, this is the name of the
referenced column. For function
calls, this is the name of the function. For complex expressions,
the system will generate a generic name.
Note: The naming of output columns here is different from that done in
the FROM clause (see Section 6.2.1.2). This pipeline will in fact
allow you to rename the same column twice, but the name chosen in
the select list is the one that will be passed on.
After the select list has been processed, the result table may
optionally be subject to the elimination of duplicates. The
DISTINCT key word is written directly after the
SELECT to enable this:
SELECT DISTINCT select_list ...
(Instead of DISTINCT the key word ALL
can be used to select the default behavior of retaining all rows.)
Obviously, two rows are considered distinct if they differ in at
least one column value. Null values are considered equal in this
comparison.