Запросы

В предыдущих разделах описывалось, как создать таблицы, как наполнить их данными и как манипулировать этими данными. В данном разделе наконец объясняется, как получить данные из базы данных.

Обзор

Процесс или команда получения данных из базы данных называется запросом. В SQL для создания запросов используется команда SELECT. Общий синтаксис команды SELECT выглядит так:

[WITH запросы_with] SELECT список_выборки FROM табличное_выражение [определение_сортировки]

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

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

SELECT * FROM table1;

Если предположить, что существует таблица с именем table1, то эта команда извлечет все строки и все определенные пользователем столбцы из table1. (Метод выдачи результата зависит от клиентского приложения. Например, программа psql отобразит на экране таблицу ASCII, хотя клиентские библиотеки предложат функции для извлечения отдельных значений из результата запроса). В качестве списка выборки указан символ *, означающий, что в результате выполнения запроса вернутся все столбцы табличного выражения. В списке выборки можно также указать подмножество доступных столбцов или выполнить вычисления с этими столбцами. Например, если в table1 есть столбцы с именами a, b и c (и, возможно, другие), можно выполнить следующий запрос:

SELECT a, b + c FROM table1;

(предполагая, что b и c имеют числовой тип данных). Более подробную информацию см. в разделе Списки выборки.

FROM table1 — это простейший вид табличного выражения, который читает только одну таблицу. В общем случае табличные выражения могут быть сложными комбинациями базовых таблиц, соединений и подзапросов. Но можно и полностью опустить табличное выражение и использовать команду SELECT как калькулятор:

SELECT 3 * 4;

Это будет иметь больший смысл, если выражения в списке выборки возвращают меняющиеся результаты. Например, можно вызвать функцию так:

SELECT random();

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

Табличное выражение вычисляет таблицу. Оно содержит предложение FROM, за которым при необходимости могут идти предложения WHERE, GROUP BY и HAVING. Простые табличные выражения просто ссылаются на таблицу на диске, также называемую базовой, но в более сложных выражениях базовые таблицы можно изменять или комбинировать различными способами.

Необязательные предложения WHERE, GROUP BY и HAVING в табличных выражениях задают последовательность преобразований, выполняемых с таблицей, полученной в предложении FROM. В результате этих преобразований создается виртуальная таблица, строки которой передаются в список выборки для вычисления выходных строк запроса.

Предложение FROM

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

FROM ссылка_на_таблицу [, ссылка_на_таблицу [, ...]]

Ссылка на таблицу может быть именем таблицы (возможно, дополненным схемой) или производной таблицей, например, подзапросом, соединением таблиц или их сложной комбинацией. Если в предложении FROM указано более одной такой ссылки, таблицы соединяются перекрестно (то есть образуется декартово произведение их строк; см. ниже). Результатом списка FROM является промежуточная виртуальная таблица, которая затем может преобразовываться с помощью предложений WHERE, GROUP BY и HAVING, и в итоге станет результатом общего табличного выражения.

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

Вместо добавление ONLY перед именем таблицы можно написать * после имени, явно указав, что должны обрабатываться и дочерние таблицы. Однако практических причин использовать этот синтаксис больше нет, поскольку поиск в дочерних таблицах теперь производится по умолчанию.

Соединенные таблицы

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

T1 тип_соединения T2 [ условие_соединения ]

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

Типы соединений

Перекрестное соединение

T1 CROSS JOIN T2

Для каждой возможной комбинации строк из T1 и T2 (т. е. их декартова произведения) соединенная таблица будет содержать строку, состоящую из всех столбцов T1 и следующих за ними столбцов T2. Если таблицы содержат N и M строк соответственно, соединенная таблица будет содержать N * M строк.

FROM T1 CROSS JOIN T2 равнозначно FROM T1 INNER JOIN T2 ON TRUE (см. ниже). Также эта запись равнозначна FROM T1, T2.

Примечание
Последний вариант не полностью равнозначен первым двум, если в соединении указано более двух таблиц, поскольку JOIN связывает сильнее, чем запятая. Например, FROM T1 CROSS JOIN T2 INNER JOIN T3 ON условие не равнозначно FROM T1, T2 INNER JOIN T3 ON условие так как условие может ссылаться на T1 в первом случае, но не во втором.

Соединения с сопоставлением строк

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON логическое_выражение
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( список столбцов соединения )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

Слова INNER и OUTER необязательны во всех формах. INNER — это значение по умолчанию; LEFT, RIGHT и FULL подразумевают внешнее соединение.

Условие соединения указывается в предложении ON или USING или неявно словом NATURAL. Это условие определяет, какие строки из двух исходных таблиц считаются «совпадающими», как подробно рассматривается ниже.

Возможные типы соединений с сопоставлением строк:

INNER JOIN
Для каждой строки R1 из T1 в соединенной таблице содержится строка для каждой строки из T2, которая удовлетворяет условию соединения с R1.

LEFT OUTER JOIN
Сначала выполняется внутреннее соединение. Затем для каждой строки T1, которая не удовлетворяют условию соединения ни с одной строкой в T2, добавляется соединенная строка со значениями NULL в столбцах T2. Таким образом, в соединенной таблице всегда будет присутствовать хотя бы одна строка для каждой строки T1.

RIGHT OUTER JOIN
Сначала выполняется внутреннее соединение. Затем для каждой строки T2, которая не удовлетворяют условию соединения ни с одной строкой в T1, добавляется соединенная строка со значениями NULL в столбцах T1. Таким образом, в соединенной таблице всегда будет присутствовать хотя бы одна строка для каждой строки T2.

