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

T1 join_type T2 [ join_condition ]

Соединения всех типов могут объединяться или вкладываться друг в друга: как 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 rows)

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

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

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

=> 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 rows)

=> 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 rows)

=> 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 rows)

Условие соединения, указанное с помощью 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 rows)

Обратите внимание, что указание ограничения в предложении WHERE приводит к другому результату:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)

Это происходит потому, что ограничение, указанное в предложении ON, обрабатывается до соединения, в то время как ограничение, указанное в предложении WHERE, обрабатывается после соединения. Это не имеет значения для внутренних соединений, но имеет большое значение для внешних соединений.

Таблицам и сложным ссылкам на таблицы можно задать временное имя, и использовать его для обращений к производной таблице в запросе. Такое имя называется псевдонимом таблицы.

Для создания псевдонима таблицы напишите

FROM table_reference AS alias

или

FROM table_reference alias

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

Типичное применение псевдонимов таблиц - это присвоение коротких идентификаторов длинным именам таблиц для обеспечения читаемости предложений соединения. Например:

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

Псевдоним становится новым именем ссылки на таблицу в пределах текущего запроса - нельзя будет обратиться к таблице по исходному имени в другом месте запроса. Таким образом, следующая запись будет недопустима:

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

Псевдонимы таблицы в основном используются для удобства записи, но они также необходимы при соединении таблицы с самой собой, например:

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

Круглые скобки используются во избежание неоднозначностей определения. В следующем примере первый оператор присваивает псевдоним b второму экземпляру my_table, но второй оператор присваивает псевдоним результату соединения:

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

Другой вариант присвоения псевдонимов таблицы - указание временных имен для столбцов таблицы, а также для самой таблицы:

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

Если указано меньше псевдонимов столбцов, чем есть на самом деле в таблице, оставшиеся столбцы не переименовываются. Этот синтаксис особенно полезен для самосоединений или подзапросов.

Когда псевдоним применяется к результату оператора JOIN, псевдоним скрывает исходные имена внутри оператора JOIN. Например:

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

это допустимая запись SQL, но:

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

не является допустимой записью; псевдоним таблицы a не виден за пределами псевдонима c.

Подзапросы, указывающие производную таблицу, должны быть заключены в скобки. Им может быть присвоено псевдоним таблицы и, при необходимости, псевдонимы столбцов (как в Раздел 7.2.1.2). Например:

FROM (SELECT * FROM table1) AS alias_name

Этот пример эквивалентен FROM table1 AS alias_name. Более интересные случаи, которые нельзя свести к обычному соединению, возникают, когда подзапрос включает группировку или агрегацию.

Подзапросом также может являться список VALUES:

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

Тут тоже псевдоним таблицы является необязательным. Присвоение псевдонимов столбцам списка VALUES является необязательным, но это хорошая практика. Для получения дополнительной информации см. Раздел 7.7.

Согласно стандарту SQL, для подзапроса должно быть указано имя псевдонима таблицы. Tantor SE позволяет опустить AS и псевдоним, но его указание является хорошей практикой в SQL-коде, который может быть перенесен в другую систему.

Табличные функции — это функции, которые выдают набор строк, состоящих из базовых типов данных (скалярных типов) или составных типов данных (строк таблицы). Они используются в запросах как таблица, представление или подзапросы в предложении FROM. Столбцы, возвращаемые табличными функциями, могут быть включены в предложения SELECT, JOIN или WHERE так же, как столбцы таблицы, представления или подзапроса.

Табличные функции также могут быть объединены с использованием синтаксиса ROWS FROM, результаты при этом возвращаются в параллельных столбцах. Количество строк в этом случае равно количеству строк в наибольшем из результатов функции, а результаты с меньшими количеством строк будут дополнены значениями null.

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

Если указано предложение WITH ORDINALITY, к результатам функции будет добавлен дополнительный столбец типа bigint. Этот столбец нумерует строки результата набора функции, начиная с 1. (Это обобщение синтаксиса стандарта SQL для UNNEST ... WITH ORDINALITY). По умолчанию, столбец с порядковым номером называется ordinality, но можно назначить ему другое имя с помощью предложения AS.

Специальная табличная функция UNNEST может быть вызвана с любым количеством параметров-массивов и возвращает соответствующее количество столбцов, как если бы UNNEST (Раздел 9.19) был вызван для каждого параметра отдельно и объединен с использованием конструкции ROWS FROM.

UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

Если не указан table_alias, то имя функции используется в качестве имени таблицы; в случае конструкции ROWS FROM(), используется имя первой функции.

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

Некоторые примеры:

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;

В некоторых случаях полезно определить табличные функции, которые могут возвращать различные наборы столбцов в зависимости от того, как они вызываются. Для поддержки этого табличная функция может быть объявлена с возвращаемым псевдотипом record без параметров OUT. Когда такая функция используется в запросе, ожидаемая структура строки должна быть указана в самом запросе, чтобы система могла знать, как разбирать и планировать запрос. Синтаксис выглядит следующим образом:

function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

Когда не используется синтаксис ROWS FROM(), список column_definition заменяет список псевдонимов столбцов, который в противном случае может быть присоединен к элементу FROM; имена в определениях столбцов служат псевдонимами столбцов. При использовании синтаксиса ROWS FROM(), список column_definition может быть присоединен к каждой отдельной функции-члену; или если есть только одна функция-член и нет предложения WITH ORDINALITY, вместо списка псевдонимов столбцов после ROWS FROM() может быть записан список column_definition.

Рассмотрим следующий пример:

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

Функция dblink (часть модуля dblink) выполняет удаленный запрос. Она объявлена с возвращаемым типом record, так как может использоваться для любого типа запроса. Фактический набор столбцов должен быть указан в вызывающем запросе, чтобы парсер знал, например, что должно быть раскрыто вместо *.

В этом примере используется ROWS FROM:

SELECT *
FROM ROWS FROM
    (
        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
            AS (a INTEGER, b TEXT),
        generate_series(1, 3)
    ) AS x (p, q, s)
ORDER BY p;

  p  |  q  | s
-----+-----+---
  40 | foo | 1
 100 | bar | 2
     |     | 3

Он объединяет две функции в одну целевую FROM. json_to_recordset() указано вернуть два столбца: первый integer и второй text. Результат generate_series() используется напрямую. Предложение ORDER BY сортирует значения столбца как целые числа.

Перед подзапросами, появляющимся в FROM можно добавлять ключевое слово LATERAL. Это позволяет им ссылаться на столбцы, предоставляемые предшествующими элементами FROM. (Без LATERAL каждый подзапрос оценивается независимо и поэтому не может ссылаться на другие элементы FROM).

Перед функциями таблицы в предложении FROM также можно указывать ключевое слово LATERAL, но для функций это ключевое слово является необязательным; аргументы функции могут содержать ссылки на столбцы предыдущих элементов FROM в любом случае.

Элемент LATERAL может появиться на верхнем уровне в списке FROM, или внутри дерева JOIN. В последнем случае он также может ссылаться на любые элементы, которые находятся с левой стороны JOIN, если он находится с правой стороны.

Когда элемент FROM содержит ссылки LATERAL, запрос происходит следующим образом: для каждой строки элемента FROM, с целевыми столбцами, или набора строк из нескольких элементов FROM, содержащих целевые столбцы, элемент LATERAL вычисляется с использованием значений этой строки или набора строк столбцов. Результирующие строки объединяются, как обычно, с вычисленными из них строками. Это повторяется для каждой строки или набора строк из исходной таблицы столбцов.

Простой пример LATERAL:

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

В данном случае это не особенно полезно, так как такой же результат можно получить более традиционным способом:

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

LATERAL в первую очередь полезен, когда столбец с перекрестными ссылками необходим для вычисления объединяемой строки (строк). Часто применяется при предоставлении значения аргумента для функции, возвращающей набор. Например, предположим, что vertices(polygon) возвращает набор вершин многоугольника, тогда мы можем определить близкие вершины многоугольников, хранящихся в таблице:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
     LATERAL vertices(p1.poly) v1,
     LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

Этот запрос также может быть записан так:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

или переписать другими способами. (Как уже упоминалось, ключевое слово LATERAL в этом примере необязательно, но мы используем его для ясности).

Часто бывает особенно удобно использовать LEFT JOIN с LATERAL подзапросом, чтобы исходные строки появлялись в результате даже в том случае, если LATERAL подзапрос не возвращает для них строк. Например, если get_product_names() возвращает названия продуктов, произведенных производителем, но некоторые производители в нашей таблице в настоящее время не производят продукты, можно узнать, какие из них это такие, вот так:

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;