1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>7.3.Select Lists</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="queries.html" title="Chapter7.Queries">
<link rel="prev" href="queries-table-expressions.html" title="7.2.Table Expressions">
<link rel="next" href="queries-union.html" title="7.4.Combining Queries">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="queries-select-lists"></a>7.3.Select Lists</h2></div></div></div>
<a name="id582383"></a><p> As shown in the previous section,
the table expression in the <code class="command">SELECT</code> 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 <em class="firstterm">select list</em>. The select
list determines which <span class="emphasis"><em>columns</em></span> of the
intermediate table are actually output.
</p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="queries-select-list-items"></a>7.3.1.Select-List Items</h3></div></div></div>
<a name="id582424"></a><p> The simplest kind of select list is <code class="literal">*</code> which
emits all columns that the table expression produces. Otherwise,
a select list is a comma-separated list of value expressions (as
defined in <a href="sql-expressions.html" title="4.2.Value Expressions">Section4.2, “Value Expressions”</a>). For instance, it
could be a list of column names:
</p>
<pre class="programlisting">SELECT a, b, c FROM ...</pre>
<p>
The columns names <code class="literal">a</code>, <code class="literal">b</code>, and <code class="literal">c</code>
are either the actual names of the columns of tables referenced
in the <code class="literal">FROM</code> clause, or the aliases given to them as
explained in <a href="queries-table-expressions.html#queries-table-aliases" title="7.2.1.2.Table and Column Aliases">Section7.2.1.2, “Table and Column Aliases”</a>. The name
space available in the select list is the same as in the
<code class="literal">WHERE</code> clause, unless grouping is used, in which case
it is the same as in the <code class="literal">HAVING</code> clause.
</p>
<p> If more than one table has a column of the same name, the table
name must also be given, as in
</p>
<pre class="programlisting">SELECT tbl1.a, tbl2.a, tbl1.b FROM ...</pre>
<p>
When working with multiple tables, it can also be useful to ask for
all the columns of a particular table:
</p>
<pre class="programlisting">SELECT tbl1.*, tbl2.a FROM ...</pre>
<p>
(See also <a href="queries-table-expressions.html#queries-where" title="7.2.2.The WHERE Clause">Section7.2.2, “The <code class="literal">WHERE</code> Clause”</a>.)
</p>
<p> 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 <code class="literal">FROM</code> clause;
they could be constant arithmetic expressions as well, for
instance.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="queries-column-labels"></a>7.3.2.Column Labels</h3></div></div></div>
<a name="id582559"></a><p> The entries in the select list can be assigned names for further
processing. The “<span class="quote">further processing</span>” in this case is
an optional sort specification and the client application (e.g.,
column headers for display). For example:
</p>
<pre class="programlisting">SELECT a AS value, b + c AS sum FROM ...</pre>
<p>
</p>
<p> If no output column name is specified using <code class="literal">AS</code>, 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.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> The naming of output columns here is different from that done in
the <code class="literal">FROM</code> clause (see <a href="queries-table-expressions.html#queries-table-aliases" title="7.2.1.2.Table and Column Aliases">Section7.2.1.2, “Table and Column Aliases”</a>). 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.
</p>
</div>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="queries-distinct"></a>7.3.3.<code class="literal">DISTINCT</code></h3></div></div></div>
<a name="id582638"></a><a name="id582648"></a><p> After the select list has been processed, the result table may
optionally be subject to the elimination of duplicate rows. The
<code class="literal">DISTINCT</code> key word is written directly after
<code class="literal">SELECT</code> to specify this:
</p>
<pre class="synopsis">SELECT DISTINCT <em class="replaceable"><code>select_list</code></em> ...</pre>
<p>
(Instead of <code class="literal">DISTINCT</code> the key word <code class="literal">ALL</code>
can be used to specify the default behavior of retaining all rows.)
</p>
<p> <a name="id582706"></a>
Obviously, two rows are considered distinct if they differ in at
least one column value. Null values are considered equal in this
comparison.
</p>
<p> Alternatively, an arbitrary expression can determine what rows are
to be considered distinct:
</p>
<pre class="synopsis">SELECT DISTINCT ON (<em class="replaceable"><code>expression</code></em> [<span class="optional">, <em class="replaceable"><code>expression</code></em> ...</span>]) <em class="replaceable"><code>select_list</code></em> ...</pre>
<p>
Here <em class="replaceable"><code>expression</code></em> is an arbitrary value
expression that is evaluated for all rows. A set of rows for
which all the expressions are equal are considered duplicates, and
only the first row of the set is kept in the output. Note that
the “<span class="quote">first row</span>” of a set is unpredictable unless the
query is sorted on enough columns to guarantee a unique ordering
of the rows arriving at the <code class="literal">DISTINCT</code> filter.
(<code class="literal">DISTINCT ON</code> processing occurs after <code class="literal">ORDER
BY</code> sorting.)
</p>
<p> The <code class="literal">DISTINCT ON</code> clause is not part of the SQL standard
and is sometimes considered bad style because of the potentially
indeterminate nature of its results. With judicious use of
<code class="literal">GROUP BY</code> and subqueries in <code class="literal">FROM</code> the
construct can be avoided, but it is often the most convenient
alternative.
</p>
</div>
</div></body>
</html>
|