FULL OUTER JOIN
Сначала выполняется внутреннее соединение. Затем для каждой строки T1, которая не удовлетворяют условию соединения ни с одной строкой в T2, добавляется соединенная строка со значениями NULL в столбцах T2. А для каждой строки T2, которая не удовлетворяют условию соединения ни с одной строкой в T1, добавляется соединенная строка со значениями NULL в столбцах T1.

Предложение ON является наиболее общим видом условия соединения: в нем указываются выражения логического типа, такие же, как те, что используются в предложении WHERE. Пара строк из T1 и T2 соответствуют друг другу, если выражение ON возвращает true.

Предложение USING — это сокращенная запись, позволяющая извлечь пользу в особой ситуации, когда обе стороны соединения используют одинаковые имена столбцов. Оно принимает разделенный запятыми список имен общих столбцов и формирует условие соединения с равенством этих столбцов. Например, соединение T1 и T2 с помощью USING (a, b) создает условие ON T1.a = T2.a AND T1.b = T2.b.

Кроме того, при выводе JOIN USING исключаются избыточные столбцы: нет необходимости выводить оба сопоставленных столбца, так как они имеют одинаковые значения. Тогда как JOIN ON выводит все столбцы из T1, а затем все столбцы из T2, JOIN USING создает один столбец для каждой из перечисленных пар столбцов (в указанном порядке), затем все оставшиеся столбцы из T1 и наконец все оставшиеся столбцы из T2.

Наконец, NATURAL — это сокращенная форма USING: оно формирует список USING, состоящий из всех имен столбцов, которые появляются в обеих входных таблицах. Как и с USING, в выходной таблице эти столбцы появляются только один раз. Если нет общих имен столбцов, NATURAL JOIN ведет себя как JOIN ... ON TRUE, выводя перекрестное соединение таблиц.

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

Рассмотрим вышеизложенное на примерах. Допустим, у нас есть таблицы 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 — после. Это не имеет значения при внутренних соединениях, но очень важно при внешних.

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

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

Псевдоним можно создать с помощью ключевого слова AS:

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

Или:

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

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

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

SELECT * FROM некое_очень_длинное_имя_таблицы s JOIN другое_довольно_длинное_имя 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 табличная_ссылка [AS] псевдоним ( столбец1 [, столбец2 [, ...]] )

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

Когда псевдоним применяется к результату JOIN, он скрывает изначальные имена таблиц внутри JOIN. Например, следующий запрос SQL возможен:

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

а запрос:

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

выдаст ошибку, так как псевдоним таблицы a не виден снаружи псевдонима c.

Подзапросы

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

FROM (SELECT * FROM table1) AS псевдоним

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

Кроме того, подзапрос может быть списком VALUES:

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

Опять же, тут требуется псевдоним таблицы. Назначать псевдонимы столбцам списка VALUES необязательно, но это хорошая практика. Дополнительную информацию см. в разделе Списки VALUES.

Табличные функции

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

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

вызов_функции [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_столбца [, ... ])]]
ROWS FROM( вызов_функции [, ... ] ) [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_столбца [, ... ])]]

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

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

UNNEST( выражение_массива [, ... ] ) [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_столбца [, ... ])]]

Если псевдоним_таблицы не указан, то в качестве имени таблицы используется имя функции, а в случае c конструкцией 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;

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

вызов_функции [AS] псевдоним (определение_столбца [, ... ])
вызов_функции AS [псевдоним] (определение_столбца [, ... ])
ROWS FROM( ... вызов_функции AS (определение_столбца [, ... ]) [, ... ] )

Если синтаксис ROWS FROM() не используется, список определений_столбцов заменяет список псевдонимов столбцов, который в противном случае можно было бы добавить в предложение FROM; имена в определениях столбцов служат псевдонимами. При использовании ROWS FROM() список определений_столбцов можно добавить к каждой функции отдельно; или если имеется только одна функция и нет предложения WITH ORDINALITY, список определений_столбцов можно записать вместо списка псевдонимов после 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, поскольку его можно использовать для любого запроса. Здесь фактический набор столбцов необходимо указать в вызывающем запросе, чтобы анализатор запроса знал, например, как преобразовать *.

В этом примере используется 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 упорядочивает значения этого столбца как целочисленные.

Подзапросы LATERAL

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

Ключевое слово LATERAL также можно указать перед табличными функциями в предложении FROM, но для них оно не необязательно; аргументы функций в любом случае могут содержать ссылки на столбцы в предыдущих элементах 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;

Предложение WHERE

Синтаксис предложения WHERE:

WHERE условие_поиска

где условие_поиска — это любое выражение значения (см. раздел Выражения значения), возвращающее значение типа boolean.

После обработки предложения FROM каждая строка получившейся виртуальной таблицы проверяется по условию поиска. Если результат условия равен true, строка остается в выходной таблице, а иначе (т. е. если результат равен false или NULL) отбрасывается. Обычно условие поиска ссылается как минимум на один столбец таблицы, получившейся в предложении FROM; это не требуется, но в противном случае предложение WHERE будет довольно бессмысленным.

Примечание
Условие для внутреннего соединения можно записать как в предложении WHERE, так и в предложении JOIN. Например, это табличное выражение:

FROM a, b WHERE a.id = b.id AND b.val > 5

равнозначно этому:

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

и, возможно, даже этому:

FROM a NATURAL JOIN b WHERE b.val > 5

Какой вариант использовать, в основном дело вкуса. Синтаксис JOIN в предложении FROM, наверное, не самый портируемый на другие SQL-СУБД, хотя он и описан в стандарте SQL. Но для внешних соединений других вариантов нет: их нужно записывать в предложении FROM. Предложения ON и USING во внешних соединениях не равнозначны условию WHERE, так как они могут добавлять строки (для несовпадающих входных строк), а также удалять их из конечного результата.

Несколько примеров предложений WHERE:

