| 1 |
<!-- doc/src/sgml/queries.sgml --> |
| 2 |
|
| 3 |
<chapter id="queries"> |
| 4 |
<title>Queries</title> |
| 5 |
|
| 6 |
<indexterm zone="queries"> |
| 7 |
<primary>query</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<indexterm zone="queries"> |
| 11 |
<primary>SELECT</primary> |
| 12 |
</indexterm> |
| 13 |
|
| 14 |
<para> |
| 15 |
The previous chapters explained how to create tables, how to fill |
| 16 |
them with data, and how to manipulate that data. Now we finally |
| 17 |
discuss how to retrieve the data from the database. |
| 18 |
</para> |
| 19 |
|
| 20 |
|
| 21 |
<sect1 id="queries-overview"> |
| 22 |
<title>Overview</title> |
| 23 |
|
| 24 |
<para> |
| 25 |
The process of retrieving or the command to retrieve data from a |
| 26 |
database is called a <firstterm>query</firstterm>. In SQL the |
| 27 |
<link linkend="sql-select"><command>SELECT</command></link> command is |
| 28 |
used to specify queries. The general syntax of the |
| 29 |
<command>SELECT</command> command is |
| 30 |
<synopsis> |
| 31 |
<optional>WITH <replaceable>with_queries</replaceable></optional> SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> <optional><replaceable>sort_specification</replaceable></optional> |
| 32 |
</synopsis> |
| 33 |
The following sections describe the details of the select list, the |
| 34 |
table expression, and the sort specification. <literal>WITH</literal> |
| 35 |
queries are treated last since they are an advanced feature. |
| 36 |
</para> |
| 37 |
|
| 38 |
<para> |
| 39 |
A simple kind of query has the form: |
| 40 |
<programlisting> |
| 41 |
SELECT * FROM table1; |
| 42 |
</programlisting> |
| 43 |
Assuming that there is a table called <literal>table1</literal>, |
| 44 |
this command would retrieve all rows and all user-defined columns from |
| 45 |
<literal>table1</literal>. (The method of retrieval depends on the |
| 46 |
client application. For example, the |
| 47 |
<application>psql</application> program will display an ASCII-art |
| 48 |
table on the screen, while client libraries will offer functions to |
| 49 |
extract individual values from the query result.) The select list |
| 50 |
specification <literal>*</literal> means all columns that the table |
| 51 |
expression happens to provide. A select list can also select a |
| 52 |
subset of the available columns or make calculations using the |
| 53 |
columns. For example, if |
| 54 |
<literal>table1</literal> has columns named <literal>a</literal>, |
| 55 |
<literal>b</literal>, and <literal>c</literal> (and perhaps others) you can make |
| 56 |
the following query: |
| 57 |
<programlisting> |
| 58 |
SELECT a, b + c FROM table1; |
| 59 |
</programlisting> |
| 60 |
(assuming that <literal>b</literal> and <literal>c</literal> are of a numerical |
| 61 |
data type). |
| 62 |
See <xref linkend="queries-select-lists"/> for more details. |
| 63 |
</para> |
| 64 |
|
| 65 |
<para> |
| 66 |
<literal>FROM table1</literal> is a simple kind of |
| 67 |
table expression: it reads just one table. In general, table |
| 68 |
expressions can be complex constructs of base tables, joins, and |
| 69 |
subqueries. But you can also omit the table expression entirely and |
| 70 |
use the <command>SELECT</command> command as a calculator: |
| 71 |
<programlisting> |
| 72 |
SELECT 3 * 4; |
| 73 |
</programlisting> |
| 74 |
This is more useful if the expressions in the select list return |
| 75 |
varying results. For example, you could call a function this way: |
| 76 |
<programlisting> |
| 77 |
SELECT random(); |
| 78 |
</programlisting> |
| 79 |
</para> |
| 80 |
</sect1> |
| 81 |
|
| 82 |
|
| 83 |
<sect1 id="queries-table-expressions"> |
| 84 |
<title>Table Expressions</title> |
| 85 |
|
| 86 |
<indexterm zone="queries-table-expressions"> |
| 87 |
<primary>table expression</primary> |
| 88 |
</indexterm> |
| 89 |
|
| 90 |
<para> |
| 91 |
A <firstterm>table expression</firstterm> computes a table. The |
| 92 |
table expression contains a <literal>FROM</literal> clause that is |
| 93 |
optionally followed by <literal>WHERE</literal>, <literal>GROUP BY</literal>, and |
| 94 |
<literal>HAVING</literal> clauses. Trivial table expressions simply refer |
| 95 |
to a table on disk, a so-called base table, but more complex |
| 96 |
expressions can be used to modify or combine base tables in various |
| 97 |
ways. |
| 98 |
</para> |
| 99 |
|
| 100 |
<para> |
| 101 |
The optional <literal>WHERE</literal>, <literal>GROUP BY</literal>, and |
| 102 |
<literal>HAVING</literal> clauses in the table expression specify a |
| 103 |
pipeline of successive transformations performed on the table |
| 104 |
derived in the <literal>FROM</literal> clause. All these transformations |
| 105 |
produce a virtual table that provides the rows that are passed to |
| 106 |
the select list to compute the output rows of the query. |
| 107 |
</para> |
| 108 |
|
| 109 |
<sect2 id="queries-from"> |
| 110 |
<title>The <literal>FROM</literal> Clause</title> |
| 111 |
|
| 112 |
<para> |
| 113 |
The <link linkend="sql-from"><literal>FROM</literal></link> clause derives a |
| 114 |
table from one or more other tables given in a comma-separated |
| 115 |
table reference list. |
| 116 |
<synopsis> |
| 117 |
FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_reference</replaceable> <optional>, ...</optional></optional> |
| 118 |
</synopsis> |
| 119 |
|
| 120 |
A table reference can be a table name (possibly schema-qualified), |
| 121 |
or a derived table such as a subquery, a <literal>JOIN</literal> construct, or |
| 122 |
complex combinations of these. If more than one table reference is |
| 123 |
listed in the <literal>FROM</literal> clause, the tables are cross-joined |
| 124 |
(that is, the Cartesian product of their rows is formed; see below). |
| 125 |
The result of the <literal>FROM</literal> list is an intermediate virtual |
| 126 |
table that can then be subject to |
| 127 |
transformations by the <literal>WHERE</literal>, <literal>GROUP BY</literal>, |
| 128 |
and <literal>HAVING</literal> clauses and is finally the result of the |
| 129 |
overall table expression. |
| 130 |
</para> |
| 131 |
|
| 132 |
<indexterm> |
| 133 |
<primary>ONLY</primary> |
| 134 |
</indexterm> |
| 135 |
|
| 136 |
<para> |
| 137 |
When a table reference names a table that is the parent of a |
| 138 |
table inheritance hierarchy, the table reference produces rows of |
| 139 |
not only that table but all of its descendant tables, unless the |
| 140 |
key word <literal>ONLY</literal> precedes the table name. However, the |
| 141 |
reference produces only the columns that appear in the named table |
| 142 |
— any columns added in subtables are ignored. |
| 143 |
</para> |
| 144 |
|
| 145 |
<para> |
| 146 |
Instead of writing <literal>ONLY</literal> before the table name, you can write |
| 147 |
<literal>*</literal> after the table name to explicitly specify that descendant |
| 148 |
tables are included. There is no real reason to use this syntax any more, |
| 149 |
because searching descendant tables is now always the default behavior. |
| 150 |
However, it is supported for compatibility with older releases. |
| 151 |
</para> |
| 152 |
|
| 153 |
<sect3 id="queries-join"> |
| 154 |
<title>Joined Tables</title> |
| 155 |
|
| 156 |
<indexterm zone="queries-join"> |
| 157 |
<primary>join</primary> |
| 158 |
</indexterm> |
| 159 |
|
| 160 |
<para> |
| 161 |
A joined table is a table derived from two other (real or |
| 162 |
derived) tables according to the rules of the particular join |
| 163 |
type. Inner, outer, and cross-joins are available. |
| 164 |
The general syntax of a joined table is |
| 165 |
<synopsis> |
| 166 |
<replaceable>T1</replaceable> <replaceable>join_type</replaceable> <replaceable>T2</replaceable> <optional> <replaceable>join_condition</replaceable> </optional> |
| 167 |
</synopsis> |
| 168 |
Joins of all types can be chained together, or nested: either or |
| 169 |
both <replaceable>T1</replaceable> and |
| 170 |
<replaceable>T2</replaceable> can be joined tables. Parentheses |
| 171 |
can be used around <literal>JOIN</literal> clauses to control the join |
| 172 |
order. In the absence of parentheses, <literal>JOIN</literal> clauses |
| 173 |
nest left-to-right. |
| 174 |
</para> |
| 175 |
|
| 176 |
<variablelist> |
| 177 |
<title>Join Types</title> |
| 178 |
|
| 179 |
<varlistentry> |
| 180 |
<term>Cross join |
| 181 |
<indexterm> |
| 182 |
<primary>join</primary> |
| 183 |
<secondary>cross</secondary> |
| 184 |
</indexterm> |
| 185 |
|
| 186 |
<indexterm> |
| 187 |
<primary>cross join</primary> |
| 188 |
</indexterm> |
| 189 |
</term> |
| 190 |
|
| 191 |
<listitem> |
| 192 |
<synopsis> |
| 193 |
<replaceable>T1</replaceable> CROSS JOIN <replaceable>T2</replaceable> |
| 194 |
</synopsis> |
| 195 |
|
| 196 |
<para> |
| 197 |
For every possible combination of rows from |
| 198 |
<replaceable>T1</replaceable> and |
| 199 |
<replaceable>T2</replaceable> (i.e., a Cartesian product), |
| 200 |
the joined table will contain a |
| 201 |
row consisting of all columns in <replaceable>T1</replaceable> |
| 202 |
followed by all columns in <replaceable>T2</replaceable>. If |
| 203 |
the tables have N and M rows respectively, the joined |
| 204 |
table will have N * M rows. |
| 205 |
</para> |
| 206 |
|
| 207 |
<para> |
| 208 |
<literal>FROM <replaceable>T1</replaceable> CROSS JOIN |
| 209 |
<replaceable>T2</replaceable></literal> is equivalent to |
| 210 |
<literal>FROM <replaceable>T1</replaceable> INNER JOIN |
| 211 |
<replaceable>T2</replaceable> ON TRUE</literal> (see below). |
| 212 |
It is also equivalent to |
| 213 |
<literal>FROM <replaceable>T1</replaceable>, |
| 214 |
<replaceable>T2</replaceable></literal>. |
| 215 |
<note> |
| 216 |
<para> |
| 217 |
This latter equivalence does not hold exactly when more than two |
| 218 |
tables appear, because <literal>JOIN</literal> binds more tightly than |
| 219 |
comma. For example |
| 220 |
<literal>FROM <replaceable>T1</replaceable> CROSS JOIN |
| 221 |
<replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable> |
| 222 |
ON <replaceable>condition</replaceable></literal> |
| 223 |
is not the same as |
| 224 |
<literal>FROM <replaceable>T1</replaceable>, |
| 225 |
<replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable> |
| 226 |
ON <replaceable>condition</replaceable></literal> |
| 227 |
because the <replaceable>condition</replaceable> can |
| 228 |
reference <replaceable>T1</replaceable> in the first case but not |
| 229 |
the second. |
| 230 |
</para> |
| 231 |
</note> |
| 232 |
</para> |
| 233 |
</listitem> |
| 234 |
</varlistentry> |
| 235 |
|
| 236 |
<varlistentry> |
| 237 |
<term>Qualified joins |
| 238 |
<indexterm> |
| 239 |
<primary>join</primary> |
| 240 |
<secondary>outer</secondary> |
| 241 |
</indexterm> |
| 242 |
|
| 243 |
<indexterm> |
| 244 |
<primary>outer join</primary> |
| 245 |
</indexterm> |
| 246 |
</term> |
| 247 |
|
| 248 |
<listitem> |
| 249 |
<synopsis> |
| 250 |
<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> ON <replaceable>boolean_expression</replaceable> |
| 251 |
<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> ) |
| 252 |
<replaceable>T1</replaceable> NATURAL { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> |
| 253 |
</synopsis> |
| 254 |
|
| 255 |
<para> |
| 256 |
The words <literal>INNER</literal> and |
| 257 |
<literal>OUTER</literal> are optional in all forms. |
| 258 |
<literal>INNER</literal> is the default; |
| 259 |
<literal>LEFT</literal>, <literal>RIGHT</literal>, and |
| 260 |
<literal>FULL</literal> imply an outer join. |
| 261 |
</para> |
| 262 |
|
| 263 |
<para> |
| 264 |
The <firstterm>join condition</firstterm> is specified in the |
| 265 |
<literal>ON</literal> or <literal>USING</literal> clause, or implicitly by |
| 266 |
the word <literal>NATURAL</literal>. The join condition determines |
| 267 |
which rows from the two source tables are considered to |
| 268 |
<quote>match</quote>, as explained in detail below. |
| 269 |
</para> |
| 270 |
|
| 271 |
<para> |
| 272 |
The possible types of qualified join are: |
| 273 |
|
| 274 |
<variablelist> |
| 275 |
<varlistentry> |
| 276 |
<term><literal>INNER JOIN</literal></term> |
| 277 |
|
| 278 |
<listitem> |
| 279 |
<para> |
| 280 |
For each row R1 of T1, the joined table has a row for each |
| 281 |
row in T2 that satisfies the join condition with R1. |
| 282 |
</para> |
| 283 |
</listitem> |
| 284 |
</varlistentry> |
| 285 |
|
| 286 |
<varlistentry> |
| 287 |
<term><literal>LEFT OUTER JOIN</literal> |
| 288 |
<indexterm> |
| 289 |
<primary>join</primary> |
| 290 |
<secondary>left</secondary> |
| 291 |
</indexterm> |
| 292 |
|
| 293 |
<indexterm> |
| 294 |
<primary>left join</primary> |
| 295 |
</indexterm> |
| 296 |
</term> |
| 297 |
|
| 298 |
<listitem> |
| 299 |
<para> |
| 300 |
First, an inner join is performed. Then, for each row in |
| 301 |
T1 that does not satisfy the join condition with any row in |
| 302 |
T2, a joined row is added with null values in columns of |
| 303 |
T2. Thus, the joined table always has at least |
| 304 |
one row for each row in T1. |
| 305 |
</para> |
| 306 |
</listitem> |
| 307 |
</varlistentry> |
| 308 |
|
| 309 |
<varlistentry> |
| 310 |
<term><literal>RIGHT OUTER JOIN</literal> |
| 311 |
<indexterm> |
| 312 |
<primary>join</primary> |
| 313 |
<secondary>right</secondary> |
| 314 |
</indexterm> |
| 315 |
|
| 316 |
<indexterm> |
| 317 |
<primary>right join</primary> |
| 318 |
</indexterm> |
| 319 |
</term> |
| 320 |
|
| 321 |
<listitem> |
| 322 |
<para> |
| 323 |
First, an inner join is performed. Then, for each row in |
| 324 |
T2 that does not satisfy the join condition with any row in |
| 325 |
T1, a joined row is added with null values in columns of |
| 326 |
T1. This is the converse of a left join: the result table |
| 327 |
will always have a row for each row in T2. |
| 328 |
</para> |
| 329 |
</listitem> |
| 330 |
</varlistentry> |
| 331 |
|
| 332 |
<varlistentry> |
| 333 |
<term><literal>FULL OUTER JOIN</literal></term> |
| 334 |
|
| 335 |
<listitem> |
| 336 |
<para> |
| 337 |
First, an inner join is performed. Then, for each row in |
| 338 |
T1 that does not satisfy the join condition with any row in |
| 339 |
T2, a joined row is added with null values in columns of |
| 340 |
T2. Also, for each row of T2 that does not satisfy the |
| 341 |
join condition with any row in T1, a joined row with null |
| 342 |
values in the columns of T1 is added. |
| 343 |
</para> |
| 344 |
</listitem> |
| 345 |
</varlistentry> |
| 346 |
</variablelist> |
| 347 |
</para> |
| 348 |
|
| 349 |
<para> |
| 350 |
The <literal>ON</literal> clause is the most general kind of join |
| 351 |
condition: it takes a Boolean value expression of the same |
| 352 |
kind as is used in a <literal>WHERE</literal> clause. A pair of rows |
| 353 |
from <replaceable>T1</replaceable> and <replaceable>T2</replaceable> match if the |
| 354 |
<literal>ON</literal> expression evaluates to true. |
| 355 |
</para> |
| 356 |
|
| 357 |
<para> |
| 358 |
The <literal>USING</literal> clause is a shorthand that allows you to take |
| 359 |
advantage of the specific situation where both sides of the join use |
| 360 |
the same name for the joining column(s). It takes a |
| 361 |
comma-separated list of the shared column names |
| 362 |
and forms a join condition that includes an equality comparison |
| 363 |
for each one. For example, joining <replaceable>T1</replaceable> |
| 364 |
and <replaceable>T2</replaceable> with <literal>USING (a, b)</literal> produces |
| 365 |
the join condition <literal>ON <replaceable>T1</replaceable>.a |
| 366 |
= <replaceable>T2</replaceable>.a AND <replaceable>T1</replaceable>.b |
| 367 |
= <replaceable>T2</replaceable>.b</literal>. |
| 368 |
</para> |
| 369 |
|
| 370 |
<para> |
| 371 |
Furthermore, the output of <literal>JOIN USING</literal> suppresses |
| 372 |
redundant columns: there is no need to print both of the matched |
| 373 |
columns, since they must have equal values. While <literal>JOIN |
| 374 |
ON</literal> produces all columns from <replaceable>T1</replaceable> followed by all |
| 375 |
columns from <replaceable>T2</replaceable>, <literal>JOIN USING</literal> produces one |
| 376 |
output column for each of the listed column pairs (in the listed |
| 377 |
order), followed by any remaining columns from <replaceable>T1</replaceable>, |
| 378 |
followed by any remaining columns from <replaceable>T2</replaceable>. |
| 379 |
</para> |
| 380 |
|
| 381 |
<para> |
| 382 |
<indexterm> |
| 383 |
<primary>join</primary> |
| 384 |
<secondary>natural</secondary> |
| 385 |
</indexterm> |
| 386 |
<indexterm> |
| 387 |
<primary>natural join</primary> |
| 388 |
</indexterm> |
| 389 |
Finally, <literal>NATURAL</literal> is a shorthand form of |
| 390 |
<literal>USING</literal>: it forms a <literal>USING</literal> list |
| 391 |
consisting of all column names that appear in both |
| 392 |
input tables. As with <literal>USING</literal>, these columns appear |
| 393 |
only once in the output table. If there are no common |
| 394 |
column names, <literal>NATURAL JOIN</literal> behaves like |
| 395 |
<literal>CROSS JOIN</literal>. |
| 396 |
</para> |
| 397 |
|
| 398 |
<note> |
| 399 |
<para> |
| 400 |
<literal>USING</literal> is reasonably safe from column changes |
| 401 |
in the joined relations since only the listed columns |
| 402 |
are combined. <literal>NATURAL</literal> is considerably more risky since |
| 403 |
any schema changes to either relation that cause a new matching |
| 404 |
column name to be present will cause the join to combine that new |
| 405 |
column as well. |
| 406 |
</para> |
| 407 |
</note> |
| 408 |
</listitem> |
| 409 |
</varlistentry> |
| 410 |
</variablelist> |
| 411 |
|
| 412 |
<para> |
| 413 |
To put this together, assume we have tables <literal>t1</literal>: |
| 414 |
<programlisting> |
| 415 |
num | name |
| 416 |
-----+------ |
| 417 |
1 | a |
| 418 |
2 | b |
| 419 |
3 | c |
| 420 |
</programlisting> |
| 421 |
and <literal>t2</literal>: |
| 422 |
<programlisting> |
| 423 |
num | value |
| 424 |
-----+------- |
| 425 |
1 | xxx |
| 426 |
3 | yyy |
| 427 |
5 | zzz |
| 428 |
</programlisting> |
| 429 |
then we get the following results for the various joins: |
| 430 |
<screen> |
| 431 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 CROSS JOIN t2;</userinput> |
| 432 |
num | name | num | value |
| 433 |
-----+------+-----+------- |
| 434 |
1 | a | 1 | xxx |
| 435 |
1 | a | 3 | yyy |
| 436 |
1 | a | 5 | zzz |
| 437 |
2 | b | 1 | xxx |
| 438 |
2 | b | 3 | yyy |
| 439 |
2 | b | 5 | zzz |
| 440 |
3 | c | 1 | xxx |
| 441 |
3 | c | 3 | yyy |
| 442 |
3 | c | 5 | zzz |
| 443 |
(9 rows) |
| 444 |
|
| 445 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</userinput> |
| 446 |
num | name | num | value |
| 447 |
-----+------+-----+------- |
| 448 |
1 | a | 1 | xxx |
| 449 |
3 | c | 3 | yyy |
| 450 |
(2 rows) |
| 451 |
|
| 452 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 INNER JOIN t2 USING (num);</userinput> |
| 453 |
num | name | value |
| 454 |
-----+------+------- |
| 455 |
1 | a | xxx |
| 456 |
3 | c | yyy |
| 457 |
(2 rows) |
| 458 |
|
| 459 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 NATURAL INNER JOIN t2;</userinput> |
| 460 |
num | name | value |
| 461 |
-----+------+------- |
| 462 |
1 | a | xxx |
| 463 |
3 | c | yyy |
| 464 |
(2 rows) |
| 465 |
|
| 466 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</userinput> |
| 467 |
num | name | num | value |
| 468 |
-----+------+-----+------- |
| 469 |
1 | a | 1 | xxx |
| 470 |
2 | b | | |
| 471 |
3 | c | 3 | yyy |
| 472 |
(3 rows) |
| 473 |
|
| 474 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 USING (num);</userinput> |
| 475 |
num | name | value |
| 476 |
-----+------+------- |
| 477 |
1 | a | xxx |
| 478 |
2 | b | |
| 479 |
3 | c | yyy |
| 480 |
(3 rows) |
| 481 |
|
| 482 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</userinput> |
| 483 |
num | name | num | value |
| 484 |
-----+------+-----+------- |
| 485 |
1 | a | 1 | xxx |
| 486 |
3 | c | 3 | yyy |
| 487 |
| | 5 | zzz |
| 488 |
(3 rows) |
| 489 |
|
| 490 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</userinput> |
| 491 |
num | name | num | value |
| 492 |
-----+------+-----+------- |
| 493 |
1 | a | 1 | xxx |
| 494 |
2 | b | | |
| 495 |
3 | c | 3 | yyy |
| 496 |
| | 5 | zzz |
| 497 |
(4 rows) |
| 498 |
</screen> |
| 499 |
</para> |
| 500 |
|
| 501 |
<para> |
| 502 |
The join condition specified with <literal>ON</literal> can also contain |
| 503 |
conditions that do not relate directly to the join. This can |
| 504 |
prove useful for some queries but needs to be thought out |
| 505 |
carefully. For example: |
| 506 |
<screen> |
| 507 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</userinput> |
| 508 |
num | name | num | value |
| 509 |
-----+------+-----+------- |
| 510 |
1 | a | 1 | xxx |
| 511 |
2 | b | | |
| 512 |
3 | c | | |
| 513 |
(3 rows) |
| 514 |
</screen> |
| 515 |
Notice that placing the restriction in the <literal>WHERE</literal> clause |
| 516 |
produces a different result: |
| 517 |
<screen> |
| 518 |
<prompt>=></prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';</userinput> |
| 519 |
num | name | num | value |
| 520 |
-----+------+-----+------- |
| 521 |
1 | a | 1 | xxx |
| 522 |
(1 row) |
| 523 |
</screen> |
| 524 |
This is because a restriction placed in the <literal>ON</literal> |
| 525 |
clause is processed <emphasis>before</emphasis> the join, while |
| 526 |
a restriction placed in the <literal>WHERE</literal> clause is processed |
| 527 |
<emphasis>after</emphasis> the join. |
| 528 |
That does not matter with inner joins, but it matters a lot with outer |
| 529 |
joins. |
| 530 |
</para> |
| 531 |
</sect3> |
| 532 |
|
| 533 |
<sect3 id="queries-table-aliases"> |
| 534 |
<title>Table and Column Aliases</title> |
| 535 |
|
| 536 |
<indexterm zone="queries-table-aliases"> |
| 537 |
<primary>alias</primary> |
| 538 |
<secondary>in the FROM clause</secondary> |
| 539 |
</indexterm> |
| 540 |
|
| 541 |
<indexterm> |
| 542 |
<primary>label</primary> |
| 543 |
<see>alias</see> |
| 544 |
</indexterm> |
| 545 |
|
| 546 |
<para> |
| 547 |
A temporary name can be given to tables and complex table |
| 548 |
references to be used for references to the derived table in |
| 549 |
the rest of the query. This is called a <firstterm>table |
| 550 |
alias</firstterm>. |
| 551 |
</para> |
| 552 |
|
| 553 |
<para> |
| 554 |
To create a table alias, write |
| 555 |
<synopsis> |
| 556 |
FROM <replaceable>table_reference</replaceable> AS <replaceable>alias</replaceable> |
| 557 |
</synopsis> |
| 558 |
or |
| 559 |
<synopsis> |
| 560 |
FROM <replaceable>table_reference</replaceable> <replaceable>alias</replaceable> |
| 561 |
</synopsis> |
| 562 |
The <literal>AS</literal> key word is optional noise. |
| 563 |
<replaceable>alias</replaceable> can be any identifier. |
| 564 |
</para> |
| 565 |
|
| 566 |
<para> |
| 567 |
A typical application of table aliases is to assign short |
| 568 |
identifiers to long table names to keep the join clauses |
| 569 |
readable. For example: |
| 570 |
<programlisting> |
| 571 |
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num; |
| 572 |
</programlisting> |
| 573 |
</para> |
| 574 |
|
| 575 |
<para> |
| 576 |
The alias becomes the new name of the table reference so far as the |
| 577 |
current query is concerned — it is not allowed to refer to the |
| 578 |
table by the original name elsewhere in the query. Thus, this is not |
| 579 |
valid: |
| 580 |
<programlisting> |
| 581 |
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- wrong |
| 582 |
</programlisting> |
| 583 |
</para> |
| 584 |
|
| 585 |
<para> |
| 586 |
Table aliases are mainly for notational convenience, but it is |
| 587 |
necessary to use them when joining a table to itself, e.g.: |
| 588 |
<programlisting> |
| 589 |
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id; |
| 590 |
</programlisting> |
| 591 |
</para> |
| 592 |
|
| 593 |
<para> |
| 594 |
Parentheses are used to resolve ambiguities. In the following example, |
| 595 |
the first statement assigns the alias <literal>b</literal> to the second |
| 596 |
instance of <literal>my_table</literal>, but the second statement assigns the |
| 597 |
alias to the result of the join: |
| 598 |
<programlisting> |
| 599 |
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ... |
| 600 |
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ... |
| 601 |
</programlisting> |
| 602 |
</para> |
| 603 |
|
| 604 |
<para> |
| 605 |
Another form of table aliasing gives temporary names to the columns of |
| 606 |
the table, as well as the table itself: |
| 607 |
<synopsis> |
| 608 |
FROM <replaceable>table_reference</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> ( <replaceable>column1</replaceable> <optional>, <replaceable>column2</replaceable> <optional>, ...</optional></optional> ) |
| 609 |
</synopsis> |
| 610 |
If fewer column aliases are specified than the actual table has |
| 611 |
columns, the remaining columns are not renamed. This syntax is |
| 612 |
especially useful for self-joins or subqueries. |
| 613 |
</para> |
| 614 |
|
| 615 |
<para> |
| 616 |
When an alias is applied to the output of a <literal>JOIN</literal> |
| 617 |
clause, the alias hides the original |
| 618 |
name(s) within the <literal>JOIN</literal>. For example: |
| 619 |
<programlisting> |
| 620 |
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ... |
| 621 |
</programlisting> |
| 622 |
is valid SQL, but: |
| 623 |
<programlisting> |
| 624 |
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c |
| 625 |
</programlisting> |
| 626 |
is not valid; the table alias <literal>a</literal> is not visible |
| 627 |
outside the alias <literal>c</literal>. |
| 628 |
</para> |
| 629 |
</sect3> |
| 630 |
|
| 631 |
<sect3 id="queries-subqueries"> |
| 632 |
<title>Subqueries</title> |
| 633 |
|
| 634 |
<indexterm zone="queries-subqueries"> |
| 635 |
<primary>subquery</primary> |
| 636 |
</indexterm> |
| 637 |
|
| 638 |
<para> |
| 639 |
Subqueries specifying a derived table must be enclosed in |
| 640 |
parentheses. They may be assigned a table alias name, and optionally |
| 641 |
column alias names (as in <xref linkend="queries-table-aliases"/>). |
| 642 |
For example: |
| 643 |
<programlisting> |
| 644 |
FROM (SELECT * FROM table1) AS alias_name |
| 645 |
</programlisting> |
| 646 |
</para> |
| 647 |
|
| 648 |
<para> |
| 649 |
This example is equivalent to <literal>FROM table1 AS |
| 650 |
alias_name</literal>. More interesting cases, which cannot be |
| 651 |
reduced to a plain join, arise when the subquery involves |
| 652 |
grouping or aggregation. |
| 653 |
</para> |
| 654 |
|
| 655 |
<para> |
| 656 |
A subquery can also be a <command>VALUES</command> list: |
| 657 |
<programlisting> |
| 658 |
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow')) |
| 659 |
AS names(first, last) |
| 660 |
</programlisting> |
| 661 |
Again, a table alias is optional. Assigning alias names to the columns |
| 662 |
of the <command>VALUES</command> list is optional, but is good practice. |
| 663 |
For more information see <xref linkend="queries-values"/>. |
| 664 |
</para> |
| 665 |
|
| 666 |
<para> |
| 667 |
According to the SQL standard, a table alias name must be supplied |
| 668 |
for a subquery. <productname>PostgreSQL</productname> |
| 669 |
allows <literal>AS</literal> and the alias to be omitted, but |
| 670 |
writing one is good practice in SQL code that might be ported to |
| 671 |
another system. |
| 672 |
</para> |
| 673 |
</sect3> |
| 674 |
|
| 675 |
<sect3 id="queries-tablefunctions"> |
| 676 |
<title>Table Functions</title> |
| 677 |
|
| 678 |
<indexterm zone="queries-tablefunctions"><primary>table function</primary></indexterm> |
| 679 |
|
| 680 |
<indexterm zone="queries-tablefunctions"> |
| 681 |
<primary>function</primary> |
| 682 |
<secondary>in the FROM clause</secondary> |
| 683 |
</indexterm> |
| 684 |
|
| 685 |
<para> |
| 686 |
Table functions are functions that produce a set of rows, made up |
| 687 |
of either base data types (scalar types) or composite data types |
| 688 |
(table rows). They are used like a table, view, or subquery in |
| 689 |
the <literal>FROM</literal> clause of a query. Columns returned by table |
| 690 |
functions can be included in <literal>SELECT</literal>, |
| 691 |
<literal>JOIN</literal>, or <literal>WHERE</literal> clauses in the same manner |
| 692 |
as columns of a table, view, or subquery. |
| 693 |
</para> |
| 694 |
|
| 695 |
<para> |
| 696 |
Table functions may also be combined using the <literal>ROWS FROM</literal> |
| 697 |
syntax, with the results returned in parallel columns; the number of |
| 698 |
result rows in this case is that of the largest function result, with |
| 699 |
smaller results padded with null values to match. |
| 700 |
</para> |
| 701 |
|
| 702 |
<synopsis> |
| 703 |
<replaceable>function_call</replaceable> <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional> |
| 704 |
ROWS FROM( <replaceable>function_call</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional> |
| 705 |
</synopsis> |
| 706 |
|
| 707 |
<para> |
| 708 |
If the <literal>WITH ORDINALITY</literal> clause is specified, an |
| 709 |
additional column of type <type>bigint</type> will be added to the |
| 710 |
function result columns. This column numbers the rows of the function |
| 711 |
result set, starting from 1. (This is a generalization of the |
| 712 |
SQL-standard syntax for <literal>UNNEST ... WITH ORDINALITY</literal>.) |
| 713 |
By default, the ordinal column is called <literal>ordinality</literal>, but |
| 714 |
a different column name can be assigned to it using |
| 715 |
an <literal>AS</literal> clause. |
| 716 |
</para> |
| 717 |
|
| 718 |
<para> |
| 719 |
The special table function <literal>UNNEST</literal> may be called with |
| 720 |
any number of array parameters, and it returns a corresponding number of |
| 721 |
columns, as if <literal>UNNEST</literal> |
| 722 |
(<xref linkend="functions-array"/>) had been called on each parameter |
| 723 |
separately and combined using the <literal>ROWS FROM</literal> construct. |
| 724 |
</para> |
| 725 |
|
| 726 |
<synopsis> |
| 727 |
UNNEST( <replaceable>array_expression</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional> |
| 728 |
</synopsis> |
| 729 |
|
| 730 |
<para> |
| 731 |
If no <replaceable>table_alias</replaceable> is specified, the function |
| 732 |
name is used as the table name; in the case of a <literal>ROWS FROM()</literal> |
| 733 |
construct, the first function's name is used. |
| 734 |
</para> |
| 735 |
|
| 736 |
<para> |
| 737 |
If column aliases are not supplied, then for a function returning a base |
| 738 |
data type, the column name is also the same as the function name. For a |
| 739 |
function returning a composite type, the result columns get the names |
| 740 |
of the individual attributes of the type. |
| 741 |
</para> |
| 742 |
|
| 743 |
<para> |
| 744 |
Some examples: |
| 745 |
<programlisting> |
| 746 |
CREATE TABLE foo (fooid int, foosubid int, fooname text); |
| 747 |
|
| 748 |
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ |
| 749 |
SELECT * FROM foo WHERE fooid = $1; |
| 750 |
$$ LANGUAGE SQL; |
| 751 |
|
| 752 |
SELECT * FROM getfoo(1) AS t1; |
| 753 |
|
| 754 |
SELECT * FROM foo |
| 755 |
WHERE foosubid IN ( |
| 756 |
SELECT foosubid |
| 757 |
FROM getfoo(foo.fooid) z |
| 758 |
WHERE z.fooid = foo.fooid |
| 759 |
); |
| 760 |
|
| 761 |
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); |
| 762 |
|
| 763 |
SELECT * FROM vw_getfoo; |
| 764 |
</programlisting> |
| 765 |
</para> |
| 766 |
|
| 767 |
<para> |
| 768 |
In some cases it is useful to define table functions that can |
| 769 |
return different column sets depending on how they are invoked. |
| 770 |
To support this, the table function can be declared as returning |
| 771 |
the pseudo-type <type>record</type> with no <literal>OUT</literal> |
| 772 |
parameters. When such a function is used in |
| 773 |
a query, the expected row structure must be specified in the |
| 774 |
query itself, so that the system can know how to parse and plan |
| 775 |
the query. This syntax looks like: |
| 776 |
</para> |
| 777 |
|
| 778 |
<synopsis> |
| 779 |
<replaceable>function_call</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> (<replaceable>column_definition</replaceable> <optional>, ... </optional>) |
| 780 |
<replaceable>function_call</replaceable> AS <optional><replaceable>alias</replaceable></optional> (<replaceable>column_definition</replaceable> <optional>, ... </optional>) |
| 781 |
ROWS FROM( ... <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> ) |
| 782 |
</synopsis> |
| 783 |
|
| 784 |
<para> |
| 785 |
When not using the <literal>ROWS FROM()</literal> syntax, |
| 786 |
the <replaceable>column_definition</replaceable> list replaces the column |
| 787 |
alias list that could otherwise be attached to the <literal>FROM</literal> |
| 788 |
item; the names in the column definitions serve as column aliases. |
| 789 |
When using the <literal>ROWS FROM()</literal> syntax, |
| 790 |
a <replaceable>column_definition</replaceable> list can be attached to |
| 791 |
each member function separately; or if there is only one member function |
| 792 |
and no <literal>WITH ORDINALITY</literal> clause, |
| 793 |
a <replaceable>column_definition</replaceable> list can be written in |
| 794 |
place of a column alias list following <literal>ROWS FROM()</literal>. |
| 795 |
</para> |
| 796 |
|
| 797 |
<para> |
| 798 |
Consider this example: |
| 799 |
<programlisting> |
| 800 |
SELECT * |
| 801 |
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc') |
| 802 |
AS t1(proname name, prosrc text) |
| 803 |
WHERE proname LIKE 'bytea%'; |
| 804 |
</programlisting> |
| 805 |
The <xref linkend="contrib-dblink-function"/> function |
| 806 |
(part of the <xref linkend="dblink"/> module) executes |
| 807 |
a remote query. It is declared to return |
| 808 |
<type>record</type> since it might be used for any kind of query. |
| 809 |
The actual column set must be specified in the calling query so |
| 810 |
that the parser knows, for example, what <literal>*</literal> should |
| 811 |
expand to. |
| 812 |
</para> |
| 813 |
|
| 814 |
<para> |
| 815 |
This example uses <literal>ROWS FROM</literal>: |
| 816 |
<programlisting> |
| 817 |
SELECT * |
| 818 |
FROM ROWS FROM |
| 819 |
( |
| 820 |
json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]') |
| 821 |
AS (a INTEGER, b TEXT), |
| 822 |
generate_series(1, 3) |
| 823 |
) AS x (p, q, s) |
| 824 |
ORDER BY p; |
| 825 |
|
| 826 |
p | q | s |
| 827 |
-----+-----+--- |
| 828 |
40 | foo | 1 |
| 829 |
100 | bar | 2 |
| 830 |
| | 3 |
| 831 |
</programlisting> |
| 832 |
It joins two functions into a single <literal>FROM</literal> |
| 833 |
target. <function>json_to_recordset()</function> is instructed |
| 834 |
to return two columns, the first <type>integer</type> |
| 835 |
and the second <type>text</type>. The result of |
| 836 |
<function>generate_series()</function> is used directly. |
| 837 |
The <literal>ORDER BY</literal> clause sorts the column values |
| 838 |
as integers. |
| 839 |
</para> |
| 840 |
</sect3> |
| 841 |
|
| 842 |
<sect3 id="queries-lateral"> |
| 843 |
<title><literal>LATERAL</literal> Subqueries</title> |
| 844 |
|
| 845 |
<indexterm zone="queries-lateral"> |
| 846 |
<primary>LATERAL</primary> |
| 847 |
<secondary>in the FROM clause</secondary> |
| 848 |
</indexterm> |
| 849 |
|
| 850 |
<para> |
| 851 |
Subqueries appearing in <literal>FROM</literal> can be |
| 852 |
preceded by the key word <literal>LATERAL</literal>. This allows them to |
| 853 |
reference columns provided by preceding <literal>FROM</literal> items. |
| 854 |
(Without <literal>LATERAL</literal>, each subquery is |
| 855 |
evaluated independently and so cannot cross-reference any other |
| 856 |
<literal>FROM</literal> item.) |
| 857 |
</para> |
| 858 |
|
| 859 |
<para> |
| 860 |
Table functions appearing in <literal>FROM</literal> can also be |
| 861 |
preceded by the key word <literal>LATERAL</literal>, but for functions the |
| 862 |
key word is optional; the function's arguments can contain references |
| 863 |
to columns provided by preceding <literal>FROM</literal> items in any case. |
| 864 |
</para> |
| 865 |
|
| 866 |
<para> |
| 867 |
A <literal>LATERAL</literal> item can appear at the top level in the |
| 868 |
<literal>FROM</literal> list, or within a <literal>JOIN</literal> tree. In the latter |
| 869 |
case it can also refer to any items that are on the left-hand side of a |
| 870 |
<literal>JOIN</literal> that it is on the right-hand side of. |
| 871 |
</para> |
| 872 |
|
| 873 |
<para> |
| 874 |
When a <literal>FROM</literal> item contains <literal>LATERAL</literal> |
| 875 |
cross-references, evaluation proceeds as follows: for each row of the |
| 876 |
<literal>FROM</literal> item providing the cross-referenced column(s), or |
| 877 |
set of rows of multiple <literal>FROM</literal> items providing the |
| 878 |
columns, the <literal>LATERAL</literal> item is evaluated using that |
| 879 |
row or row set's values of the columns. The resulting row(s) are |
| 880 |
joined as usual with the rows they were computed from. This is |
| 881 |
repeated for each row or set of rows from the column source table(s). |
| 882 |
</para> |
| 883 |
|
| 884 |
<para> |
| 885 |
A trivial example of <literal>LATERAL</literal> is |
| 886 |
<programlisting> |
| 887 |
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss; |
| 888 |
</programlisting> |
| 889 |
This is not especially useful since it has exactly the same result as |
| 890 |
the more conventional |
| 891 |
<programlisting> |
| 892 |
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id; |
| 893 |
</programlisting> |
| 894 |
<literal>LATERAL</literal> is primarily useful when the cross-referenced |
| 895 |
column is necessary for computing the row(s) to be joined. A common |
| 896 |
application is providing an argument value for a set-returning function. |
| 897 |
For example, supposing that <function>vertices(polygon)</function> returns the |
| 898 |
set of vertices of a polygon, we could identify close-together vertices |
| 899 |
of polygons stored in a table with: |
| 900 |
<programlisting> |
| 901 |
SELECT p1.id, p2.id, v1, v2 |
| 902 |
FROM polygons p1, polygons p2, |
| 903 |
LATERAL vertices(p1.poly) v1, |
| 904 |
LATERAL vertices(p2.poly) v2 |
| 905 |
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id; |
| 906 |
</programlisting> |
| 907 |
This query could also be written |
| 908 |
<programlisting> |
| 909 |
SELECT p1.id, p2.id, v1, v2 |
| 910 |
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1, |
| 911 |
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2 |
| 912 |
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id; |
| 913 |
</programlisting> |
| 914 |
or in several other equivalent formulations. (As already mentioned, |
| 915 |
the <literal>LATERAL</literal> key word is unnecessary in this example, but |
| 916 |
we use it for clarity.) |
| 917 |
</para> |
| 918 |
|
| 919 |
<para> |
| 920 |
It is often particularly handy to <literal>LEFT JOIN</literal> to a |
| 921 |
<literal>LATERAL</literal> subquery, so that source rows will appear in |
| 922 |
the result even if the <literal>LATERAL</literal> subquery produces no |
| 923 |
rows for them. For example, if <function>get_product_names()</function> returns |
| 924 |
the names of products made by a manufacturer, but some manufacturers in |
| 925 |
our table currently produce no products, we could find out which ones |
| 926 |
those are like this: |
| 927 |
<programlisting> |
| 928 |
SELECT m.name |
| 929 |
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true |
| 930 |
WHERE pname IS NULL; |
| 931 |
</programlisting> |
| 932 |
</para> |
| 933 |
</sect3> |
| 934 |
</sect2> |
| 935 |
|
| 936 |
<sect2 id="queries-where"> |
| 937 |
<title>The <literal>WHERE</literal> Clause</title> |
| 938 |
|
| 939 |
<indexterm zone="queries-where"> |
| 940 |
<primary>WHERE</primary> |
| 941 |
</indexterm> |
| 942 |
|
| 943 |
<para> |
| 944 |
The syntax of the <link linkend="sql-where"><literal>WHERE</literal></link> |
| 945 |
clause is |
| 946 |
<synopsis> |
| 947 |
WHERE <replaceable>search_condition</replaceable> |
| 948 |
</synopsis> |
| 949 |
where <replaceable>search_condition</replaceable> is any value |
| 950 |
expression (see <xref linkend="sql-expressions"/>) that |
| 951 |
returns a value of type <type>boolean</type>. |
| 952 |
</para> |
| 953 |
|
| 954 |
<para> |
| 955 |
After the processing of the <literal>FROM</literal> clause is done, each |
| 956 |
row of the derived virtual table is checked against the search |
| 957 |
condition. If the result of the condition is true, the row is |
| 958 |
kept in the output table, otherwise (i.e., if the result is |
| 959 |
false or null) it is discarded. The search condition typically |
| 960 |
references at least one column of the table generated in the |
| 961 |
<literal>FROM</literal> clause; this is not required, but otherwise the |
| 962 |
<literal>WHERE</literal> clause will be fairly useless. |
| 963 |
</para> |
| 964 |
|
| 965 |
<note> |
| 966 |
<para> |
| 967 |
The join condition of an inner join can be written either in |
| 968 |
the <literal>WHERE</literal> clause or in the <literal>JOIN</literal> clause. |
| 969 |
For example, these table expressions are equivalent: |
| 970 |
<programlisting> |
| 971 |
FROM a, b WHERE a.id = b.id AND b.val > 5 |
| 972 |
</programlisting> |
| 973 |
and: |
| 974 |
<programlisting> |
| 975 |
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5 |
| 976 |
</programlisting> |
| 977 |
or perhaps even: |
| 978 |
<programlisting> |
| 979 |
FROM a NATURAL JOIN b WHERE b.val > 5 |
| 980 |
</programlisting> |
| 981 |
Which one of these you use is mainly a matter of style. The |
| 982 |
<literal>JOIN</literal> syntax in the <literal>FROM</literal> clause is |
| 983 |
probably not as portable to other SQL database management systems, |
| 984 |
even though it is in the SQL standard. For |
| 985 |
outer joins there is no choice: they must be done in |
| 986 |
the <literal>FROM</literal> clause. The <literal>ON</literal> or <literal>USING</literal> |
| 987 |
clause of an outer join is <emphasis>not</emphasis> equivalent to a |
| 988 |
<literal>WHERE</literal> condition, because it results in the addition |
| 989 |
of rows (for unmatched input rows) as well as the removal of rows |
| 990 |
in the final result. |
| 991 |
</para> |
| 992 |
</note> |
| 993 |
|
| 994 |
<para> |
| 995 |
Here are some examples of <literal>WHERE</literal> clauses: |
| 996 |
<programlisting> |
| 997 |
SELECT ... FROM fdt WHERE c1 > 5 |
| 998 |
|
| 999 |
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3) |
| 1000 |
|
| 1001 |
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2) |
| 1002 |
|
| 1003 |
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) |
| 1004 |
|
| 1005 |
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100 |
| 1006 |
|
| 1007 |
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1) |
| 1008 |
</programlisting> |
| 1009 |
<literal>fdt</literal> is the table derived in the |
| 1010 |
<literal>FROM</literal> clause. Rows that do not meet the search |
| 1011 |
condition of the <literal>WHERE</literal> clause are eliminated from |
| 1012 |
<literal>fdt</literal>. Notice the use of scalar subqueries as |
| 1013 |
value expressions. Just like any other query, the subqueries can |
| 1014 |
employ complex table expressions. Notice also how |
| 1015 |
<literal>fdt</literal> is referenced in the subqueries. |
| 1016 |
Qualifying <literal>c1</literal> as <literal>fdt.c1</literal> is only necessary |
| 1017 |
if <literal>c1</literal> is also the name of a column in the derived |
| 1018 |
input table of the subquery. But qualifying the column name adds |
| 1019 |
clarity even when it is not needed. This example shows how the column |
| 1020 |
naming scope of an outer query extends into its inner queries. |
| 1021 |
</para> |
| 1022 |
</sect2> |
| 1023 |
|
| 1024 |
|
| 1025 |
<sect2 id="queries-group"> |
| 1026 |
<title>The <literal>GROUP BY</literal> and <literal>HAVING</literal> Clauses</title> |
| 1027 |
|
| 1028 |
<indexterm zone="queries-group"> |
| 1029 |
<primary>GROUP BY</primary> |
| 1030 |
</indexterm> |
| 1031 |
|
| 1032 |
<indexterm zone="queries-group"> |
| 1033 |
<primary>grouping</primary> |
| 1034 |
</indexterm> |
| 1035 |
|
| 1036 |
<para> |
| 1037 |
After passing the <literal>WHERE</literal> filter, the derived input |
| 1038 |
table might be subject to grouping, using the <literal>GROUP BY</literal> |
| 1039 |
clause, and elimination of group rows using the <literal>HAVING</literal> |
| 1040 |
clause. |
| 1041 |
</para> |
| 1042 |
|
| 1043 |
<synopsis> |
| 1044 |
SELECT <replaceable>select_list</replaceable> |
| 1045 |
FROM ... |
| 1046 |
<optional>WHERE ...</optional> |
| 1047 |
GROUP BY <replaceable>grouping_column_reference</replaceable> <optional>, <replaceable>grouping_column_reference</replaceable></optional>... |
| 1048 |
</synopsis> |
| 1049 |
|
| 1050 |
<para> |
| 1051 |
The <link linkend="sql-groupby"><literal>GROUP BY</literal></link> clause is |
| 1052 |
used to group together those rows in a table that have the same |
| 1053 |
values in all the columns listed. The order in which the columns |
| 1054 |
are listed does not matter. The effect is to combine each set |
| 1055 |
of rows having common values into one group row that |
| 1056 |
represents all rows in the group. This is done to |
| 1057 |
eliminate redundancy in the output and/or compute aggregates that |
| 1058 |
apply to these groups. For instance: |
| 1059 |
<screen> |
| 1060 |
<prompt>=></prompt> <userinput>SELECT * FROM test1;</userinput> |
| 1061 |
x | y |
| 1062 |
---+--- |
| 1063 |
a | 3 |
| 1064 |
c | 2 |
| 1065 |
b | 5 |
| 1066 |
a | 1 |
| 1067 |
(4 rows) |
| 1068 |
|
| 1069 |
<prompt>=></prompt> <userinput>SELECT x FROM test1 GROUP BY x;</userinput> |
| 1070 |
x |
| 1071 |
--- |
| 1072 |
a |
| 1073 |
b |
| 1074 |
c |
| 1075 |
(3 rows) |
| 1076 |
</screen> |
| 1077 |
</para> |
| 1078 |
|
| 1079 |
<para> |
| 1080 |
In the second query, we could not have written <literal>SELECT * |
| 1081 |
FROM test1 GROUP BY x</literal>, because there is no single value |
| 1082 |
for the column <literal>y</literal> that could be associated with each |
| 1083 |
group. The grouped-by columns can be referenced in the select list since |
| 1084 |
they have a single value in each group. |
| 1085 |
</para> |
| 1086 |
|
| 1087 |
<para> |
| 1088 |
In general, if a table is grouped, columns that are not |
| 1089 |
listed in <literal>GROUP BY</literal> cannot be referenced except in aggregate |
| 1090 |
expressions. An example with aggregate expressions is: |
| 1091 |
<screen> |
| 1092 |
<prompt>=></prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x;</userinput> |
| 1093 |
x | sum |
| 1094 |
---+----- |
| 1095 |
a | 4 |
| 1096 |
b | 5 |
| 1097 |
c | 2 |
| 1098 |
(3 rows) |
| 1099 |
</screen> |
| 1100 |
Here <literal>sum</literal> is an aggregate function that |
| 1101 |
computes a single value over the entire group. More information |
| 1102 |
about the available aggregate functions can be found in <xref |
| 1103 |
linkend="functions-aggregate"/>. |
| 1104 |
</para> |
| 1105 |
|
| 1106 |
<tip> |
| 1107 |
<para> |
| 1108 |
Grouping without aggregate expressions effectively calculates the |
| 1109 |
set of distinct values in a column. This can also be achieved |
| 1110 |
using the <literal>DISTINCT</literal> clause (see <xref |
| 1111 |
linkend="queries-distinct"/>). |
| 1112 |
</para> |
| 1113 |
</tip> |
| 1114 |
|
| 1115 |
<para> |
| 1116 |
Here is another example: it calculates the total sales for each |
| 1117 |
product (rather than the total sales of all products): |
| 1118 |
<programlisting> |
| 1119 |
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales |
| 1120 |
FROM products p LEFT JOIN sales s USING (product_id) |
| 1121 |
GROUP BY product_id, p.name, p.price; |
| 1122 |
</programlisting> |
| 1123 |
In this example, the columns <literal>product_id</literal>, |
| 1124 |
<literal>p.name</literal>, and <literal>p.price</literal> must be |
| 1125 |
in the <literal>GROUP BY</literal> clause since they are referenced in |
| 1126 |
the query select list (but see below). The column |
| 1127 |
<literal>s.units</literal> does not have to be in the <literal>GROUP |
| 1128 |
BY</literal> list since it is only used in an aggregate expression |
| 1129 |
(<literal>sum(...)</literal>), which represents the sales |
| 1130 |
of a product. For each product, the query returns a summary row about |
| 1131 |
all sales of the product. |
| 1132 |
</para> |
| 1133 |
|
| 1134 |
<indexterm><primary>functional dependency</primary></indexterm> |
| 1135 |
|
| 1136 |
<para> |
| 1137 |
If the products table is set up so that, say, |
| 1138 |
<literal>product_id</literal> is the primary key, then it would be |
| 1139 |
enough to group by <literal>product_id</literal> in the above example, |
| 1140 |
since name and price would be <firstterm>functionally |
| 1141 |
dependent</firstterm> on the product ID, and so there would be no |
| 1142 |
ambiguity about which name and price value to return for each product |
| 1143 |
ID group. |
| 1144 |
</para> |
| 1145 |
|
| 1146 |
<para> |
| 1147 |
In strict SQL, <literal>GROUP BY</literal> can only group by columns of |
| 1148 |
the source table but <productname>PostgreSQL</productname> extends |
| 1149 |
this to also allow <literal>GROUP BY</literal> to group by columns in the |
| 1150 |
select list. Grouping by value expressions instead of simple |
| 1151 |
column names is also allowed. |
| 1152 |
</para> |
| 1153 |
|
| 1154 |
<indexterm> |
| 1155 |
<primary>HAVING</primary> |
| 1156 |
</indexterm> |
| 1157 |
|
| 1158 |
<para> |
| 1159 |
If a table has been grouped using <literal>GROUP BY</literal>, |
| 1160 |
but only certain groups are of interest, the |
| 1161 |
<literal>HAVING</literal> clause can be used, much like a |
| 1162 |
<literal>WHERE</literal> clause, to eliminate groups from the result. |
| 1163 |
The syntax is: |
| 1164 |
<synopsis> |
| 1165 |
SELECT <replaceable>select_list</replaceable> FROM ... <optional>WHERE ...</optional> GROUP BY ... HAVING <replaceable>boolean_expression</replaceable> |
| 1166 |
</synopsis> |
| 1167 |
Expressions in the <literal>HAVING</literal> clause can refer both to |
| 1168 |
grouped expressions and to ungrouped expressions (which necessarily |
| 1169 |
involve an aggregate function). |
| 1170 |
</para> |
| 1171 |
|
| 1172 |
<para> |
| 1173 |
Example: |
| 1174 |
<screen> |
| 1175 |
<prompt>=></prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;</userinput> |
| 1176 |
x | sum |
| 1177 |
---+----- |
| 1178 |
a | 4 |
| 1179 |
b | 5 |
| 1180 |
(2 rows) |
| 1181 |
|
| 1182 |
<prompt>=></prompt> <userinput>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';</userinput> |
| 1183 |
x | sum |
| 1184 |
---+----- |
| 1185 |
a | 4 |
| 1186 |
b | 5 |
| 1187 |
(2 rows) |
| 1188 |
</screen> |
| 1189 |
</para> |
| 1190 |
|
| 1191 |
<para> |
| 1192 |
Again, a more realistic example: |
| 1193 |
<programlisting> |
| 1194 |
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit |
| 1195 |
FROM products p LEFT JOIN sales s USING (product_id) |
| 1196 |
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks' |
| 1197 |
GROUP BY product_id, p.name, p.price, p.cost |
| 1198 |
HAVING sum(p.price * s.units) > 5000; |
| 1199 |
</programlisting> |
| 1200 |
In the example above, the <literal>WHERE</literal> clause is selecting |
| 1201 |
rows by a column that is not grouped (the expression is only true for |
| 1202 |
sales during the last four weeks), while the <literal>HAVING</literal> |
| 1203 |
clause restricts the output to groups with total gross sales over |
| 1204 |
5000. Note that the aggregate expressions do not necessarily need |
| 1205 |
to be the same in all parts of the query. |
| 1206 |
</para> |
| 1207 |
|
| 1208 |
<para> |
| 1209 |
If a query contains aggregate function calls, but no <literal>GROUP BY</literal> |
| 1210 |
clause, grouping still occurs: the result is a single group row (or |
| 1211 |
perhaps no rows at all, if the single row is then eliminated by |
| 1212 |
<literal>HAVING</literal>). |
| 1213 |
The same is true if it contains a <literal>HAVING</literal> clause, even |
| 1214 |
without any aggregate function calls or <literal>GROUP BY</literal> clause. |
| 1215 |
</para> |
| 1216 |
</sect2> |
| 1217 |
|
| 1218 |
<sect2 id="queries-grouping-sets"> |
| 1219 |
<title><literal>GROUPING SETS</literal>, <literal>CUBE</literal>, and <literal>ROLLUP</literal></title> |
| 1220 |
|
| 1221 |
<indexterm zone="queries-grouping-sets"> |
| 1222 |
<primary>GROUPING SETS</primary> |
| 1223 |
</indexterm> |
| 1224 |
<indexterm zone="queries-grouping-sets"> |
| 1225 |
<primary>CUBE</primary> |
| 1226 |
</indexterm> |
| 1227 |
<indexterm zone="queries-grouping-sets"> |
| 1228 |
<primary>ROLLUP</primary> |
| 1229 |
</indexterm> |
| 1230 |
|
| 1231 |
<para> |
| 1232 |
More complex grouping operations than those described above are possible |
| 1233 |
using the concept of <firstterm>grouping sets</firstterm>. The data selected by |
| 1234 |
the <literal>FROM</literal> and <literal>WHERE</literal> clauses is grouped separately |
| 1235 |
by each specified grouping set, aggregates computed for each group just as |
| 1236 |
for simple <literal>GROUP BY</literal> clauses, and then the results returned. |
| 1237 |
For example: |
| 1238 |
<screen> |
| 1239 |
<prompt>=></prompt> <userinput>SELECT * FROM items_sold;</userinput> |
| 1240 |
brand | size | sales |
| 1241 |
-------+------+------- |
| 1242 |
Foo | L | 10 |
| 1243 |
Foo | M | 20 |
| 1244 |
Bar | M | 15 |
| 1245 |
Bar | L | 5 |
| 1246 |
(4 rows) |
| 1247 |
|
| 1248 |
<prompt>=></prompt> <userinput>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</userinput> |
| 1249 |
brand | size | sum |
| 1250 |
-------+------+----- |
| 1251 |
Foo | | 30 |
| 1252 |
Bar | | 20 |
| 1253 |
| L | 15 |
| 1254 |
| M | 35 |
| 1255 |
| | 50 |
| 1256 |
(5 rows) |
| 1257 |
</screen> |
| 1258 |
</para> |
| 1259 |
|
| 1260 |
<para> |
| 1261 |
Each sublist of <literal>GROUPING SETS</literal> may specify zero or more columns |
| 1262 |
or expressions and is interpreted the same way as though it were directly |
| 1263 |
in the <literal>GROUP BY</literal> clause. An empty grouping set means that all |
| 1264 |
rows are aggregated down to a single group (which is output even if no |
| 1265 |
input rows were present), as described above for the case of aggregate |
| 1266 |
functions with no <literal>GROUP BY</literal> clause. |
| 1267 |
</para> |
| 1268 |
|
| 1269 |
<para> |
| 1270 |
References to the grouping columns or expressions are replaced |
| 1271 |
by null values in result rows for grouping sets in which those |
| 1272 |
columns do not appear. To distinguish which grouping a particular output |
| 1273 |
row resulted from, see <xref linkend="functions-grouping-table"/>. |
| 1274 |
</para> |
| 1275 |
|
| 1276 |
<para> |
| 1277 |
A shorthand notation is provided for specifying two common types of grouping set. |
| 1278 |
A clause of the form |
| 1279 |
<programlisting> |
| 1280 |
ROLLUP ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, <replaceable>e3</replaceable>, ... ) |
| 1281 |
</programlisting> |
| 1282 |
represents the given list of expressions and all prefixes of the list including |
| 1283 |
the empty list; thus it is equivalent to |
| 1284 |
<programlisting> |
| 1285 |
GROUPING SETS ( |
| 1286 |
( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, <replaceable>e3</replaceable>, ... ), |
| 1287 |
... |
| 1288 |
( <replaceable>e1</replaceable>, <replaceable>e2</replaceable> ), |
| 1289 |
( <replaceable>e1</replaceable> ), |
| 1290 |
( ) |
| 1291 |
) |
| 1292 |
</programlisting> |
| 1293 |
This is commonly used for analysis over hierarchical data; e.g., total |
| 1294 |
salary by department, division, and company-wide total. |
| 1295 |
</para> |
| 1296 |
|
| 1297 |
<para> |
| 1298 |
A clause of the form |
| 1299 |
<programlisting> |
| 1300 |
CUBE ( <replaceable>e1</replaceable>, <replaceable>e2</replaceable>, ... ) |
| 1301 |
</programlisting> |
| 1302 |
represents the given list and all of its possible subsets (i.e., the power |
| 1303 |
set). Thus |
| 1304 |
<programlisting> |
| 1305 |
CUBE ( a, b, c ) |
| 1306 |
</programlisting> |
| 1307 |
is equivalent to |
| 1308 |
<programlisting> |
| 1309 |
GROUPING SETS ( |
| 1310 |
( a, b, c ), |
| 1311 |
( a, b ), |
| 1312 |
( a, c ), |
| 1313 |
( a ), |
| 1314 |
( b, c ), |
| 1315 |
( b ), |
| 1316 |
( c ), |
| 1317 |
( ) |
| 1318 |
) |
| 1319 |
</programlisting> |
| 1320 |
</para> |
| 1321 |
|
| 1322 |
<para> |
| 1323 |
The individual elements of a <literal>CUBE</literal> or <literal>ROLLUP</literal> |
| 1324 |
clause may be either individual expressions, or sublists of elements in |
| 1325 |
parentheses. In the latter case, the sublists are treated as single |
| 1326 |
units for the purposes of generating the individual grouping sets. |
| 1327 |
For example: |
| 1328 |
<programlisting> |
| 1329 |
CUBE ( (a, b), (c, d) ) |
| 1330 |
</programlisting> |
| 1331 |
is equivalent to |
| 1332 |
<programlisting> |
| 1333 |
GROUPING SETS ( |
| 1334 |
( a, b, c, d ), |
| 1335 |
( a, b ), |
| 1336 |
( c, d ), |
| 1337 |
( ) |
| 1338 |
) |
| 1339 |
</programlisting> |
| 1340 |
and |
| 1341 |
<programlisting> |
| 1342 |
ROLLUP ( a, (b, c), d ) |
| 1343 |
</programlisting> |
| 1344 |
is equivalent to |
| 1345 |
<programlisting> |
| 1346 |
GROUPING SETS ( |
| 1347 |
( a, b, c, d ), |
| 1348 |
( a, b, c ), |
| 1349 |
( a ), |
| 1350 |
( ) |
| 1351 |
) |
| 1352 |
</programlisting> |
| 1353 |
</para> |
| 1354 |
|
| 1355 |
<para> |
| 1356 |
The <literal>CUBE</literal> and <literal>ROLLUP</literal> constructs can be used either |
| 1357 |
directly in the <literal>GROUP BY</literal> clause, or nested inside a |
| 1358 |
<literal>GROUPING SETS</literal> clause. If one <literal>GROUPING SETS</literal> clause |
| 1359 |
is nested inside another, the effect is the same as if all the elements of |
| 1360 |
the inner clause had been written directly in the outer clause. |
| 1361 |
</para> |
| 1362 |
|
| 1363 |
<para> |
| 1364 |
If multiple grouping items are specified in a single <literal>GROUP BY</literal> |
| 1365 |
clause, then the final list of grouping sets is the Cartesian product of the |
| 1366 |
individual items. For example: |
| 1367 |
<programlisting> |
| 1368 |
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e)) |
| 1369 |
</programlisting> |
| 1370 |
is equivalent to |
| 1371 |
<programlisting> |
| 1372 |
GROUP BY GROUPING SETS ( |
| 1373 |
(a, b, c, d), (a, b, c, e), |
| 1374 |
(a, b, d), (a, b, e), |
| 1375 |
(a, c, d), (a, c, e), |
| 1376 |
(a, d), (a, e) |
| 1377 |
) |
| 1378 |
</programlisting> |
| 1379 |
</para> |
| 1380 |
|
| 1381 |
<para> |
| 1382 |
<indexterm zone="queries-grouping-sets"> |
| 1383 |
<primary>ALL</primary> |
| 1384 |
<secondary>GROUP BY ALL</secondary> |
| 1385 |
</indexterm> |
| 1386 |
<indexterm zone="queries-grouping-sets"> |
| 1387 |
<primary>DISTINCT</primary> |
| 1388 |
<secondary>GROUP BY DISTINCT</secondary> |
| 1389 |
</indexterm> |
| 1390 |
When specifying multiple grouping items together, the final set of grouping |
| 1391 |
sets might contain duplicates. For example: |
| 1392 |
<programlisting> |
| 1393 |
GROUP BY ROLLUP (a, b), ROLLUP (a, c) |
| 1394 |
</programlisting> |
| 1395 |
is equivalent to |
| 1396 |
<programlisting> |
| 1397 |
GROUP BY GROUPING SETS ( |
| 1398 |
(a, b, c), |
| 1399 |
(a, b), |
| 1400 |
(a, b), |
| 1401 |
(a, c), |
| 1402 |
(a), |
| 1403 |
(a), |
| 1404 |
(a, c), |
| 1405 |
(a), |
| 1406 |
() |
| 1407 |
) |
| 1408 |
</programlisting> |
| 1409 |
If these duplicates are undesirable, they can be removed using the |
| 1410 |
<literal>DISTINCT</literal> clause directly on the <literal>GROUP BY</literal>. |
| 1411 |
Therefore: |
| 1412 |
<programlisting> |
| 1413 |
GROUP BY <emphasis>DISTINCT</emphasis> ROLLUP (a, b), ROLLUP (a, c) |
| 1414 |
</programlisting> |
| 1415 |
is equivalent to |
| 1416 |
<programlisting> |
| 1417 |
GROUP BY GROUPING SETS ( |
| 1418 |
(a, b, c), |
| 1419 |
(a, b), |
| 1420 |
(a, c), |
| 1421 |
(a), |
| 1422 |
() |
| 1423 |
) |
| 1424 |
</programlisting> |
| 1425 |
This is not the same as using <literal>SELECT DISTINCT</literal> because the output |
| 1426 |
rows may still contain duplicates. If any of the ungrouped columns contains NULL, |
| 1427 |
it will be indistinguishable from the NULL used when that same column is grouped. |
| 1428 |
</para> |
| 1429 |
|
| 1430 |
<note> |
| 1431 |
<para> |
| 1432 |
The construct <literal>(a, b)</literal> is normally recognized in expressions as |
| 1433 |
a <link linkend="sql-syntax-row-constructors">row constructor</link>. |
| 1434 |
Within the <literal>GROUP BY</literal> clause, this does not apply at the top |
| 1435 |
levels of expressions, and <literal>(a, b)</literal> is parsed as a list of |
| 1436 |
expressions as described above. If for some reason you <emphasis>need</emphasis> |
| 1437 |
a row constructor in a grouping expression, use <literal>ROW(a, b)</literal>. |
| 1438 |
</para> |
| 1439 |
</note> |
| 1440 |
</sect2> |
| 1441 |
|
| 1442 |
<sect2 id="queries-window"> |
| 1443 |
<title>Window Function Processing</title> |
| 1444 |
|
| 1445 |
<indexterm zone="queries-window"> |
| 1446 |
<primary>window function</primary> |
| 1447 |
<secondary>order of execution</secondary> |
| 1448 |
</indexterm> |
| 1449 |
|
| 1450 |
<para> |
| 1451 |
If the query contains any window functions (see |
| 1452 |
<xref linkend="tutorial-window"/>, |
| 1453 |
<xref linkend="functions-window"/> and |
| 1454 |
<xref linkend="syntax-window-functions"/>), these functions are evaluated |
| 1455 |
after any grouping, aggregation, and <literal>HAVING</literal> filtering is |
| 1456 |
performed. That is, if the query uses any aggregates, <literal>GROUP |
| 1457 |
BY</literal>, or <literal>HAVING</literal>, then the rows seen by the window functions |
| 1458 |
are the group rows instead of the original table rows from |
| 1459 |
<literal>FROM</literal>/<literal>WHERE</literal>. |
| 1460 |
</para> |
| 1461 |
|
| 1462 |
<para> |
| 1463 |
When multiple window functions are used, all the window functions having |
| 1464 |
equivalent <literal>PARTITION BY</literal> and <literal>ORDER BY</literal> |
| 1465 |
clauses in their window definitions are guaranteed to see the same |
| 1466 |
ordering of the input rows, even if the <literal>ORDER BY</literal> does |
| 1467 |
not uniquely determine the ordering. |
| 1468 |
However, no guarantees are made about the evaluation of functions having |
| 1469 |
different <literal>PARTITION BY</literal> or <literal>ORDER BY</literal> specifications. |
| 1470 |
(In such cases a sort step is typically required between the passes of |
| 1471 |
window function evaluations, and the sort is not guaranteed to preserve |
| 1472 |
ordering of rows that its <literal>ORDER BY</literal> sees as equivalent.) |
| 1473 |
</para> |
| 1474 |
|
| 1475 |
<para> |
| 1476 |
Currently, window functions always require presorted data, and so the |
| 1477 |
query output will be ordered according to one or another of the window |
| 1478 |
functions' <literal>PARTITION BY</literal>/<literal>ORDER BY</literal> clauses. |
| 1479 |
It is not recommended to rely on this, however. Use an explicit |
| 1480 |
top-level <literal>ORDER BY</literal> clause if you want to be sure the |
| 1481 |
results are sorted in a particular way. |
| 1482 |
</para> |
| 1483 |
</sect2> |
| 1484 |
</sect1> |
| 1485 |
|
| 1486 |
|
| 1487 |
<sect1 id="queries-select-lists"> |
| 1488 |
<title>Select Lists</title> |
| 1489 |
|
| 1490 |
<indexterm> |
| 1491 |
<primary>SELECT</primary> |
| 1492 |
<secondary>select list</secondary> |
| 1493 |
</indexterm> |
| 1494 |
|
| 1495 |
<para> |
| 1496 |
As shown in the previous section, |
| 1497 |
the table expression in the <command>SELECT</command> command |
| 1498 |
constructs an intermediate virtual table by possibly combining |
| 1499 |
tables, views, eliminating rows, grouping, etc. This table is |
| 1500 |
finally passed on to processing by the <firstterm>select list</firstterm>. The select |
| 1501 |
list determines which <emphasis>columns</emphasis> of the |
| 1502 |
intermediate table are actually output. |
| 1503 |
</para> |
| 1504 |
|
| 1505 |
<sect2 id="queries-select-list-items"> |
| 1506 |
<title>Select-List Items</title> |
| 1507 |
|
| 1508 |
<indexterm> |
| 1509 |
<primary>*</primary> |
| 1510 |
</indexterm> |
| 1511 |
|
| 1512 |
<para> |
| 1513 |
The simplest kind of select list is <literal>*</literal> which |
| 1514 |
emits all columns that the table expression produces. Otherwise, |
| 1515 |
a select list is a comma-separated list of value expressions (as |
| 1516 |
defined in <xref linkend="sql-expressions"/>). For instance, it |
| 1517 |
could be a list of column names: |
| 1518 |
<programlisting> |
| 1519 |
SELECT a, b, c FROM ... |
| 1520 |
</programlisting> |
| 1521 |
The columns names <literal>a</literal>, <literal>b</literal>, and <literal>c</literal> |
| 1522 |
are either the actual names of the columns of tables referenced |
| 1523 |
in the <literal>FROM</literal> clause, or the aliases given to them as |
| 1524 |
explained in <xref linkend="queries-table-aliases"/>. The name |
| 1525 |
space available in the select list is the same as in the |
| 1526 |
<literal>WHERE</literal> clause, unless grouping is used, in which case |
| 1527 |
it is the same as in the <literal>HAVING</literal> clause. |
| 1528 |
</para> |
| 1529 |
|
| 1530 |
<para> |
| 1531 |
If more than one table has a column of the same name, the table |
| 1532 |
name must also be given, as in: |
| 1533 |
<programlisting> |
| 1534 |
SELECT tbl1.a, tbl2.a, tbl1.b FROM ... |
| 1535 |
</programlisting> |
| 1536 |
When working with multiple tables, it can also be useful to ask for |
| 1537 |
all the columns of a particular table: |
| 1538 |
<programlisting> |
| 1539 |
SELECT tbl1.*, tbl2.a FROM ... |
| 1540 |
</programlisting> |
| 1541 |
See <xref linkend="rowtypes-usage"/> for more about |
| 1542 |
the <replaceable>table_name</replaceable><literal>.*</literal> notation. |
| 1543 |
</para> |
| 1544 |
|
| 1545 |
<para> |
| 1546 |
If an arbitrary value expression is used in the select list, it |
| 1547 |
conceptually adds a new virtual column to the returned table. The |
| 1548 |
value expression is evaluated once for each result row, with |
| 1549 |
the row's values substituted for any column references. But the |
| 1550 |
expressions in the select list do not have to reference any |
| 1551 |
columns in the table expression of the <literal>FROM</literal> clause; |
| 1552 |
they can be constant arithmetic expressions, for instance. |
| 1553 |
</para> |
| 1554 |
</sect2> |
| 1555 |
|
| 1556 |
<sect2 id="queries-column-labels"> |
| 1557 |
<title>Column Labels</title> |
| 1558 |
|
| 1559 |
<indexterm zone="queries-column-labels"> |
| 1560 |
<primary>alias</primary> |
| 1561 |
<secondary>in the select list</secondary> |
| 1562 |
</indexterm> |
| 1563 |
|
| 1564 |
<para> |
| 1565 |
The entries in the select list can be assigned names for subsequent |
| 1566 |
processing, such as for use in an <literal>ORDER BY</literal> clause |
| 1567 |
or for display by the client application. For example: |
| 1568 |
<programlisting> |
| 1569 |
SELECT a AS value, b + c AS sum FROM ... |
| 1570 |
</programlisting> |
| 1571 |
</para> |
| 1572 |
|
| 1573 |
<para> |
| 1574 |
If no output column name is specified using <literal>AS</literal>, |
| 1575 |
the system assigns a default column name. For simple column references, |
| 1576 |
this is the name of the referenced column. For function |
| 1577 |
calls, this is the name of the function. For complex expressions, |
| 1578 |
the system will generate a generic name. |
| 1579 |
</para> |
| 1580 |
|
| 1581 |
<para> |
| 1582 |
The <literal>AS</literal> key word is usually optional, but in some |
| 1583 |
cases where the desired column name matches a |
| 1584 |
<productname>PostgreSQL</productname> key word, you must write |
| 1585 |
<literal>AS</literal> or double-quote the column name in order to |
| 1586 |
avoid ambiguity. |
| 1587 |
(<xref linkend="sql-keywords-appendix"/> shows which key words |
| 1588 |
require <literal>AS</literal> to be used as a column label.) |
| 1589 |
For example, <literal>FROM</literal> is one such key word, so this |
| 1590 |
does not work: |
| 1591 |
<programlisting> |
| 1592 |
SELECT a from, b + c AS sum FROM ... |
| 1593 |
</programlisting> |
| 1594 |
but either of these do: |
| 1595 |
<programlisting> |
| 1596 |
SELECT a AS from, b + c AS sum FROM ... |
| 1597 |
SELECT a "from", b + c AS sum FROM ... |
| 1598 |
</programlisting> |
| 1599 |
For greatest safety against possible |
| 1600 |
future key word additions, it is recommended that you always either |
| 1601 |
write <literal>AS</literal> or double-quote the output column name. |
| 1602 |
</para> |
| 1603 |
|
| 1604 |
<note> |
| 1605 |
<para> |
| 1606 |
The naming of output columns here is different from that done in |
| 1607 |
the <literal>FROM</literal> clause (see <xref |
| 1608 |
linkend="queries-table-aliases"/>). It is possible |
| 1609 |
to rename the same column twice, but the name assigned in |
| 1610 |
the select list is the one that will be passed on. |
| 1611 |
</para> |
| 1612 |
</note> |
| 1613 |
</sect2> |
| 1614 |
|
| 1615 |
<sect2 id="queries-distinct"> |
| 1616 |
<title><literal>DISTINCT</literal></title> |
| 1617 |
|
| 1618 |
<indexterm zone="queries-distinct"> |
| 1619 |
<primary>ALL</primary> |
| 1620 |
<secondary>SELECT ALL</secondary> |
| 1621 |
</indexterm> |
| 1622 |
<indexterm zone="queries-distinct"> |
| 1623 |
<primary>DISTINCT</primary> |
| 1624 |
<secondary>SELECT DISTINCT</secondary> |
| 1625 |
</indexterm> |
| 1626 |
|
| 1627 |
<indexterm zone="queries-distinct"> |
| 1628 |
<primary>duplicates</primary> |
| 1629 |
</indexterm> |
| 1630 |
|
| 1631 |
<para> |
| 1632 |
After the select list has been processed, the result table can |
| 1633 |
optionally be subject to the elimination of duplicate rows. The |
| 1634 |
<literal>DISTINCT</literal> key word is written directly after |
| 1635 |
<literal>SELECT</literal> to specify this: |
| 1636 |
<synopsis> |
| 1637 |
SELECT DISTINCT <replaceable>select_list</replaceable> ... |
| 1638 |
</synopsis> |
| 1639 |
(Instead of <literal>DISTINCT</literal> the key word <literal>ALL</literal> |
| 1640 |
can be used to specify the default behavior of retaining all rows.) |
| 1641 |
</para> |
| 1642 |
|
| 1643 |
<indexterm> |
| 1644 |
<primary>null value</primary> |
| 1645 |
<secondary sortas="DISTINCT">in DISTINCT</secondary> |
| 1646 |
</indexterm> |
| 1647 |
|
| 1648 |
<para> |
| 1649 |
Obviously, two rows are considered distinct if they differ in at |
| 1650 |
least one column value. Null values are considered equal in this |
| 1651 |
comparison. |
| 1652 |
</para> |
| 1653 |
|
| 1654 |
<para> |
| 1655 |
Alternatively, an arbitrary expression can determine what rows are |
| 1656 |
to be considered distinct: |
| 1657 |
<synopsis> |
| 1658 |
SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>select_list</replaceable> ... |
| 1659 |
</synopsis> |
| 1660 |
Here <replaceable>expression</replaceable> is an arbitrary value |
| 1661 |
expression that is evaluated for all rows. A set of rows for |
| 1662 |
which all the expressions are equal are considered duplicates, and |
| 1663 |
only the first row of the set is kept in the output. Note that |
| 1664 |
the <quote>first row</quote> of a set is unpredictable unless the |
| 1665 |
query is sorted on enough columns to guarantee a unique ordering |
| 1666 |
of the rows arriving at the <literal>DISTINCT</literal> filter. |
| 1667 |
(<literal>DISTINCT ON</literal> processing occurs after <literal>ORDER |
| 1668 |
BY</literal> sorting.) |
| 1669 |
</para> |
| 1670 |
|
| 1671 |
<para> |
| 1672 |
The <literal>DISTINCT ON</literal> clause is not part of the SQL standard |
| 1673 |
and is sometimes considered bad style because of the potentially |
| 1674 |
indeterminate nature of its results. With judicious use of |
| 1675 |
<literal>GROUP BY</literal> and subqueries in <literal>FROM</literal>, this |
| 1676 |
construct can be avoided, but it is often the most convenient |
| 1677 |
alternative. |
| 1678 |
</para> |
| 1679 |
</sect2> |
| 1680 |
</sect1> |
| 1681 |
|
| 1682 |
|
| 1683 |
<sect1 id="queries-union"> |
| 1684 |
<title>Combining Queries (<literal>UNION</literal>, <literal>INTERSECT</literal>, <literal>EXCEPT</literal>)</title> |
| 1685 |
|
| 1686 |
<indexterm zone="queries-union"> |
| 1687 |
<primary>UNION</primary> |
| 1688 |
</indexterm> |
| 1689 |
<indexterm zone="queries-union"> |
| 1690 |
<primary>INTERSECT</primary> |
| 1691 |
</indexterm> |
| 1692 |
<indexterm zone="queries-union"> |
| 1693 |
<primary>EXCEPT</primary> |
| 1694 |
</indexterm> |
| 1695 |
<indexterm zone="queries-union"> |
| 1696 |
<primary>set union</primary> |
| 1697 |
</indexterm> |
| 1698 |
<indexterm zone="queries-union"> |
| 1699 |
<primary>set intersection</primary> |
| 1700 |
</indexterm> |
| 1701 |
<indexterm zone="queries-union"> |
| 1702 |
<primary>set difference</primary> |
| 1703 |
</indexterm> |
| 1704 |
<indexterm zone="queries-union"> |
| 1705 |
<primary>set operation</primary> |
| 1706 |
</indexterm> |
| 1707 |
|
| 1708 |
<para> |
| 1709 |
The results of two queries can be combined using the set operations |
| 1710 |
union, intersection, and difference. The syntax is |
| 1711 |
<synopsis> |
| 1712 |
<replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable> |
| 1713 |
<replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable> |
| 1714 |
<replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable> |
| 1715 |
</synopsis> |
| 1716 |
where <replaceable>query1</replaceable> and |
| 1717 |
<replaceable>query2</replaceable> are queries that can use any of |
| 1718 |
the features discussed up to this point. |
| 1719 |
</para> |
| 1720 |
|
| 1721 |
<para> |
| 1722 |
<literal>UNION</literal> effectively appends the result of |
| 1723 |
<replaceable>query2</replaceable> to the result of |
| 1724 |
<replaceable>query1</replaceable> (although there is no guarantee |
| 1725 |
that this is the order in which the rows are actually returned). |
| 1726 |
Furthermore, it eliminates duplicate rows from its result, in the same |
| 1727 |
way as <literal>DISTINCT</literal>, unless <literal>UNION ALL</literal> is used. |
| 1728 |
</para> |
| 1729 |
|
| 1730 |
<para> |
| 1731 |
<literal>INTERSECT</literal> returns all rows that are both in the result |
| 1732 |
of <replaceable>query1</replaceable> and in the result of |
| 1733 |
<replaceable>query2</replaceable>. Duplicate rows are eliminated |
| 1734 |
unless <literal>INTERSECT ALL</literal> is used. |
| 1735 |
</para> |
| 1736 |
|
| 1737 |
<para> |
| 1738 |
<literal>EXCEPT</literal> returns all rows that are in the result of |
| 1739 |
<replaceable>query1</replaceable> but not in the result of |
| 1740 |
<replaceable>query2</replaceable>. (This is sometimes called the |
| 1741 |
<firstterm>difference</firstterm> between two queries.) Again, duplicates |
| 1742 |
are eliminated unless <literal>EXCEPT ALL</literal> is used. |
| 1743 |
</para> |
| 1744 |
|
| 1745 |
<para> |
| 1746 |
In order to calculate the union, intersection, or difference of two |
| 1747 |
queries, the two queries must be <quote>union compatible</quote>, |
| 1748 |
which means that they return the same number of columns and |
| 1749 |
the corresponding columns have compatible data types, as |
| 1750 |
described in <xref linkend="typeconv-union-case"/>. |
| 1751 |
</para> |
| 1752 |
|
| 1753 |
<para> |
| 1754 |
Set operations can be combined, for example |
| 1755 |
<synopsis> |
| 1756 |
<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> EXCEPT <replaceable>query3</replaceable> |
| 1757 |
</synopsis> |
| 1758 |
which is equivalent to |
| 1759 |
<synopsis> |
| 1760 |
(<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable>) EXCEPT <replaceable>query3</replaceable> |
| 1761 |
</synopsis> |
| 1762 |
As shown here, you can use parentheses to control the order of |
| 1763 |
evaluation. Without parentheses, <literal>UNION</literal> |
| 1764 |
and <literal>EXCEPT</literal> associate left-to-right, |
| 1765 |
but <literal>INTERSECT</literal> binds more tightly than those two |
| 1766 |
operators. Thus |
| 1767 |
<synopsis> |
| 1768 |
<replaceable>query1</replaceable> UNION <replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable> |
| 1769 |
</synopsis> |
| 1770 |
means |
| 1771 |
<synopsis> |
| 1772 |
<replaceable>query1</replaceable> UNION (<replaceable>query2</replaceable> INTERSECT <replaceable>query3</replaceable>) |
| 1773 |
</synopsis> |
| 1774 |
You can also surround an individual <replaceable>query</replaceable> |
| 1775 |
with parentheses. This is important if |
| 1776 |
the <replaceable>query</replaceable> needs to use any of the clauses |
| 1777 |
discussed in following sections, such as <literal>LIMIT</literal>. |
| 1778 |
Without parentheses, you'll get a syntax error, or else the clause will |
| 1779 |
be understood as applying to the output of the set operation rather |
| 1780 |
than one of its inputs. For example, |
| 1781 |
<synopsis> |
| 1782 |
SELECT a FROM b UNION SELECT x FROM y LIMIT 10 |
| 1783 |
</synopsis> |
| 1784 |
is accepted, but it means |
| 1785 |
<synopsis> |
| 1786 |
(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10 |
| 1787 |
</synopsis> |
| 1788 |
not |
| 1789 |
<synopsis> |
| 1790 |
SELECT a FROM b UNION (SELECT x FROM y LIMIT 10) |
| 1791 |
</synopsis> |
| 1792 |
</para> |
| 1793 |
</sect1> |
| 1794 |
|
| 1795 |
|
| 1796 |
<sect1 id="queries-order"> |
| 1797 |
<title>Sorting Rows (<literal>ORDER BY</literal>)</title> |
| 1798 |
|
| 1799 |
<indexterm zone="queries-order"> |
| 1800 |
<primary>sorting</primary> |
| 1801 |
</indexterm> |
| 1802 |
|
| 1803 |
<indexterm zone="queries-order"> |
| 1804 |
<primary>ORDER BY</primary> |
| 1805 |
</indexterm> |
| 1806 |
|
| 1807 |
<para> |
| 1808 |
After a query has produced an output table (after the select list |
| 1809 |
has been processed) it can optionally be sorted. If sorting is not |
| 1810 |
chosen, the rows will be returned in an unspecified order. The actual |
| 1811 |
order in that case will depend on the scan and join plan types and |
| 1812 |
the order on disk, but it must not be relied on. A particular |
| 1813 |
output ordering can only be guaranteed if the sort step is explicitly |
| 1814 |
chosen. |
| 1815 |
</para> |
| 1816 |
|
| 1817 |
<para> |
| 1818 |
The <literal>ORDER BY</literal> clause specifies the sort order: |
| 1819 |
<synopsis> |
| 1820 |
SELECT <replaceable>select_list</replaceable> |
| 1821 |
FROM <replaceable>table_expression</replaceable> |
| 1822 |
ORDER BY <replaceable>sort_expression1</replaceable> <optional>ASC | DESC</optional> <optional>NULLS { FIRST | LAST }</optional> |
| 1823 |
<optional>, <replaceable>sort_expression2</replaceable> <optional>ASC | DESC</optional> <optional>NULLS { FIRST | LAST }</optional> ...</optional> |
| 1824 |
</synopsis> |
| 1825 |
The sort expression(s) can be any expression that would be valid in the |
| 1826 |
query's select list. An example is: |
| 1827 |
<programlisting> |
| 1828 |
SELECT a, b FROM table1 ORDER BY a + b, c; |
| 1829 |
</programlisting> |
| 1830 |
When more than one expression is specified, |
| 1831 |
the later values are used to sort rows that are equal according to the |
| 1832 |
earlier values. Each expression can be followed by an optional |
| 1833 |
<literal>ASC</literal> or <literal>DESC</literal> keyword to set the sort direction to |
| 1834 |
ascending or descending. <literal>ASC</literal> order is the default. |
| 1835 |
Ascending order puts smaller values first, where |
| 1836 |
<quote>smaller</quote> is defined in terms of the |
| 1837 |
<literal><</literal> operator. Similarly, descending order is |
| 1838 |
determined with the <literal>></literal> operator. |
| 1839 |
<footnote> |
| 1840 |
<para> |
| 1841 |
Actually, <productname>PostgreSQL</productname> uses the <firstterm>default B-tree |
| 1842 |
operator class</firstterm> for the expression's data type to determine the sort |
| 1843 |
ordering for <literal>ASC</literal> and <literal>DESC</literal>. Conventionally, |
| 1844 |
data types will be set up so that the <literal><</literal> and |
| 1845 |
<literal>></literal> operators correspond to this sort ordering, |
| 1846 |
but a user-defined data type's designer could choose to do something |
| 1847 |
different. |
| 1848 |
</para> |
| 1849 |
</footnote> |
| 1850 |
</para> |
| 1851 |
|
| 1852 |
<para> |
| 1853 |
The <literal>NULLS FIRST</literal> and <literal>NULLS LAST</literal> options can be |
| 1854 |
used to determine whether nulls appear before or after non-null values |
| 1855 |
in the sort ordering. By default, null values sort as if larger than any |
| 1856 |
non-null value; that is, <literal>NULLS FIRST</literal> is the default for |
| 1857 |
<literal>DESC</literal> order, and <literal>NULLS LAST</literal> otherwise. |
| 1858 |
</para> |
| 1859 |
|
| 1860 |
<para> |
| 1861 |
Note that the ordering options are considered independently for each |
| 1862 |
sort column. For example <literal>ORDER BY x, y DESC</literal> means |
| 1863 |
<literal>ORDER BY x ASC, y DESC</literal>, which is not the same as |
| 1864 |
<literal>ORDER BY x DESC, y DESC</literal>. |
| 1865 |
</para> |
| 1866 |
|
| 1867 |
<para> |
| 1868 |
A <replaceable>sort_expression</replaceable> can also be the column label or number |
| 1869 |
of an output column, as in: |
| 1870 |
<programlisting> |
| 1871 |
SELECT a + b AS sum, c FROM table1 ORDER BY sum; |
| 1872 |
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1; |
| 1873 |
</programlisting> |
| 1874 |
both of which sort by the first output column. Note that an output |
| 1875 |
column name has to stand alone, that is, it cannot be used in an expression |
| 1876 |
— for example, this is <emphasis>not</emphasis> correct: |
| 1877 |
<programlisting> |
| 1878 |
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong |
| 1879 |
</programlisting> |
| 1880 |
This restriction is made to reduce ambiguity. There is still |
| 1881 |
ambiguity if an <literal>ORDER BY</literal> item is a simple name that |
| 1882 |
could match either an output column name or a column from the table |
| 1883 |
expression. The output column is used in such cases. This would |
| 1884 |
only cause confusion if you use <literal>AS</literal> to rename an output |
| 1885 |
column to match some other table column's name. |
| 1886 |
</para> |
| 1887 |
|
| 1888 |
<para> |
| 1889 |
<literal>ORDER BY</literal> can be applied to the result of a |
| 1890 |
<literal>UNION</literal>, <literal>INTERSECT</literal>, or <literal>EXCEPT</literal> |
| 1891 |
combination, but in this case it is only permitted to sort by |
| 1892 |
output column names or numbers, not by expressions. |
| 1893 |
</para> |
| 1894 |
</sect1> |
| 1895 |
|
| 1896 |
|
| 1897 |
<sect1 id="queries-limit"> |
| 1898 |
<title><literal>LIMIT</literal> and <literal>OFFSET</literal></title> |
| 1899 |
|
| 1900 |
<indexterm zone="queries-limit"> |
| 1901 |
<primary>LIMIT</primary> |
| 1902 |
</indexterm> |
| 1903 |
|
| 1904 |
<indexterm zone="queries-limit"> |
| 1905 |
<primary>OFFSET</primary> |
| 1906 |
</indexterm> |
| 1907 |
|
| 1908 |
<para> |
| 1909 |
<literal>LIMIT</literal> and <literal>OFFSET</literal> allow you to retrieve just |
| 1910 |
a portion of the rows that are generated by the rest of the query: |
| 1911 |
<synopsis> |
| 1912 |
SELECT <replaceable>select_list</replaceable> |
| 1913 |
FROM <replaceable>table_expression</replaceable> |
| 1914 |
<optional> ORDER BY ... </optional> |
| 1915 |
<optional> LIMIT { <replaceable class="parameter">count</replaceable> | ALL } </optional> |
| 1916 |
<optional> OFFSET <replaceable class="parameter">start</replaceable> </optional> |
| 1917 |
</synopsis> |
| 1918 |
</para> |
| 1919 |
|
| 1920 |
<para> |
| 1921 |
If a limit count is given, no more than that many rows will be |
| 1922 |
returned (but possibly fewer, if the query itself yields fewer rows). |
| 1923 |
<literal>LIMIT ALL</literal> is the same as omitting the <literal>LIMIT</literal> |
| 1924 |
clause, as is <literal>LIMIT</literal> with a NULL argument. |
| 1925 |
</para> |
| 1926 |
|
| 1927 |
<para> |
| 1928 |
<literal>OFFSET</literal> says to skip that many rows before beginning to |
| 1929 |
return rows. <literal>OFFSET 0</literal> is the same as omitting the |
| 1930 |
<literal>OFFSET</literal> clause, as is <literal>OFFSET</literal> with a NULL argument. |
| 1931 |
</para> |
| 1932 |
|
| 1933 |
<para> |
| 1934 |
If both <literal>OFFSET</literal> |
| 1935 |
and <literal>LIMIT</literal> appear, then <literal>OFFSET</literal> rows are |
| 1936 |
skipped before starting to count the <literal>LIMIT</literal> rows that |
| 1937 |
are returned. |
| 1938 |
</para> |
| 1939 |
|
| 1940 |
<para> |
| 1941 |
When using <literal>LIMIT</literal>, it is important to use an |
| 1942 |
<literal>ORDER BY</literal> clause that constrains the result rows into a |
| 1943 |
unique order. Otherwise you will get an unpredictable subset of |
| 1944 |
the query's rows. You might be asking for the tenth through |
| 1945 |
twentieth rows, but tenth through twentieth in what ordering? The |
| 1946 |
ordering is unknown, unless you specified <literal>ORDER BY</literal>. |
| 1947 |
</para> |
| 1948 |
|
| 1949 |
<para> |
| 1950 |
The query optimizer takes <literal>LIMIT</literal> into account when |
| 1951 |
generating query plans, so you are very likely to get different |
| 1952 |
plans (yielding different row orders) depending on what you give |
| 1953 |
for <literal>LIMIT</literal> and <literal>OFFSET</literal>. Thus, using |
| 1954 |
different <literal>LIMIT</literal>/<literal>OFFSET</literal> values to select |
| 1955 |
different subsets of a query result <emphasis>will give |
| 1956 |
inconsistent results</emphasis> unless you enforce a predictable |
| 1957 |
result ordering with <literal>ORDER BY</literal>. This is not a bug; it |
| 1958 |
is an inherent consequence of the fact that SQL does not promise to |
| 1959 |
deliver the results of a query in any particular order unless |
| 1960 |
<literal>ORDER BY</literal> is used to constrain the order. |
| 1961 |
</para> |
| 1962 |
|
| 1963 |
<para> |
| 1964 |
The rows skipped by an <literal>OFFSET</literal> clause still have to be |
| 1965 |
computed inside the server; therefore a large <literal>OFFSET</literal> |
| 1966 |
might be inefficient. |
| 1967 |
</para> |
| 1968 |
</sect1> |
| 1969 |
|
| 1970 |
|
| 1971 |
<sect1 id="queries-values"> |
| 1972 |
<title><literal>VALUES</literal> Lists</title> |
| 1973 |
|
| 1974 |
<indexterm zone="queries-values"> |
| 1975 |
<primary>VALUES</primary> |
| 1976 |
</indexterm> |
| 1977 |
|
| 1978 |
<para> |
| 1979 |
<literal>VALUES</literal> provides a way to generate a <quote>constant table</quote> |
| 1980 |
that can be used in a query without having to actually create and populate |
| 1981 |
a table on-disk. The syntax is |
| 1982 |
<synopsis> |
| 1983 |
VALUES ( <replaceable class="parameter">expression</replaceable> [, ...] ) [, ...] |
| 1984 |
</synopsis> |
| 1985 |
Each parenthesized list of expressions generates a row in the table. |
| 1986 |
The lists must all have the same number of elements (i.e., the number |
| 1987 |
of columns in the table), and corresponding entries in each list must |
| 1988 |
have compatible data types. The actual data type assigned to each column |
| 1989 |
of the result is determined using the same rules as for <literal>UNION</literal> |
| 1990 |
(see <xref linkend="typeconv-union-case"/>). |
| 1991 |
</para> |
| 1992 |
|
| 1993 |
<para> |
| 1994 |
As an example: |
| 1995 |
<programlisting> |
| 1996 |
VALUES (1, 'one'), (2, 'two'), (3, 'three'); |
| 1997 |
</programlisting> |
| 1998 |
|
| 1999 |
will return a table of two columns and three rows. It's effectively |
| 2000 |
equivalent to: |
| 2001 |
<programlisting> |
| 2002 |
SELECT 1 AS column1, 'one' AS column2 |
| 2003 |
UNION ALL |
| 2004 |
SELECT 2, 'two' |
| 2005 |
UNION ALL |
| 2006 |
SELECT 3, 'three'; |
| 2007 |
</programlisting> |
| 2008 |
|
| 2009 |
By default, <productname>PostgreSQL</productname> assigns the names |
| 2010 |
<literal>column1</literal>, <literal>column2</literal>, etc. to the columns of a |
| 2011 |
<literal>VALUES</literal> table. The column names are not specified by the |
| 2012 |
SQL standard and different database systems do it differently, so |
| 2013 |
it's usually better to override the default names with a table alias |
| 2014 |
list, like this: |
| 2015 |
<programlisting> |
| 2016 |
=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter); |
| 2017 |
num | letter |
| 2018 |
-----+-------- |
| 2019 |
1 | one |
| 2020 |
2 | two |
| 2021 |
3 | three |
| 2022 |
(3 rows) |
| 2023 |
</programlisting> |
| 2024 |
</para> |
| 2025 |
|
| 2026 |
<para> |
| 2027 |
Syntactically, <literal>VALUES</literal> followed by expression lists is |
| 2028 |
treated as equivalent to: |
| 2029 |
<synopsis> |
| 2030 |
SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> |
| 2031 |
</synopsis> |
| 2032 |
and can appear anywhere a <literal>SELECT</literal> can. For example, you can |
| 2033 |
use it as part of a <literal>UNION</literal>, or attach a |
| 2034 |
<replaceable>sort_specification</replaceable> (<literal>ORDER BY</literal>, |
| 2035 |
<literal>LIMIT</literal>, and/or <literal>OFFSET</literal>) to it. <literal>VALUES</literal> |
| 2036 |
is most commonly used as the data source in an <command>INSERT</command> command, |
| 2037 |
and next most commonly as a subquery. |
| 2038 |
</para> |
| 2039 |
|
| 2040 |
<para> |
| 2041 |
For more information see <xref linkend="sql-values"/>. |
| 2042 |
</para> |
| 2043 |
|
| 2044 |
</sect1> |
| 2045 |
|
| 2046 |
|
| 2047 |
<sect1 id="queries-with"> |
| 2048 |
<title><literal>WITH</literal> Queries (Common Table Expressions)</title> |
| 2049 |
|
| 2050 |
<indexterm zone="queries-with"> |
| 2051 |
<primary>WITH</primary> |
| 2052 |
<secondary>in SELECT</secondary> |
| 2053 |
</indexterm> |
| 2054 |
|
| 2055 |
<indexterm> |
| 2056 |
<primary>common table expression</primary> |
| 2057 |
<see>WITH</see> |
| 2058 |
</indexterm> |
| 2059 |
|
| 2060 |
<para> |
| 2061 |
<literal>WITH</literal> provides a way to write auxiliary statements for use in a |
| 2062 |
larger query. These statements, which are often referred to as Common |
| 2063 |
Table Expressions or <acronym>CTE</acronym>s, can be thought of as defining |
| 2064 |
temporary tables that exist just for one query. Each auxiliary statement |
| 2065 |
in a <literal>WITH</literal> clause can be a <command>SELECT</command>, |
| 2066 |
<command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>, |
| 2067 |
or <command>MERGE</command>; and the |
| 2068 |
<literal>WITH</literal> clause itself is attached to a primary statement that can |
| 2069 |
also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, |
| 2070 |
<command>DELETE</command>, or <command>MERGE</command>. |
| 2071 |
</para> |
| 2072 |
|
| 2073 |
<sect2 id="queries-with-select"> |
| 2074 |
<title><command>SELECT</command> in <literal>WITH</literal></title> |
| 2075 |
|
| 2076 |
<para> |
| 2077 |
The basic value of <command>SELECT</command> in <literal>WITH</literal> is to |
| 2078 |
break down complicated queries into simpler parts. An example is: |
| 2079 |
|
| 2080 |
<programlisting> |
| 2081 |
WITH regional_sales AS ( |
| 2082 |
SELECT region, SUM(amount) AS total_sales |
| 2083 |
FROM orders |
| 2084 |
GROUP BY region |
| 2085 |
), top_regions AS ( |
| 2086 |
SELECT region |
| 2087 |
FROM regional_sales |
| 2088 |
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) |
| 2089 |
) |
| 2090 |
SELECT region, |
| 2091 |
product, |
| 2092 |
SUM(quantity) AS product_units, |
| 2093 |
SUM(amount) AS product_sales |
| 2094 |
FROM orders |
| 2095 |
WHERE region IN (SELECT region FROM top_regions) |
| 2096 |
GROUP BY region, product; |
| 2097 |
</programlisting> |
| 2098 |
|
| 2099 |
which displays per-product sales totals in only the top sales regions. |
| 2100 |
The <literal>WITH</literal> clause defines two auxiliary statements named |
| 2101 |
<structname>regional_sales</structname> and <structname>top_regions</structname>, |
| 2102 |
where the output of <structname>regional_sales</structname> is used in |
| 2103 |
<structname>top_regions</structname> and the output of <structname>top_regions</structname> |
| 2104 |
is used in the primary <command>SELECT</command> query. |
| 2105 |
This example could have been written without <literal>WITH</literal>, |
| 2106 |
but we'd have needed two levels of nested sub-<command>SELECT</command>s. It's a bit |
| 2107 |
easier to follow this way. |
| 2108 |
</para> |
| 2109 |
</sect2> |
| 2110 |
|
| 2111 |
<sect2 id="queries-with-recursive"> |
| 2112 |
<title>Recursive Queries</title> |
| 2113 |
|
| 2114 |
<para> |
| 2115 |
<indexterm> |
| 2116 |
<primary>RECURSIVE</primary> |
| 2117 |
<secondary>in common table expressions</secondary> |
| 2118 |
</indexterm> |
| 2119 |
The optional <literal>RECURSIVE</literal> modifier changes <literal>WITH</literal> |
| 2120 |
from a mere syntactic convenience into a feature that accomplishes |
| 2121 |
things not otherwise possible in standard SQL. Using |
| 2122 |
<literal>RECURSIVE</literal>, a <literal>WITH</literal> query can refer to its own |
| 2123 |
output. A very simple example is this query to sum the integers from 1 |
| 2124 |
through 100: |
| 2125 |
|
| 2126 |
<programlisting> |
| 2127 |
WITH RECURSIVE t(n) AS ( |
| 2128 |
VALUES (1) |
| 2129 |
UNION ALL |
| 2130 |
SELECT n+1 FROM t WHERE n < 100 |
| 2131 |
) |
| 2132 |
SELECT sum(n) FROM t; |
| 2133 |
</programlisting> |
| 2134 |
|
| 2135 |
The general form of a recursive <literal>WITH</literal> query is always a |
| 2136 |
<firstterm>non-recursive term</firstterm>, then <literal>UNION</literal> (or |
| 2137 |
<literal>UNION ALL</literal>), then a |
| 2138 |
<firstterm>recursive term</firstterm>, where only the recursive term can contain |
| 2139 |
a reference to the query's own output. Such a query is executed as |
| 2140 |
follows: |
| 2141 |
</para> |
| 2142 |
|
| 2143 |
<procedure> |
| 2144 |
<title>Recursive Query Evaluation</title> |
| 2145 |
|
| 2146 |
<step performance="required"> |
| 2147 |
<para> |
| 2148 |
Evaluate the non-recursive term. For <literal>UNION</literal> (but not |
| 2149 |
<literal>UNION ALL</literal>), discard duplicate rows. Include all remaining |
| 2150 |
rows in the result of the recursive query, and also place them in a |
| 2151 |
temporary <firstterm>working table</firstterm>. |
| 2152 |
</para> |
| 2153 |
</step> |
| 2154 |
|
| 2155 |
<step performance="required"> |
| 2156 |
<para> |
| 2157 |
So long as the working table is not empty, repeat these steps: |
| 2158 |
</para> |
| 2159 |
<substeps> |
| 2160 |
<step performance="required"> |
| 2161 |
<para> |
| 2162 |
Evaluate the recursive term, substituting the current contents of |
| 2163 |
the working table for the recursive self-reference. |
| 2164 |
For <literal>UNION</literal> (but not <literal>UNION ALL</literal>), discard |
| 2165 |
duplicate rows and rows that duplicate any previous result row. |
| 2166 |
Include all remaining rows in the result of the recursive query, and |
| 2167 |
also place them in a temporary <firstterm>intermediate table</firstterm>. |
| 2168 |
</para> |
| 2169 |
</step> |
| 2170 |
|
| 2171 |
<step performance="required"> |
| 2172 |
<para> |
| 2173 |
Replace the contents of the working table with the contents of the |
| 2174 |
intermediate table, then empty the intermediate table. |
| 2175 |
</para> |
| 2176 |
</step> |
| 2177 |
</substeps> |
| 2178 |
</step> |
| 2179 |
</procedure> |
| 2180 |
|
| 2181 |
<note> |
| 2182 |
<para> |
| 2183 |
While <literal>RECURSIVE</literal> allows queries to be specified |
| 2184 |
recursively, internally such queries are evaluated iteratively. |
| 2185 |
</para> |
| 2186 |
</note> |
| 2187 |
|
| 2188 |
<para> |
| 2189 |
In the example above, the working table has just a single row in each step, |
| 2190 |
and it takes on the values from 1 through 100 in successive steps. In |
| 2191 |
the 100th step, there is no output because of the <literal>WHERE</literal> |
| 2192 |
clause, and so the query terminates. |
| 2193 |
</para> |
| 2194 |
|
| 2195 |
<para> |
| 2196 |
Recursive queries are typically used to deal with hierarchical or |
| 2197 |
tree-structured data. A useful example is this query to find all the |
| 2198 |
direct and indirect sub-parts of a product, given only a table that |
| 2199 |
shows immediate inclusions: |
| 2200 |
|
| 2201 |
<programlisting> |
| 2202 |
WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( |
| 2203 |
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' |
| 2204 |
UNION ALL |
| 2205 |
SELECT p.sub_part, p.part, p.quantity * pr.quantity |
| 2206 |
FROM included_parts pr, parts p |
| 2207 |
WHERE p.part = pr.sub_part |
| 2208 |
) |
| 2209 |
SELECT sub_part, SUM(quantity) as total_quantity |
| 2210 |
FROM included_parts |
| 2211 |
GROUP BY sub_part |
| 2212 |
</programlisting> |
| 2213 |
</para> |
| 2214 |
|
| 2215 |
<sect3 id="queries-with-search"> |
| 2216 |
<title>Search Order</title> |
| 2217 |
|
| 2218 |
<para> |
| 2219 |
When computing a tree traversal using a recursive query, you might want to |
| 2220 |
order the results in either depth-first or breadth-first order. This can |
| 2221 |
be done by computing an ordering column alongside the other data columns |
| 2222 |
and using that to sort the results at the end. Note that this does not |
| 2223 |
actually control in which order the query evaluation visits the rows; that |
| 2224 |
is as always in SQL implementation-dependent. This approach merely |
| 2225 |
provides a convenient way to order the results afterwards. |
| 2226 |
</para> |
| 2227 |
|
| 2228 |
<para> |
| 2229 |
To create a depth-first order, we compute for each result row an array of |
| 2230 |
rows that we have visited so far. For example, consider the following |
| 2231 |
query that searches a table <structname>tree</structname> using a |
| 2232 |
<structfield>link</structfield> field: |
| 2233 |
|
| 2234 |
<programlisting> |
| 2235 |
WITH RECURSIVE search_tree(id, link, data) AS ( |
| 2236 |
SELECT t.id, t.link, t.data |
| 2237 |
FROM tree t |
| 2238 |
UNION ALL |
| 2239 |
SELECT t.id, t.link, t.data |
| 2240 |
FROM tree t, search_tree st |
| 2241 |
WHERE t.id = st.link |
| 2242 |
) |
| 2243 |
SELECT * FROM search_tree; |
| 2244 |
</programlisting> |
| 2245 |
|
| 2246 |
To add depth-first ordering information, you can write this: |
| 2247 |
|
| 2248 |
<programlisting> |
| 2249 |
WITH RECURSIVE search_tree(id, link, data, <emphasis>path</emphasis>) AS ( |
| 2250 |
SELECT t.id, t.link, t.data, <emphasis>ARRAY[t.id]</emphasis> |
| 2251 |
FROM tree t |
| 2252 |
UNION ALL |
| 2253 |
SELECT t.id, t.link, t.data, <emphasis>path || t.id</emphasis> |
| 2254 |
FROM tree t, search_tree st |
| 2255 |
WHERE t.id = st.link |
| 2256 |
) |
| 2257 |
SELECT * FROM search_tree <emphasis>ORDER BY path</emphasis>; |
| 2258 |
</programlisting> |
| 2259 |
</para> |
| 2260 |
|
| 2261 |
<para> |
| 2262 |
In the general case where more than one field needs to be used to identify |
| 2263 |
a row, use an array of rows. For example, if we needed to track fields |
| 2264 |
<structfield>f1</structfield> and <structfield>f2</structfield>: |
| 2265 |
|
| 2266 |
<programlisting> |
| 2267 |
WITH RECURSIVE search_tree(id, link, data, <emphasis>path</emphasis>) AS ( |
| 2268 |
SELECT t.id, t.link, t.data, <emphasis>ARRAY[ROW(t.f1, t.f2)]</emphasis> |
| 2269 |
FROM tree t |
| 2270 |
UNION ALL |
| 2271 |
SELECT t.id, t.link, t.data, <emphasis>path || ROW(t.f1, t.f2)</emphasis> |
| 2272 |
FROM tree t, search_tree st |
| 2273 |
WHERE t.id = st.link |
| 2274 |
) |
| 2275 |
SELECT * FROM search_tree <emphasis>ORDER BY path</emphasis>; |
| 2276 |
</programlisting> |
| 2277 |
</para> |
| 2278 |
|
| 2279 |
<tip> |
| 2280 |
<para> |
| 2281 |
Omit the <literal>ROW()</literal> syntax in the common case where only one |
| 2282 |
field needs to be tracked. This allows a simple array rather than a |
| 2283 |
composite-type array to be used, gaining efficiency. |
| 2284 |
</para> |
| 2285 |
</tip> |
| 2286 |
|
| 2287 |
<para> |
| 2288 |
To create a breadth-first order, you can add a column that tracks the depth |
| 2289 |
of the search, for example: |
| 2290 |
|
| 2291 |
<programlisting> |
| 2292 |
WITH RECURSIVE search_tree(id, link, data, <emphasis>depth</emphasis>) AS ( |
| 2293 |
SELECT t.id, t.link, t.data, <emphasis>0</emphasis> |
| 2294 |
FROM tree t |
| 2295 |
UNION ALL |
| 2296 |
SELECT t.id, t.link, t.data, <emphasis>depth + 1</emphasis> |
| 2297 |
FROM tree t, search_tree st |
| 2298 |
WHERE t.id = st.link |
| 2299 |
) |
| 2300 |
SELECT * FROM search_tree <emphasis>ORDER BY depth</emphasis>; |
| 2301 |
</programlisting> |
| 2302 |
|
| 2303 |
To get a stable sort, add data columns as secondary sorting columns. |
| 2304 |
</para> |
| 2305 |
|
| 2306 |
<tip> |
| 2307 |
<para> |
| 2308 |
The recursive query evaluation algorithm produces its output in |
| 2309 |
breadth-first search order. However, this is an implementation detail and |
| 2310 |
it is perhaps unsound to rely on it. The order of the rows within each |
| 2311 |
level is certainly undefined, so some explicit ordering might be desired |
| 2312 |
in any case. |
| 2313 |
</para> |
| 2314 |
</tip> |
| 2315 |
|
| 2316 |
<para> |
| 2317 |
There is built-in syntax to compute a depth- or breadth-first sort column. |
| 2318 |
For example: |
| 2319 |
|
| 2320 |
<programlisting> |
| 2321 |
WITH RECURSIVE search_tree(id, link, data) AS ( |
| 2322 |
SELECT t.id, t.link, t.data |
| 2323 |
FROM tree t |
| 2324 |
UNION ALL |
| 2325 |
SELECT t.id, t.link, t.data |
| 2326 |
FROM tree t, search_tree st |
| 2327 |
WHERE t.id = st.link |
| 2328 |
) <emphasis>SEARCH DEPTH FIRST BY id SET ordercol</emphasis> |
| 2329 |
SELECT * FROM search_tree ORDER BY ordercol; |
| 2330 |
|
| 2331 |
WITH RECURSIVE search_tree(id, link, data) AS ( |
| 2332 |
SELECT t.id, t.link, t.data |
| 2333 |
FROM tree t |
| 2334 |
UNION ALL |
| 2335 |
SELECT t.id, t.link, t.data |
| 2336 |
FROM tree t, search_tree st |
| 2337 |
WHERE t.id = st.link |
| 2338 |
) <emphasis>SEARCH BREADTH FIRST BY id SET ordercol</emphasis> |
| 2339 |
SELECT * FROM search_tree ORDER BY ordercol; |
| 2340 |
</programlisting> |
| 2341 |
This syntax is internally expanded to something similar to the above |
| 2342 |
hand-written forms. The <literal>SEARCH</literal> clause specifies whether |
| 2343 |
depth- or breadth first search is wanted, the list of columns to track for |
| 2344 |
sorting, and a column name that will contain the result data that can be |
| 2345 |
used for sorting. That column will implicitly be added to the output rows |
| 2346 |
of the CTE. |
| 2347 |
</para> |
| 2348 |
</sect3> |
| 2349 |
|
| 2350 |
<sect3 id="queries-with-cycle"> |
| 2351 |
<title>Cycle Detection</title> |
| 2352 |
|
| 2353 |
<para> |
| 2354 |
When working with recursive queries it is important to be sure that |
| 2355 |
the recursive part of the query will eventually return no tuples, |
| 2356 |
or else the query will loop indefinitely. Sometimes, using |
| 2357 |
<literal>UNION</literal> instead of <literal>UNION ALL</literal> can accomplish this |
| 2358 |
by discarding rows that duplicate previous output rows. However, often a |
| 2359 |
cycle does not involve output rows that are completely duplicate: it may be |
| 2360 |
necessary to check just one or a few fields to see if the same point has |
| 2361 |
been reached before. The standard method for handling such situations is |
| 2362 |
to compute an array of the already-visited values. For example, consider again |
| 2363 |
the following query that searches a table <structname>graph</structname> using a |
| 2364 |
<structfield>link</structfield> field: |
| 2365 |
|
| 2366 |
<programlisting> |
| 2367 |
WITH RECURSIVE search_graph(id, link, data, depth) AS ( |
| 2368 |
SELECT g.id, g.link, g.data, 0 |
| 2369 |
FROM graph g |
| 2370 |
UNION ALL |
| 2371 |
SELECT g.id, g.link, g.data, sg.depth + 1 |
| 2372 |
FROM graph g, search_graph sg |
| 2373 |
WHERE g.id = sg.link |
| 2374 |
) |
| 2375 |
SELECT * FROM search_graph; |
| 2376 |
</programlisting> |
| 2377 |
|
| 2378 |
This query will loop if the <structfield>link</structfield> relationships contain |
| 2379 |
cycles. Because we require a <quote>depth</quote> output, just changing |
| 2380 |
<literal>UNION ALL</literal> to <literal>UNION</literal> would not eliminate the looping. |
| 2381 |
Instead we need to recognize whether we have reached the same row again |
| 2382 |
while following a particular path of links. We add two columns |
| 2383 |
<structfield>is_cycle</structfield> and <structfield>path</structfield> to the loop-prone query: |
| 2384 |
|
| 2385 |
<programlisting> |
| 2386 |
WITH RECURSIVE search_graph(id, link, data, depth, <emphasis>is_cycle, path</emphasis>) AS ( |
| 2387 |
SELECT g.id, g.link, g.data, 0, |
| 2388 |
<emphasis>false, |
| 2389 |
ARRAY[g.id]</emphasis> |
| 2390 |
FROM graph g |
| 2391 |
UNION ALL |
| 2392 |
SELECT g.id, g.link, g.data, sg.depth + 1, |
| 2393 |
<emphasis>g.id = ANY(path), |
| 2394 |
path || g.id</emphasis> |
| 2395 |
FROM graph g, search_graph sg |
| 2396 |
WHERE g.id = sg.link <emphasis>AND NOT is_cycle</emphasis> |
| 2397 |
) |
| 2398 |
SELECT * FROM search_graph; |
| 2399 |
</programlisting> |
| 2400 |
|
| 2401 |
Aside from preventing cycles, the array value is often useful in its own |
| 2402 |
right as representing the <quote>path</quote> taken to reach any particular row. |
| 2403 |
</para> |
| 2404 |
|
| 2405 |
<para> |
| 2406 |
In the general case where more than one field needs to be checked to |
| 2407 |
recognize a cycle, use an array of rows. For example, if we needed to |
| 2408 |
compare fields <structfield>f1</structfield> and <structfield>f2</structfield>: |
| 2409 |
|
| 2410 |
<programlisting> |
| 2411 |
WITH RECURSIVE search_graph(id, link, data, depth, <emphasis>is_cycle, path</emphasis>) AS ( |
| 2412 |
SELECT g.id, g.link, g.data, 0, |
| 2413 |
<emphasis>false, |
| 2414 |
ARRAY[ROW(g.f1, g.f2)]</emphasis> |
| 2415 |
FROM graph g |
| 2416 |
UNION ALL |
| 2417 |
SELECT g.id, g.link, g.data, sg.depth + 1, |
| 2418 |
<emphasis>ROW(g.f1, g.f2) = ANY(path), |
| 2419 |
path || ROW(g.f1, g.f2)</emphasis> |
| 2420 |
FROM graph g, search_graph sg |
| 2421 |
WHERE g.id = sg.link <emphasis>AND NOT is_cycle</emphasis> |
| 2422 |
) |
| 2423 |
SELECT * FROM search_graph; |
| 2424 |
</programlisting> |
| 2425 |
</para> |
| 2426 |
|
| 2427 |
<tip> |
| 2428 |
<para> |
| 2429 |
Omit the <literal>ROW()</literal> syntax in the common case where only one field |
| 2430 |
needs to be checked to recognize a cycle. This allows a simple array |
| 2431 |
rather than a composite-type array to be used, gaining efficiency. |
| 2432 |
</para> |
| 2433 |
</tip> |
| 2434 |
|
| 2435 |
<para> |
| 2436 |
There is built-in syntax to simplify cycle detection. The above query can |
| 2437 |
also be written like this: |
| 2438 |
<programlisting> |
| 2439 |
WITH RECURSIVE search_graph(id, link, data, depth) AS ( |
| 2440 |
SELECT g.id, g.link, g.data, 1 |
| 2441 |
FROM graph g |
| 2442 |
UNION ALL |
| 2443 |
SELECT g.id, g.link, g.data, sg.depth + 1 |
| 2444 |
FROM graph g, search_graph sg |
| 2445 |
WHERE g.id = sg.link |
| 2446 |
) <emphasis>CYCLE id SET is_cycle USING path</emphasis> |
| 2447 |
SELECT * FROM search_graph; |
| 2448 |
</programlisting> |
| 2449 |
and it will be internally rewritten to the above form. The |
| 2450 |
<literal>CYCLE</literal> clause specifies first the list of columns to |
| 2451 |
track for cycle detection, then a column name that will show whether a |
| 2452 |
cycle has been detected, and finally the name of another column that will track the |
| 2453 |
path. The cycle and path columns will implicitly be added to the output |
| 2454 |
rows of the CTE. |
| 2455 |
</para> |
| 2456 |
|
| 2457 |
<tip> |
| 2458 |
<para> |
| 2459 |
The cycle path column is computed in the same way as the depth-first |
| 2460 |
ordering column show in the previous section. A query can have both a |
| 2461 |
<literal>SEARCH</literal> and a <literal>CYCLE</literal> clause, but a |
| 2462 |
depth-first search specification and a cycle detection specification would |
| 2463 |
create redundant computations, so it's more efficient to just use the |
| 2464 |
<literal>CYCLE</literal> clause and order by the path column. If |
| 2465 |
breadth-first ordering is wanted, then specifying both |
| 2466 |
<literal>SEARCH</literal> and <literal>CYCLE</literal> can be useful. |
| 2467 |
</para> |
| 2468 |
</tip> |
| 2469 |
|
| 2470 |
<para> |
| 2471 |
A helpful trick for testing queries |
| 2472 |
when you are not certain if they might loop is to place a <literal>LIMIT</literal> |
| 2473 |
in the parent query. For example, this query would loop forever without |
| 2474 |
the <literal>LIMIT</literal>: |
| 2475 |
|
| 2476 |
<programlisting> |
| 2477 |
WITH RECURSIVE t(n) AS ( |
| 2478 |
SELECT 1 |
| 2479 |
UNION ALL |
| 2480 |
SELECT n+1 FROM t |
| 2481 |
) |
| 2482 |
SELECT n FROM t <emphasis>LIMIT 100</emphasis>; |
| 2483 |
</programlisting> |
| 2484 |
|
| 2485 |
This works because <productname>PostgreSQL</productname>'s implementation |
| 2486 |
evaluates only as many rows of a <literal>WITH</literal> query as are actually |
| 2487 |
fetched by the parent query. Using this trick in production is not |
| 2488 |
recommended, because other systems might work differently. Also, it |
| 2489 |
usually won't work if you make the outer query sort the recursive query's |
| 2490 |
results or join them to some other table, because in such cases the |
| 2491 |
outer query will usually try to fetch all of the <literal>WITH</literal> query's |
| 2492 |
output anyway. |
| 2493 |
</para> |
| 2494 |
</sect3> |
| 2495 |
</sect2> |
| 2496 |
|
| 2497 |
<sect2 id="queries-with-cte-materialization"> |
| 2498 |
<title>Common Table Expression Materialization</title> |
| 2499 |
|
| 2500 |
<para> |
| 2501 |
A useful property of <literal>WITH</literal> queries is that they are |
| 2502 |
normally evaluated only once per execution of the parent query, even if |
| 2503 |
they are referred to more than once by the parent query or |
| 2504 |
sibling <literal>WITH</literal> queries. |
| 2505 |
Thus, expensive calculations that are needed in multiple places can be |
| 2506 |
placed within a <literal>WITH</literal> query to avoid redundant work. Another |
| 2507 |
possible application is to prevent unwanted multiple evaluations of |
| 2508 |
functions with side-effects. |
| 2509 |
However, the other side of this coin is that the optimizer is not able to |
| 2510 |
push restrictions from the parent query down into a multiply-referenced |
| 2511 |
<literal>WITH</literal> query, since that might affect all uses of the |
| 2512 |
<literal>WITH</literal> query's output when it should affect only one. |
| 2513 |
The multiply-referenced <literal>WITH</literal> query will be |
| 2514 |
evaluated as written, without suppression of rows that the parent query |
| 2515 |
might discard afterwards. (But, as mentioned above, evaluation might stop |
| 2516 |
early if the reference(s) to the query demand only a limited number of |
| 2517 |
rows.) |
| 2518 |
</para> |
| 2519 |
|
| 2520 |
<para> |
| 2521 |
However, if a <literal>WITH</literal> query is non-recursive and |
| 2522 |
side-effect-free (that is, it is a <literal>SELECT</literal> containing |
| 2523 |
no volatile functions) then it can be folded into the parent query, |
| 2524 |
allowing joint optimization of the two query levels. By default, this |
| 2525 |
happens if the parent query references the <literal>WITH</literal> query |
| 2526 |
just once, but not if it references the <literal>WITH</literal> query |
| 2527 |
more than once. You can override that decision by |
| 2528 |
specifying <literal>MATERIALIZED</literal> to force separate calculation |
| 2529 |
of the <literal>WITH</literal> query, or by specifying <literal>NOT |
| 2530 |
MATERIALIZED</literal> to force it to be merged into the parent query. |
| 2531 |
The latter choice risks duplicate computation of |
| 2532 |
the <literal>WITH</literal> query, but it can still give a net savings if |
| 2533 |
each usage of the <literal>WITH</literal> query needs only a small part |
| 2534 |
of the <literal>WITH</literal> query's full output. |
| 2535 |
</para> |
| 2536 |
|
| 2537 |
<para> |
| 2538 |
A simple example of these rules is |
| 2539 |
<programlisting> |
| 2540 |
WITH w AS ( |
| 2541 |
SELECT * FROM big_table |
| 2542 |
) |
| 2543 |
SELECT * FROM w WHERE key = 123; |
| 2544 |
</programlisting> |
| 2545 |
This <literal>WITH</literal> query will be folded, producing the same |
| 2546 |
execution plan as |
| 2547 |
<programlisting> |
| 2548 |
SELECT * FROM big_table WHERE key = 123; |
| 2549 |
</programlisting> |
| 2550 |
In particular, if there's an index on <structfield>key</structfield>, |
| 2551 |
it will probably be used to fetch just the rows having <literal>key = |
| 2552 |
123</literal>. On the other hand, in |
| 2553 |
<programlisting> |
| 2554 |
WITH w AS ( |
| 2555 |
SELECT * FROM big_table |
| 2556 |
) |
| 2557 |
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref |
| 2558 |
WHERE w2.key = 123; |
| 2559 |
</programlisting> |
| 2560 |
the <literal>WITH</literal> query will be materialized, producing a |
| 2561 |
temporary copy of <structname>big_table</structname> that is then |
| 2562 |
joined with itself — without benefit of any index. This query |
| 2563 |
will be executed much more efficiently if written as |
| 2564 |
<programlisting> |
| 2565 |
WITH w AS NOT MATERIALIZED ( |
| 2566 |
SELECT * FROM big_table |
| 2567 |
) |
| 2568 |
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref |
| 2569 |
WHERE w2.key = 123; |
| 2570 |
</programlisting> |
| 2571 |
so that the parent query's restrictions can be applied directly |
| 2572 |
to scans of <structname>big_table</structname>. |
| 2573 |
</para> |
| 2574 |
|
| 2575 |
<para> |
| 2576 |
An example where <literal>NOT MATERIALIZED</literal> could be |
| 2577 |
undesirable is |
| 2578 |
<programlisting> |
| 2579 |
WITH w AS ( |
| 2580 |
SELECT key, very_expensive_function(val) as f FROM some_table |
| 2581 |
) |
| 2582 |
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f; |
| 2583 |
</programlisting> |
| 2584 |
Here, materialization of the <literal>WITH</literal> query ensures |
| 2585 |
that <function>very_expensive_function</function> is evaluated only |
| 2586 |
once per table row, not twice. |
| 2587 |
</para> |
| 2588 |
|
| 2589 |
<para> |
| 2590 |
The examples above only show <literal>WITH</literal> being used with |
| 2591 |
<command>SELECT</command>, but it can be attached in the same way to |
| 2592 |
<command>INSERT</command>, <command>UPDATE</command>, |
| 2593 |
<command>DELETE</command>, or <command>MERGE</command>. |
| 2594 |
In each case it effectively provides temporary table(s) that can |
| 2595 |
be referred to in the main command. |
| 2596 |
</para> |
| 2597 |
</sect2> |
| 2598 |
|
| 2599 |
<sect2 id="queries-with-modifying"> |
| 2600 |
<title>Data-Modifying Statements in <literal>WITH</literal></title> |
| 2601 |
|
| 2602 |
<para> |
| 2603 |
You can use data-modifying statements (<command>INSERT</command>, |
| 2604 |
<command>UPDATE</command>, <command>DELETE</command>, or |
| 2605 |
<command>MERGE</command>) in <literal>WITH</literal>. This |
| 2606 |
allows you to perform several different operations in the same query. |
| 2607 |
An example is: |
| 2608 |
|
| 2609 |
<programlisting> |
| 2610 |
WITH moved_rows AS ( |
| 2611 |
DELETE FROM products |
| 2612 |
WHERE |
| 2613 |
"date" >= '2010-10-01' AND |
| 2614 |
"date" < '2010-11-01' |
| 2615 |
RETURNING * |
| 2616 |
) |
| 2617 |
INSERT INTO products_log |
| 2618 |
SELECT * FROM moved_rows; |
| 2619 |
</programlisting> |
| 2620 |
|
| 2621 |
This query effectively moves rows from <structname>products</structname> to |
| 2622 |
<structname>products_log</structname>. The <command>DELETE</command> in <literal>WITH</literal> |
| 2623 |
deletes the specified rows from <structname>products</structname>, returning their |
| 2624 |
contents by means of its <literal>RETURNING</literal> clause; and then the |
| 2625 |
primary query reads that output and inserts it into |
| 2626 |
<structname>products_log</structname>. |
| 2627 |
</para> |
| 2628 |
|
| 2629 |
<para> |
| 2630 |
A fine point of the above example is that the <literal>WITH</literal> clause is |
| 2631 |
attached to the <command>INSERT</command>, not the sub-<command>SELECT</command> within |
| 2632 |
the <command>INSERT</command>. This is necessary because data-modifying |
| 2633 |
statements are only allowed in <literal>WITH</literal> clauses that are attached |
| 2634 |
to the top-level statement. However, normal <literal>WITH</literal> visibility |
| 2635 |
rules apply, so it is possible to refer to the <literal>WITH</literal> |
| 2636 |
statement's output from the sub-<command>SELECT</command>. |
| 2637 |
</para> |
| 2638 |
|
| 2639 |
<para> |
| 2640 |
Data-modifying statements in <literal>WITH</literal> usually have |
| 2641 |
<literal>RETURNING</literal> clauses (see <xref linkend="dml-returning"/>), |
| 2642 |
as shown in the example above. |
| 2643 |
It is the output of the <literal>RETURNING</literal> clause, <emphasis>not</emphasis> the |
| 2644 |
target table of the data-modifying statement, that forms the temporary |
| 2645 |
table that can be referred to by the rest of the query. If a |
| 2646 |
data-modifying statement in <literal>WITH</literal> lacks a <literal>RETURNING</literal> |
| 2647 |
clause, then it forms no temporary table and cannot be referred to in |
| 2648 |
the rest of the query. Such a statement will be executed nonetheless. |
| 2649 |
A not-particularly-useful example is: |
| 2650 |
|
| 2651 |
<programlisting> |
| 2652 |
WITH t AS ( |
| 2653 |
DELETE FROM foo |
| 2654 |
) |
| 2655 |
DELETE FROM bar; |
| 2656 |
</programlisting> |
| 2657 |
|
| 2658 |
This example would remove all rows from tables <structname>foo</structname> and |
| 2659 |
<structname>bar</structname>. The number of affected rows reported to the client |
| 2660 |
would only include rows removed from <structname>bar</structname>. |
| 2661 |
</para> |
| 2662 |
|
| 2663 |
<para> |
| 2664 |
Recursive self-references in data-modifying statements are not |
| 2665 |
allowed. In some cases it is possible to work around this limitation by |
| 2666 |
referring to the output of a recursive <literal>WITH</literal>, for example: |
| 2667 |
|
| 2668 |
<programlisting> |
| 2669 |
WITH RECURSIVE included_parts(sub_part, part) AS ( |
| 2670 |
SELECT sub_part, part FROM parts WHERE part = 'our_product' |
| 2671 |
UNION ALL |
| 2672 |
SELECT p.sub_part, p.part |
| 2673 |
FROM included_parts pr, parts p |
| 2674 |
WHERE p.part = pr.sub_part |
| 2675 |
) |
| 2676 |
DELETE FROM parts |
| 2677 |
WHERE part IN (SELECT part FROM included_parts); |
| 2678 |
</programlisting> |
| 2679 |
|
| 2680 |
This query would remove all direct and indirect subparts of a product. |
| 2681 |
</para> |
| 2682 |
|
| 2683 |
<para> |
| 2684 |
Data-modifying statements in <literal>WITH</literal> are executed exactly once, |
| 2685 |
and always to completion, independently of whether the primary query |
| 2686 |
reads all (or indeed any) of their output. Notice that this is different |
| 2687 |
from the rule for <command>SELECT</command> in <literal>WITH</literal>: as stated in the |
| 2688 |
previous section, execution of a <command>SELECT</command> is carried only as far |
| 2689 |
as the primary query demands its output. |
| 2690 |
</para> |
| 2691 |
|
| 2692 |
<para> |
| 2693 |
The sub-statements in <literal>WITH</literal> are executed concurrently with |
| 2694 |
each other and with the main query. Therefore, when using data-modifying |
| 2695 |
statements in <literal>WITH</literal>, the order in which the specified updates |
| 2696 |
actually happen is unpredictable. All the statements are executed with |
| 2697 |
the same <firstterm>snapshot</firstterm> (see <xref linkend="mvcc"/>), so they |
| 2698 |
cannot <quote>see</quote> one another's effects on the target tables. This |
| 2699 |
alleviates the effects of the unpredictability of the actual order of row |
| 2700 |
updates, and means that <literal>RETURNING</literal> data is the only way to |
| 2701 |
communicate changes between different <literal>WITH</literal> sub-statements and |
| 2702 |
the main query. An example of this is that in |
| 2703 |
|
| 2704 |
<programlisting> |
| 2705 |
WITH t AS ( |
| 2706 |
UPDATE products SET price = price * 1.05 |
| 2707 |
RETURNING * |
| 2708 |
) |
| 2709 |
SELECT * FROM products; |
| 2710 |
</programlisting> |
| 2711 |
|
| 2712 |
the outer <command>SELECT</command> would return the original prices before the |
| 2713 |
action of the <command>UPDATE</command>, while in |
| 2714 |
|
| 2715 |
<programlisting> |
| 2716 |
WITH t AS ( |
| 2717 |
UPDATE products SET price = price * 1.05 |
| 2718 |
RETURNING * |
| 2719 |
) |
| 2720 |
SELECT * FROM t; |
| 2721 |
</programlisting> |
| 2722 |
|
| 2723 |
the outer <command>SELECT</command> would return the updated data. |
| 2724 |
</para> |
| 2725 |
|
| 2726 |
<para> |
| 2727 |
Trying to update the same row twice in a single statement is not |
| 2728 |
supported. Only one of the modifications takes place, but it is not easy |
| 2729 |
(and sometimes not possible) to reliably predict which one. This also |
| 2730 |
applies to deleting a row that was already updated in the same statement: |
| 2731 |
only the update is performed. Therefore you should generally avoid trying |
| 2732 |
to modify a single row twice in a single statement. In particular avoid |
| 2733 |
writing <literal>WITH</literal> sub-statements that could affect the same rows |
| 2734 |
changed by the main statement or a sibling sub-statement. The effects |
| 2735 |
of such a statement will not be predictable. |
| 2736 |
</para> |
| 2737 |
|
| 2738 |
<para> |
| 2739 |
At present, any table used as the target of a data-modifying statement in |
| 2740 |
<literal>WITH</literal> must not have a conditional rule, nor an <literal>ALSO</literal> |
| 2741 |
rule, nor an <literal>INSTEAD</literal> rule that expands to multiple statements. |
| 2742 |
</para> |
| 2743 |
|
| 2744 |
</sect2> |
| 2745 |
|
| 2746 |
</sect1> |
| 2747 |
|
| 2748 |
</chapter> |