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

T1 join_type T2 [ join_condition ]

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

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

 число | имя
-----+------
   1 | a
   2 | b
   3 | c

и t2:

 число | значение
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

в результате мы получим следующие данные для различных соединений:

=> SELECT * FROM t1 CROSS JOIN t2;
 число | имя | число | значение
-----+------+-----+-------
   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;
 число | имя | число | значение
-----+------+-----+-------
   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;
 число | имя | число | значение
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 строки)

=> 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;
 число | имя | число | значение
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 строки)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 число | имя | число | значение
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 строки)

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

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 число | имя | число | значение
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 строки)

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

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 число | имя | число | значение
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 строка)

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

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

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

предложение FROM table_reference AS псевдоним

или

предложение FROM table_reference псевдоним

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

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

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;    -- неправильно

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

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] псевдоним ( 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.

Подзапросы, определяющие производную таблицу, должны быть заключены в круглые скобки. Для них может быть задано имя псевдонима таблицы и, по желанию, имена псевдонимов столбцов (как в Раздел 2.4.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)

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

Согласно стандарту SQL, для подзапроса обязательно должно быть указано имя псевдонима таблицы. Digital Q.DataBase позволяет 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 will be added to the function result columns. В этом столбце строки результирующего набора функции нумеруются по порядку, начиная с 1. (Это расширение синтаксиса стандарта SQL для команды UNNEST ... WITH ORDINALITY.) По умолчанию столбец порядковых номеров называется ordinality, но ему можно присвоить другое имя, используя предложение AS предложение.

Специальная табличная функция UNNEST может быть вызвана с произвольным количеством параметров-массивов и возвращает соответствующее количество столбцов, как если бы функция UNNEST (Раздел 2.6.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] псевдоним (column_definition [, ... ])
function_call AS [псевдоним] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

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

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

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

Ключевое dblink функция (часть dblink модуля) выполняет удаленный запрос. Она объявлена как возвращающая значение типа record, 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;