SELECT ... FROM fdt WHERE c1 > 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

fdt — это таблица, получившаяся в предложении FROM. Строки, не соответствующие условию поиска предложения WHERE, исключаются из fdt. Обратите внимание на использование скалярных подзапросов в качестве выражений значения. Как и любые другие запросы, подзапросы могут реализовывать сложные табличные выражения. Также обратите внимание, как на fdt ссылаются подзапросы. Дополнение имени c1 в виде fdt.c1 требуется, только если c1 также является именем столбца в получившейся входной таблице этого подзапроса. Но дополнение именем столбца придает ясности, даже когда в ней нет необходимости. Этот пример показывает, как область именования столбцов внешнего запроса распространяется на его внутренние запросы.

Предложения GROUP BY и HAVING

После прохождения фильтра WHERE получившуюся входную таблицу можно подвергнуть группированию с помощью предложения GROUP BY, а затем исключить группу строк с помощью предложения HAVING.

SELECT список_выборки
    FROM ...
    [WHERE ...]
    GROUP BY ссылка_на_группирующий_столбец [, ссылка_на_группирующий_столбец]...

Предложение GROUP BY применяется для группирования тех строк в таблице, у которых во всех перечисленных столбцах совпадают значения. Порядок, в котором перечислены столбцы, значения не имеет. В результате каждый набор строк с одинаковыми значениями столбцов совмещается в одну групповую строку, представляющую все строки группы. Целью этого процесса является устранение избыточности в выходных данных и/или вычисление агрегатных функций, применимых к этим группам. Например:

=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

Во втором запросе мы не могли написать SELECT * FROM test1 GROUP BY x, поскольку для столбца y нет единого значения, которое можно было бы связать с каждой группой. Однако к группирующим столбцам можно обращаться в списке выборки, так как они имеют единственное значение в каждой группе.

В целом, если таблица сгруппирована, к столбцам, не перечисленным в GROUP BY, можно обращаться только в агрегатных выражениях. Пример с агрегатными выражениями:

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)

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

Совет
Группирование без агрегатных выражений в сущности выдает набор различающихся значений в столбце. Этот же результат можно получить с помощью предложения DISTINCT (см. подраздел DISTINCT ниже).

Еще один пример: в нем вычисляется общая сумма продаж по каждому товару (а не общая сумма по всем товарам):

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

В этом примере столбцы product_id, p.name и p.price должны находиться в предложении GROUP BY, поскольку к ним обращаются в списке выборки запроса (но см. ниже). Столбцу s.units необязательно присутствовать в списке GROUP BY, так как он используется только в агрегатном выражении (sum(...)), представляющем сумму продаж товара. Для каждого товара этот запрос возвращает строку с итоговой суммой по всем продажам этого товара.

Если бы таблица products была настроена так, чтобы, скажем, по столбцу product_id был создан первичный ключ, этого было бы достаточно, чтобы в вышеописанном примере сгруппировать строки по product_id, поскольку название и цена функционально зависят от ID товара и можно однозначно определить, какие название и цену возвращать для каждой группы по ID товара.

В стандарте SQL GROUP BY может группировать только по столбцам исходной таблицы, но QHB расширяет эту функциональность, позволяя GROUP BY группировать также и по столбцам в списке выборки. Также разрешается группировать по выражениям значения, а не просто по именам столбцов.

Если таблица была сгруппирована с помощью GROUP BY, но интерес представляют только определенные группы, можно воспользоваться предложением HAVING, действующим подобно предложению WHERE, и убрать лишние группы из результата. Синтаксис при этом такой:

SELECT список_выборки FROM ... [WHERE ...] GROUP BY ...
  HAVING логическое_выражение

Выражения в предложении HAVING могут обращаться как к группирующим выражениям, так и к выражениям, не участвующим в группировке (которые обязательно должны быть агрегирующими функциями).

Пример:

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

Более реалистичный пример:

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

В примере выше предложение WHERE выбирает строки по несгруппированному столбцу (выражение истинно только для продаж за последние четыре недели), тогда как предложение HAVING ограничивает выходные данные группами с общей суммой валовой выручки больше 5000. Обратите внимание, что агрегатные выражения не обязательно должны быть одинаковыми во всех частях запроса.

Если в запросе есть вызовы агрегатных функций, но нет предложения GROUP BY, группирование все равно произойдет: результатом будет одна строка группы (или, возможно, ни одной строки, если эта строка будет исключена предложением HAVING). Это справедливо и для запросов, содержащих только предложение HAVING, но не вызовы агрегатных функции или предложение GROUP BY.

GROUPING SETS, CUBE и ROLLUP

Более сложные, чем описанные выше, операции группировки возможны с концептом группирующих наборов. Данные, выбранные предложениями FROM и WHERE группируются отдельно каждым заданным группирующим набором, затем для каждой группы вычисляются агрегатные функции, совсем как для простых предложений GROUP BY, после чего возвращаются результаты. Например:

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

В каждом подсписке GROUPING SETS можно задать ноль и более столбцов или выражений, которые воспринимаются так же, как если бы они находились непосредственно в предложении GROUP BY. Пустой группирующий набор означает, что все строки агрегируются до одной группы (которая выводится, даже если входных строк не было), как описано выше для агрегатных функций без предложения GROUP BY.

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

Для указания двух распространенных типов группирующих наборов предусмотрена краткая запись. Предложение формы

ROLLUP ( e1, e2, e3, ... )

представляет заданный список выражений и всех префиксов списка, включая пустой список; то есть оно равнозначно записи:

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 ),
    ( e1 ),
    ( )
)

Обычно это применяется для анализа иерархических данных, например, общей зарплаты по отделам, подразделениям и компании в целом.

Предложение формы

CUBE (e1, e2, ...)

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

