7.2. Табличные выражения

7.2.1.1. Объдиненённые таблицы

Объединённая таблица — это таблица, полученная из двух других (реальных или производных) таблиц, в соответствии с правилами определённого типа объединения. Доступны такие типы объдинения как внутренее объединение, внешнее объединение и пересечение.

Объединения всех типов могут выполняться вложено или раздельно: при объединении таблицы T1 и T2 могут использоваться как по одиночке так и вместе. Чтобы управлять порядком выполнения объединений, вокруг предложений JOIN могут быть использованы круглые скобки. В отсутствии скобок, предложения JOIN выполняются раздельно слева направо.

Чтобы выполнить объединения таблиц, предположим, что у нас есть таблицы t1

 num | name
-----+------
   1 | a
   2 | b
   3 | c

и t2

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

и вот какие результаты мы получим при разные типах объединения:

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 строк)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 строки)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 строки)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 строки)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 строки)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 строки)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 строки)

Условие объединения, которое задаётся с ON может также содержать условия, которые непосредственно не относятся к объединению. Это может оказаться полезным для некоторых запросов, но необходимо соблюдать осторожность. Например:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 строки)

7.2.1.2. Псевдонимы таблиц и колонок

Для обычных и сложных таблиц могут быть назначены псевдонимы, которые затем могут быть использованы для ссылок на производные таблицы в остальной части запроса. Они называются псевдним таблицы.

Чтобы создать псевдоним таблицы, напишите

FROM ссылка_на_таблицу AS псевдоним

или

FROM ссылка_на_таблицу псевдоним

Ключевое слово AS не является обязательным. Псевдоним может быть любым идентификатором.

Обычное применение псевдонимов таблиц состоит в назначении более короткого идентифкатора для длинного имени таблицы, чтобы сделать, например, запрос объединения таблиц более читабельным. Например:

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

The alias becomes the new name of the table reference for the current query — it is no longer possible to refer to the table by the original name. Thus

SELECT * FROM my_table AS m WHERE my_table.a > 5;

is not valid SQL syntax. What will actually happen (this is a PostgreSQL extension to the standard) is that an implicit table reference is added to the FROM clause, so the query is processed as if it were written as

SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;

which will result in a cross join, which is usually not what you want.

Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.,

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...

Additionally, an alias is required if the table reference is a subquery (see Section 7.2.1.3).

Parentheses are used to resolve ambiguities. The following statement will assign the alias b to the result of the join, unlike the previous example:

SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself:

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

If fewer column aliases are specified than the actual table has columns, the remaining columns are not renamed. This syntax is especially useful for self-joins or subqueries.

When an alias is applied to the output of a JOIN clause, using any of these forms, the alias hides the original names within the JOIN. For example,

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

is valid SQL, but

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

is not valid: the table alias a is not visible outside the alias c.

7.2.1.3. Subqueries

Subqueries specifying a derived table must be enclosed in parentheses and must be assigned a table alias name. (See Section 7.2.1.2.) For example:

FROM (SELECT * FROM table1) AS alias_name

This example is equivalent to FROM table1 AS alias_name. More interesting cases, which can't be reduced to a plain join, arise when the subquery involves grouping or aggregation.

7.2.1.4. Table Functions

Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions may be included in SELECT, JOIN, or WHERE clauses in the same manner as a table, view, or subquery column.

If a table function returns a base data type, the single result column is named like the function. If the function returns a composite type, the result columns get the same names as the individual attributes of the type.

A table function may be aliased in the FROM clause, but it also may be left unaliased. If a function is used in the FROM clause with no alias, the function name is used as the resulting table name.

Some examples:

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (select foosubid from getfoo(foo.fooid) z
                           where z.fooid = foo.fooid);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

In some cases it is useful to define table functions that can return different column sets depending on how they are invoked. To support this, the table function can be declared as returning the pseudotype record. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan the query. Consider this example:

SELECT *
    FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

The dblink function executes a remote query (see contrib/dblink). It is declared to return record since it might be used for any kind of query. The actual column set must be specified in the calling query so that the parser knows, for example, what * should expand to.