CUBE (a, b, c)

равнозначно

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         )
)

Отдельными элементами предложений CUBE и ROLLUP могут быть либо отдельные выражения, либо подсписки элементов в скобках. В последнем случае подсписки обрабатываются как атомарные единицы с целью создания отдельных группирующих наборов. Например:

CUBE ((a, b), (c, d))

равнозначно

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b       ),
    (       c, d ),
    (            )
)

и

ROLLUP (a, (b, c), d)

равнозначно

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a          ),
    (            )
)

Конструкции CUBE и ROLLUP можно использовать либо непосредственно в предложении GROUP BY, либо вложенными в предложение GROUPING SETS. Если одно предложение GROUPING SETS вкладывается внутрь другого, результат будет таким же, как если бы все элементы внутреннего предложения были записаны непосредственно во внешнем.

Если в одном предложении GROUP BY задается несколько группирующих элементов, то итоговый список группирующих наборов является векторным произведением этих отдельных элементов. Например:

GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))

равнозначно

GROUP BY GROUPING SETS (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)

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

GROUP BY ROLLUP (a, b), ROLLUP (a, c)
is equivalent to

GROUP BY GROUPING SETS (
    (a, b, c),
    (a, b),
    (a, b),
    (a, c),
    (a),
    (a),
    (a, c),
    (a),
    ()
)

Если эти дубликаты нежелательны, их можно удалить, добавив предложение DISTINCT непосредственно в GROUP BY. Таким образом,

GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)

равнозначно

GROUP BY GROUPING SETS (
    (a, b, c),
    (a, b),
    (a, c),
    (a),
    ()
)

Это не то же самое, что SELECT DISTINCT, так как выходные строки все равно могут содержать дубликаты. Если любой из не участвующих в группировании столбцов содержит NULL, он будет неотличим от значения NULL, полученного при группировании этого же столбца.

Примечание
Конструкция (a, b) обычно воспринимается в выражениях как конструктор строки. Однако в предложении GROUP BY на верхних уровнях выражений это не работает, и запись (a, b) разбирается как список выражений (как описано выше). Если по какой-то причине вам нужен конструктор строки группирующем выражении, используйте запись ROW(a, b).

Обработка оконных функций

Если запрос содержит какие-либо оконные функции (см. разделы Оконные функции и Вызовы оконных функций и таблицу Оконные функции), эти функции вычисляются после каждой группировки, агрегации и фильтрации HAVING. То есть, если запрос использует какие-либо агрегатные функции, предложения GROUP BY или HAVING, тогда строки, видимые для оконных функций, будут сгруппированными строками, а не исходными строками таблицы из FROM/WHERE.

При использовании нескольких оконных функций все функции, имеющие в определениях синтаксически равнозначные предложения PARTITION BY и ORDER BY, гарантированно обрабатывают данные за один проход. Следовательно, они увидят один порядок сортировки, даже если ORDER BY не определяет однозначный порядок. Однако для функций, имеющих разные предложения PARTITION BY и ORDER BY, никаких гарантий не дается. (В таких случаях между проходами вычислений оконных функций обычно требуется этап сортировки, и эта сортировка не гарантирует сохранение порядка строк, который ее ORDER BY считает равнозначным.)

На данный момент оконные функции всегда требуют предварительно отсортированные данные, поэтому результат запроса будет упорядочен в соответствии с теми или иными предложениями PARTITION BY/ORDER BY оконных функций. Однако полагаться на это не стоит. Если хотите добиться определенного порядка сортировки результатов, явно используйте предложение ORDER BY на верхнем уровне запроса.

Списки выборки

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

Элементы списка выборки

Простейшим списком выборки является элемент *, который выбирает все столбцы, выдаваемые табличным выражением. В других случаях список выборки представляет собой список выражений значения через запятую (как определено в разделе Выражения значения). Например, это может быть список имен столбцов:

SELECT a, b, c FROM ...

Имена столбцов a, b и c являются либо фактическими именами столбцов таблиц, указанных в предложении FROM, либо их псевдонимами, заданными как описано в подразделе Псевдонимы таблиц и столбцов. Пространство имен, доступное в списке выборки, то же, что и в предложении WHERE, если не используется группировка. В последнем случае оно совпадает с пространством имен в предложении HAVING.

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

SELECT tbl1.a, tbl2.a, tbl1.b FROM ...

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

SELECT tbl1.*, tbl2.a FROM ...

Подробную информацию о записи имя_таблицы.* см. в подразделе Использование составных типов в запросах.

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

Метки столбцов

Записям в списке выборки можно назначить имена для последующей обработки, например, для использования в предложении ORDER BY или для отображения в клиентском приложении. Например:

SELECT a AS value, b + c AS sum FROM ...

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

Ключевое слово AS обычно можно не указывать, но в некоторых случаях, когда желаемое имя столбца совпадает с ключевым словом QHB, следует написать AS или заключить имя столбца в кавычки, чтобы избежать неоднозначности. (В приложении Ключевые слова SQL перечислены ключевые слова, для которых нужно использовать AS в качестве метки столбца.) Например, FROM — ключевое слово, поэтому такая запись не будет работать:

SELECT a from, b + c AS sum FROM ...

а любая из этих сработает:

SELECT a AS from, b + c AS sum FROM ...
SELECT a "from", b + c AS sum FROM ...

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

Примечание
Именование выходных столбцов здесь отличается от того, что происходит в предложении FROM (см. подраздел Псевдонимы таблиц и столбцов). Один и тот же столбец можно переименовать дважды, но передастся то имя, которое было назначено в списке выборки.

DISTINCT

После обработки списка выборки в результирующей таблице при необходимости можно убрать дублирующиеся строки. Для этого сразу после SELECT записывается ключевое слово DISTINCT:

SELECT DISTINCT список_выборки ...

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

Разумеется, две строки считаются разными, если они содержат уникальные значения как минимум в одном столбце. При таком сравнении значения NULL рассматриваются как равные.

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

SELECT DISTINCT ON (выражение [, выражение ...]) список_выборки ...

Здесь выражение — это произвольное выражение значения, вычисляемое для всех строк. Набор строк, для которых все эти выражения одинаковы, считаются дубликатами, и в выходных данных остается только первая строка из набора. Обратите внимание, что «первая строка» набора может быть любой, если только запрос не отсортирован по достаточному числу столбцов, чтобы гарантировать однозначный порядок строк, поступающих в фильтр DISTINCT. (Обработка DISTINCT ON производится после сортировки ORDER BY.)

Предложение DISTINCT ON не включено в стандарт SQL, и иногда его применение считается дурным тоном из-за потенциальной непредсказуемости его результатов. При разумном использовании GROUP BY и подзапросов во FROM можно обойтись без этой конструкции, но зачастую она является наиболее удобной альтернативой.

Сочетание запросов (UNION, INTERSECT, EXCEPT)

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

запрос1 UNION [ALL] запрос2
запрос1 INTERSECT [ALL] запрос2
запрос1 EXCEPT [ALL] запрос2

где запрос1 и запрос2 — это запросы, в которых могут использоваться все функциональные возможности, рассмотренные ранее.

UNION по сути добавляет результат запроса2 к результату запроса1 (хотя нет гарантии, что строки возвращаются именно в таком порядке). Более того, при этом из результата убираются все дублирующиеся строки, тем же способом, как это делает DISTINCT, если только не указано UNION ALL.

INTERSECT возвращает все строки, содержащиеся как в результате запроса1, так и в результате запроса2. Дублирующиеся строки убираются, если только не указано INTERSECT ALL.

EXCEPT возвращает все строки, содержащиеся в результате запроса1, но отсутствующие в результате запроса2. (Иногда это называется разницей двух запросов.) Опять же, дублирующиеся строки убираются, если только не указано EXCEPT ALL.

Чтобы вычислить объединение, пересечение или разницу двух запросов, эти два запроса должны быть «совместимыми для объединения», что означает, что они возвращают одинаковое количество столбцов и у соответствующих столбцов совместимые типы данных, как описано в разделе UNION, CASE и связанные конструкции.

Операции над множествами можно сочетать, например:

запрос1 UNION запрос2 UNION запрос3

что равнозначно

(запрос1 UNION запрос2) UNION запрос3

Как показано выше, для управления порядком вычисления можно использовать скобки. Без скобок UNION и EXCEPT объединяются слева направо, но INTERSECT имеет больший приоритет, чем эти два оператора. Таким образом,

запрос1 UNION запрос2 INTERSECT запрос3

означает

запрос1 UNION (запрос2 INTERSECT запрос3)

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

SELECT a FROM b UNION SELECT x FROM y LIMIT 10

допустимо, но означает

(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10

а не

SELECT a FROM b UNION (SELECT x FROM y LIMIT 10)

Сортировка строк (ORDER BY)

После того как запрос выдал выходную таблицу (после обработки списка выборки), при необходимости ее можно отсортировать. Если сортировка не выбрана, строки вернутся в неопределенном порядке. В этом случае фактический порядок строк будет зависеть от вида плана сканирования и соединения, а также от порядка данных на диске, но на него не следует полагаться. Определенный порядок выводимых строк гарантируется, только если этап сортировки задан явно.

Порядок сортировки задает предложение ORDER BY:

SELECT список_выборки
    FROM табличное_выражение
    ORDER BY выражение_сортировки1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, выражение_сортировки2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

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

SELECT a, b FROM table1 ORDER BY a + b, c;

Когда задается более одного выражения, последующие значения используются для сортировки строк, в которых совпали предыдущие значения. После каждого выражения при необходимости можно указать ключевое слово ASC или DESC, которые устанавливают направление сортировки по возрастанию или убыванию соответственно. По умолчанию установлено ASC. При сортировке по возрастанию сначала идут меньшие значения, где понятие «меньшие» определяется в контексте оператора <. Аналогично сортировка по убыванию определяется оператором >. 1

Для определения того, должны ли при сортировке значения NULL располагаться до или после отличных от NULL значений, можно применить параметры NULLS FIRST и NULLS LAST соответственно. По умолчанию значения NULL считаются больше всех остальных; то есть NULLS FIRST по умолчанию применяется для порядка DESC и NULLS LAST в противном случае.

Обратите внимание, что параметры сортировки рассматриваются независимо для каждого сортируемого столбца. Например, ORDER BY x, y DESC означает ORDER BY x ASC, y DESC, и это не то же самое, что ORDER BY x DESC, y DESC.

выражение_сортировки также может быть меткой столбца или номером выводимого столбца, как в данном примере:

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

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

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;

Это ограничение введено для уменьшения неоднозначности. Тем не менее неоднозначность возможна, если элементом ORDER BY является простое имя, которое может соответствовать либо имени выводимого столбца, либо столбцу из табличного выражения. В таких случаях используется выводимый столбец. Это может вызвать путаницу, только если переименовать выводимый столбец с помощью AS, чтобы его имя соответствовало имени столбца в какой-то другой таблице.

ORDER BY можно применить к результату сочетания UNION, INTERSECT и EXCEPT, однако в этом случае сортировка разрешена только по именам или номерам выводимых столбцов, но не по выражениям.

1

На самом деле для определения порядка сортировки для ASC и DESC QHB использует класс операторов B-дерева по умолчанию. Обычно типы данных устанавливаются так, чтобы этому порядку сортировки соответствовали операторы < и >, однако можно разработать собственный тип данных с иным поведением.

LIMIT и OFFSET

LIMIT и OFFSET позволяют получить только часть строк из тех, что сгенерировал остальной запрос:

SELECT список_выборки
    FROM табличное_выражение
    [ ORDER BY ... ]
    [ LIMIT { число | ALL } ] [ OFFSET число ]

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

OFFSET указывает пропустить заданное число строк, прежде чем начать возвращать строки. OFFSET 0 равнозначно исключению предложения OFFSET, как и OFFSET с аргументом NULL.

Если в запросе содержатся и OFFSET, и LIMIT, то сначала система пропускает OFFSET строк, а затем начинает подсчитывать LIMIT строк, которые будут возвращены.

При использовании LIMIT важно применять также предложение ORDER BY, выстраивающее результирующие строки в определенном порядке. Иначе вы получите непредсказуемые подмножества строк запроса. Можно запросить строки с десятой по двадцатую, но в каком порядке они будут выданы? Порядок будет неизвестен, если не указать ORDER BY.

Оптимизатор запросов учитывает LIMIT при генерировании планов запросов, поэтому, скорее всего, в зависимости от значений LIMIT и OFFSET вы получите разные планы (выдающие строки в разном порядке). Таким образом, использование разных значений LIMIT/OFFSET для выбора разных подмножеств результата запроса приведет к несогласованности результатов, если не установить предсказуемую сортировку результатов с помощью ORDER BY. Это не ошибка, а закономерное следствие того, что SQL не гарантирует вывод результатов запроса в каком-либо определенном порядке, если этот порядок не установлен жестко предложением ORDER BY.

Строки, пропускаемые согласно предложению OFFSET, все равно должны вычисляться на сервере; поэтому при больших значениях OFFSET может быть неэффективен.

Списки VALUES

VALUES дает возможность сгенерировать «константную таблицу», которую можно использовать в запросе без необходимости действительно создавать и наполнять таблицу на диске. Синтаксис этой команды таков:

VALUES ( выражение [, ...] ) [, ...]

Для каждого списка выражений в скобках создается строка в таблице. Во всех этих списках должно быть одинаковое число элементов (т. е. число столбцов в таблице) и соответствующие записи в каждом списке должны иметь совместимые типы данных. Фактический тип данных, назначенный каждому столбцу результата, определяется по тем же правилам, что и для UNION (см. раздел UNION, CASE и связанные конструкции).

Например:

VALUES (1, 'one'), (2, 'two'), (3, 'three');

вернет таблицу из двух столбцов и трех строк. Это равнозначно следующему запросу:

SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

По умолчанию QHB назначает столбцам таблицы VALUES имена column1, column2 и т. д. Имена столбцов не определены в стандарте SQL и в других СУБД они могут быть другими, поэтому обычно лучше переопределять имена по умолчанию списком псевдонимов таблицы, например так:

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

Синтаксически команда VALUES с последующим списком выражений воспринимается как равнозначная этой записи:

SELECT список_выборки FROM табличное_выражение

и допускается везде, где допустим SELECT. К примеру, ее можно использовать как часть UNION или добавить к ней определение_сортировки (ORDER BY, LIMIT и/или OFFSET). Чаще всего VALUES используется как источник данных в команде INSERT либо в качестве подзапроса.

Дополнительную информацию см. на справочной странице команды VALUES.

Запросы WITH (общие табличные выражения)

WITH дает возможность записывать вспомогательные операторы для применения в более крупных запросах. Эти операторы, также часто называемые общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, которые существуют только для одного запроса. Каждый вспомогательный оператор в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE, а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT, INSERT, UPDATE или DELETE.

SELECT в WITH

Основное назначение SELECT в WITH заключается в разбиении сложных запросов на более простые части. Например, запрос:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

отображает итоги по продажам каждого товара только для регионов, лидирующих по продажам. Предложение WITH определяет два вспомогательных оператора, именуемых regional_sales и top_regions, где выходные данные regional_sales используются в top_regions, а выходные данные top_regions — в основном запросе SELECT. Этот пример можно было бы записать и без WITH, но тогда бы понадобилось два уровня вложенных подзапросов SELECT. Способ, показанный выше, немного проще.

Рекурсивные запросы

Необязательный модификатор RECURSIVE превращает WITH из просто удобной синтаксической конструкции в средство достижения того, что иным способом невозможно в стандартном SQL. Используя RECURSIVE, запрос WITH может обращаться к собственным выходным данным. Очень простой пример, суммирующий целые числа от 1 до 100:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

В общем виде рекурсивный запрос WITH всегда является не рекурсивной частью, потом идет UNION (или UNION ALL), потом рекурсивная часть, где только в рекурсивной части можно обратиться к результату этого же запроса. Подобный запрос выполняется следующим образом:

Вычисление рекурсивного запроса

  1. Вычисляется не рекурсивная часть. Для UNION (но не UNION ALL) отбрасываются дублирующиеся строки. Все оставшиеся строки включаются в результат рекурсивного запроса, а также помещаются во временную рабочую таблицу.

  2. Пока рабочая таблица не опустеет, повторяются следующие этапы:

    • Вычисляется рекурсивная часть, заменяя текущее содержимое рабочей таблицы рекурсивными ссылками на этот же запрос. Для UNION (но не UNION ALL) отбрасываются дублирующиеся строки и строки, дублирующие предыдущие результирующие строки. Все оставшиеся строки включаются в результат рекурсивного запроса, а также помещаются во временную промежуточную таблицу.

    • Содержимое рабочей таблицы заменяется содержимым промежуточной таблицы, а затем промежуточная таблица опустошается.

      Примечание
      Строго говоря, этот процесс является итерацией, а не рекурсией, но комитетом по стандартам SQL был выбран термин RECURSIVE.

В показанном выше примере рабочая таблица на каждом этапе содержит всего одну строку, и в ней последовательно набираются значения от 1 до 100. На сотом этапе, благодаря предложению WHERE, не выводится ничего, поэтому запрос завершается.

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

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

Порядок поиска

При вычислении обхода дерева при помощи рекурсивного запроса может возникнуть необходимость отсортировать результаты в порядке «сначала в глубину» или «сначала в ширину». Для этого можно в дополнение к остальным столбцам данных вычислить упорядочивающий столбец и использовать его для сортировки полученных результатов. Обратите внимание, что это на самом деле не определяет порядок обхода строк запросом; этот порядок, как всегда, зависит от реализации SQL. Такой столбец просто предоставляет удобный способ упорядочить полученные результаты.

Чтобы отсортировать результаты в порядке «сначала в глубину», для каждой результирующей строки вычисляется массив уже просмотренных строк. Например, рассмотрим следующий запрос, выполняющий поиск в таблице tree по полю link:

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree;

Чтобы добавить информацию для упорядочивания «сначала в глубину», можно написать следующее:

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[t.id]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || t.id
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

В общем случае, когда для идентификации строки нужно использовать более одного поля, следует использовать массив строк. Например, если требуется отследить поля f1 и f2:

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2)
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

Совет
В этом общем случае, где нужно отслеживать только одно поле, элемент ROW() можно опустить. Это позволит использовать не массив составного типа, а простой массив, повысив тем самым эффективность.

Чтобы отсортировать результаты в порядке «сначала в ширину», можно добавить столбец, отслеживающий глубину поиска, например:

WITH RECURSIVE search_tree(id, link, data, depth) AS (
    SELECT t.id, t.link, t.data, 0
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, depth + 1
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY depth;

Для обеспечения стабильной сортировки добавьте столбцы данных в качестве столбцов вторичной сортировки.

Совет
Этот алгоритм вычисления рекурсивного запроса выдает свой результат в порядке поиска «сначала в ширину». Однако это особенность реализации, и, вероятно, полагаться на него неразумно. Порядок строк внутри каждого уровня, разумеется, не определен, поэтому в любом случае может понадобиться явное упорядочивание.

Для вычисления столбца, упорядочивающего «сначала в глубину» или «сначала в ширину», существует встроенный синтаксис. Например:

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

Внутри этот синтаксис разворачивается в форму, похожую на вышеописанные. В предложении SEARCH указывается, какой тип поиска требуется — «сначала в глубину» или «сначала в ширину», список столбцов, которые будут отслеживаться для сортировки, и имя столбца, который будет содержать результирующие данные, которые можно использовать для сортировки. Этот столбец будет неявно добавлен в выходные строки CTE.

Выявление циклов

Работая с рекурсивными запросами, важно позаботиться о том, чтобы рекурсивная часть запроса в итоге не возвратила никаких кортежей, иначе этот запрос превратится в бесконечный цикл. Иногда, воспользовавшись UNION вместо UNION ALL, этого можно добиться, отбрасывая строки, дублирующие предыдущие выходные строки. Однако зачастую в цикле нет полностью совпадающих выходных строк: в таких случаях может понадобиться проверить одно или несколько полей, чтобы увидеть, не была ли эта же точка достигнута раньше. Стандартный метод решения таких задач — вычислить массив уже просмотренных значений. Например, снова рассмотрим следующий запрос, просматривающий таблицу graph по полю link:

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
)
SELECT * FROM search_graph;

Этот запрос зациклится, если отношения link содержат циклы. Поскольку нам нужно получить данные, упорядоченные «в ширину», простое изменение UNION ALL на UNION не уберет угрозу зацикливания. Вместо этого нам необходимо определить, достигали ли мы уже этой же строки, пройдя некоторый путь из ссылок. Мы добавляем два столбца, is_cycle и path, в склонный к зацикливанию запрос:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[g.id],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || g.id,
      g.id = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Помимо предотвращения циклов, значение массива часто бывает полезно само по себе как представление «пути», приведшего к определенной строке.

В общем случае, когда для выявления цикла нужно проверить более одного поля, следует использовать массив строк. Например, если нужно сравнить поля f1 и f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[ROW(g.f1, g.f2)],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || ROW(g.f1, g.f2),
      ROW(g.f1, g.f2) = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Совет
В этом общем случае, где для выявления цикла нужно проверять только одно поле, элемент ROW() можно опустить. Это позволит использовать не массив составного типа, а простой массив, повысив тем самым эффективность.

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

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;

и внутри он будет переписан в вышеуказанную форму. В предложении CYCLE сначала задается список столбцов для отслеживания на предмет выявления цикла, потом имя столбца, который покажет, был ли выявлен цикл, и наконец, имя еще одного столбца, в котором будет отслеживаться путь. Столбцы, указывающие цикл и путь, будут неявно добавлены в выходные строки CTE.

Совет
Столбец, указывающий путь цикла, вычисляется тем же способом, что и столбец для упорядочивания «сначала в глубину», показанным в предыдущем подразделе. Запрос может содержать одновременно предложения SEARCH и CYCLE, но при этом будут производиться излишние вычисления, поэтому эффективнее использовать только предложение CYCLE и упорядочивать результаты по столбцу пути. Однако если требуется порядок «сначала в ширину», одновременное указание SEARCH и CYCLE может быть полезным.

Для тестирования запросов в отсутствие уверенности, что они могут зациклиться, есть полезный прием — поместить LIMIT в родительский запрос. Например, этот запрос войдет в бесконечный цикл, если не добавить LIMIT:

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

Но в данном случае этого не произойдет, потому что реализация QHB вычисляет ровно столько строк запроса WITH, сколько фактически выбрал родительский запрос. В производственной среде использовать этот прием не рекомендуется, так как другие системы могут работать по-другому. Кроме того, это обычно не работает, если внешний запрос сортирует результаты рекурсивного запроса или соединяет их с какой-нибудь другой таблицей, потому что в подобных случаях внешний запрос обычно все равно пытается выбрать результат запроса WITH полностью.

Материализация общих табличных выражений

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

Однако если запрос WITH не рекурсивный и свободен от побочных эффектов (то есть это SELECT, не содержащий изменчивых функций), то его можно свернуть в родительский запрос, что позволит совместно оптимизировать два уровня запросов. По умолчанию это происходит, если родительский запрос обращается к запросу WITH только один раз, но не при многократных обращениях. Это решение можно переопределить, указав MATERIALIZED, чтобы принудительно отделить вычисление запроса WITH, или NOT MATERIALIZED, чтобы присовокупить его к родительскому запросу. В последнем случае возникает риск дублирования вычисления запроса WITH, но это все равно может быть выгодно, если при каждом использовании запроса WITH требуется только небольшая часть из его полного результата.

Простой пример демонстрации этих правил:

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

Этот запрос WITH будет свернут, выполняя тот же план, что и этот запрос:

SELECT * FROM big_table WHERE key = 123;

В частности, если был создан индекс по столбцу key, вероятно, он будет использован для извлечения строк с key = 123. С другой стороны, в записи

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

запрос WITH будет материализован, создавая временную копию таблицы big_table, которая затем будет соединена сама с собой — без участия какого-либо индекса. Этот запрос будет выполняться гораздо эффективнее, если записать его в следующем виде:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

чтобы ограничения родительского запроса могли применяться непосредственно при сканировании big_table.

Пример, в котором NOT MATERIALIZED может быть нежелателен:

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Здесь благодаря материализации запроса WITH очень затратная функция very_expensive_function вычисляется для строки таблицы только один раз, а не два.

Примеры выше показывают только WITH, применяемое с SELECT, но таким же образом его можно добавлять в команды INSERT, UPDATE или DELETE. В каждом случае он фактически предоставляет временную таблицу (или таблицы), к которой можно обращаться в основной команде.

Операторы изменения данных в WITH

В предложении WITH можно использовать операторы, изменяющие данные (INSERT, UPDATE или DELETE). Это позволяет выполнять в одном запросе сразу несколько различных операций. Например:

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

Этот запрос по сути перемещает строки из products в products_log. Оператор DELETE в WITH удаляет указанные строки из products, возвращая их содержимое посредством своего предложения RETURNING, а затем главный запрос читает эти данные и вставляет их в products_log.

В примере выше есть один нюанс — предложение WITH присоединяется к оператору INSERT, а не к SELECT, вложенному в INSERT. Это необходимо, поскольку операторы, изменяющие данные, допустимы только в предложениях WITH, присоединяемых к оператору верхнего уровня. Однако при этом применяются обычные правила видимости WITH, так что к результату оператора с WITH можно обратиться и из подзапроса SELECT.

У операторов, изменяющих данные, в WITH обычно имеются предложения RETURNING (см. раздел Возврат данных из измененных строк), как показано в примере выше. Именно из результата предложения RETURNING, а не целевой таблицы оператора, изменяющего данные, формируется временная таблица, к которой можно будет обращаться в остальном запросе. Если у оператора, изменяющего данные, в WITH отсутствует предложение RETURNING, то временная таблица не создается и к ней нельзя будет обратиться в остальном запросе. Однако такой оператор все равно будет выполнен. Не особенно практичный пример:

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

Запрос в этом примере удалит все строки из таблиц foo и bar. В число обработанных строк, о котором будет сообщено клиенту, войдут только строки, удаленные из bar.

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

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

Этот запрос удалит все непосредственные и косвенные составные части товара.

Операторы, изменяющие данные, в WITH выполняются ровно один раз и всегда полностью, независимо от того, прочитает ли основной запрос их результат полностью или хотя бы частично. Обратите внимание, что это отличается от правила для SELECT в WITH: как говорилось в предыдущем разделе, SELECT выполняется только до тех пор, пока основному запросу требуются его его выходные данные.

Вложенные операторы в WITH выполняются одновременно друг с другом и с основным запросом. Таким образом, при использовании в WITH операторов, изменяющих данные, порядок, в котором указанные изменения фактически происходят, непредсказуем. Все эти операторы выполняются с одним снимком данных (см. главу Управление параллельным доступом), так что они не могут «видеть», как каждый из них воздействует на целевые таблицы. Это сглаживает эффекты непредсказуемости фактического порядка изменения строк и означает, что данные из RETURNING являются единственным способом передачи изменений от различных вложенных операторов WITH основному запросу. Например, в данном случае:

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

внешний SELECT вернет оригинальные цены, которые были до действия UPDATE, тогда как в запросе

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

внешний SELECT вернет измененные данные.

Изменение одной и той же строки дважды в рамках одного оператора не поддерживается. Произойдет только одно из изменений, но надежно предсказать, какое именно, нелегко (а иногда и вовсе невозможно). Это же касается удаления строки, которая уже была изменена в том же операторе: выполнится только изменение. Поэтому следует избегать попыток изменить строку дважды в одном оператора. В частности, следует избегать добавления вложенных операторов WITH, которые могут воздействовать на строки, изменяемые основным оператором или соседними вложенными операторами. Результат действия такого оператора будет непредсказуем.

В настоящее время для целевой таблицы оператора, изменяющего данные в WITH, не должно быть определено условное правило или правило ALSO или INSTEAD, распространяющееся на несколько операторов.

Copyright © KVANTOM LLC, 2019-2024