SELECT

SELECT, TABLE, WITH — выбрать строки из таблицы или представления

Синтаксис

[ WITH [ RECURSIVE ] запрос_WITH [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( выражение [, ...] ) ] ]
    [ * | выражение [ [ AS ] имя_вывода ] [, ...] ]
    [ FROM элемент_FROM [, ...] ]
    [ WHERE условие ]
    [ GROUP BY элемент_группирования [, ...] ]
    [ HAVING условие ]
    [ WINDOW имя_окна AS ( определение_окна ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] выборка ]
    [ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { число | ALL } ]
    [ OFFSET начало [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ число ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF имя_таблицы [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

где элементом_FROM может быть:

    [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним [ ( псевдоним_столбца [, ...] ) ] ]
                [ TABLESAMPLE метод_выборки ( аргумент [, ...] ) [ REPEATABLE ( затравка ) ] ]
    [ LATERAL ] ( выборка ) [ AS ] псевдоним [ ( псевдоним_столбца [, ...] ) ]
    имя_запроса_WITH [ [ AS ] псевдоним [ ( псевдоним_столбца [, ...] ) ] ]
    [ LATERAL ] имя_функции ( [ аргумент [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] псевдоним [ ( псевдоним_столбца [, ...] ) ] ]
    [ LATERAL ] имя_функции ( [ аргумент [, ...] ] ) [ AS ] псевдоним ( определение_столбца [, ...] )
    [ LATERAL ] имя_функции ( [ аргумент [, ...] ] ) AS ( определение_столбца [, ...] )
    [ LATERAL ] ROWS FROM( имя_функции ( [ аргумент [, ...] ] ) [ AS ( определение_столбца [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] псевдоним [ ( псевдоним_столбца [, ...] ) ] ]
    элемент_FROM тип_соединения элемент_FROM { ON условие_соединения | USING ( столбец_соединения [, ...] ) [ AS псевдоним_использования_соединения ] }
    элемент_FROM NATURAL тип_соединения элемент_FROM
    элемент_FROM CROSS JOIN элемент_FROM

и элементом_группирования может быть:

    ( )
    выражение
    ( выражение [, ...] )
    ROLLUP ( { выражение | ( выражение [, ...] ) } [, ...] )
    CUBE ( { выражение | ( выражение [, ...] ) } [, ...] )
    GROUPING SETS ( элемент_группирования [, ...] )

и запрос_WITH:

    имя_запроса_WITH [ ( имя_столбца [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
        [ SEARCH { BREADTH | DEPTH } FIRST BY имя_столбца [, ...] SET имя_столбца_последовательности_поиска ]
        [ CYCLE имя_столбца [, ...] SET имя_столбца_пометки_цикла [ TO значение_пометки_цикла DEFAULT пометка_цикла_по_умолчанию ] USING имя_столбца_пути_цикла ]

TABLE [ ONLY ] имя_таблицы [ * ]

Описание

Команда SELECT выбирает строки из нуля и более таблиц. В целом обработка при помощи SELECT выглядит следующим образом:

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

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

  3. Если указано предложение WHERE, все строки, которые не удовлетворяют этому условию, исключаются из результата. (См. параграф Предложение WHERE ниже.)

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

  5. Вычисляются фактические выходные строки по заданным в SELECT выходным выражениям для каждой выбранной строки или группы строк. (См. параграф Список SELECT ниже.)

  6. Команда SELECT DISTINCT удаляет из результата повторяющиеся строки. Команда SELECT DISTINCT ON удаляет строки, соответствующие всем заданным выражениям. Команда SELECT ALL (по умолчанию) возвращает все строки-кандидаты, включая дубликаты. (См. параграф Предложение DISTINCT ниже.)

  7. С операторами UNION, INTERSECT и EXCEPT выходные данные нескольких команд SELECT объединяются в один результирующий набор. Оператор UNION возвращает все строки, находящиеся в одном или обоих результирующих наборах. Оператор INTERSECT возвращает все строки, которые точно находятся в обоих результирующих наборах. Оператор EXCEPT возвращает строки, которые находятся в первом результирующем наборе, но не во втором. Во всех трех случаях повторяющиеся строки исключаются, если только явно не указано ALL. Можно добавить пропускаемое слово DISTINCT, чтобы явно указать, что требуется устранение повторяющихся строк. Обратите внимание, что здесь DISTINCT — это поведение по умолчанию, хотя в самой команде SELECT по умолчанию подразумевается ALL. (См. параграфы Предложение UNION, Предложение INTERSECT и Предложение EXCEPT ниже.)

  8. Если указано предложение ORDER BY, возвращаемые строки сортируются в указанном порядке. Если ORDER BY не задается, строки возвращаются в любом порядке, который по мнению системы быстрее всего создать. (См. параграф Предложение ORDER BY ниже.)

  9. Если указано предложение LIMIT (или FETCH FIRST) или OFFSET, команда SELECT возвращает только подмножество результирующих строк. (См. параграф Предложение LIMIT ниже.)

  10. Если указано FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE или FOR KEY SHARE, команда SELECT блокирует выбранные строки от параллельных изменений. (См. параграф Предложение блокировки ниже.)

Для всех столбцов, задействованных в команде SELECT, необходимо иметь право SELECT. Использование блокировок FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE требует права UPDATE (по меньшей мере для одного столбца каждой таблицы, выбранной для блокировки таким образом).

Параметры

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

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

Имя (без схемы) должно быть указано для каждого запроса WITH. При необходимости можно указать список имен столбцов; если этот список опущен, имена столбцов выводятся из подзапроса.

Если указано RECURSIVE, это позволяет подзапросу SELECT ссылаться самому на себя по имени. Такой подзапрос должен иметь форму

нерекурсивная_часть UNION [ ALL | DISTINCT ] рекурсивная_часть

где рекурсивная_часть должна находиться в правой части от UNION. Для каждого запроса допускается только одна рекурсивная ссылка. Операторы изменения данных не могут быть рекурсивными, но в них можно использовать результаты рекурсивного запроса SELECT. Пример см. в разделе Запросы WITH.

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

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

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

Необязательное предложение CYCLE используется для обнаружения циклов в рекурсивных запросах. Предоставляемый список имен столбцов задает ключ строки, который будет использоваться для отслеживания посещенных строк. В список результирующих столбцов запроса WITH будет добавлен столбец с именем имя_столбца_пометки_цикла. При обнаружении цикла в этом столбце будет установлено значение_пометки_цикла; иначе — пометка_цикла_по_умолчанию. Более того, при обнаружении цикла будет остановлена обработка рекурсивного объединения. значение_пометки_цикла и пометка_цикла_по_умолчанию должны быть константами и должны быть приводимыми к общему типу данных, а у этого типа должен иметься оператор неравенства. (Стандарт SQL требует, чтобы они были логическими константами или символьными строками, но QHB этого не требует.) По умолчанию используются TRUE и FALSE (типа boolean). Кроме того, в список результирующих столбцов запроса WITH будет добавлен столбец с именем имя_столбца_пути_цикла. Этот столбец применяется внутренне для отслеживания посещенных строк. Примеры см. в подразделе Выявление циклов.

Предложения SEARCH и CYCLE работают только с рекурсивными запросами WITH. При этом запрос_WITH должен быть объединением (UNION или UNION ALL) двух команд SELECT (или равнозначных им) без вложенных UNION. При использовании обоих предложений сразу столбец, добавляемый предложением SEARCH, располагается перед столбцами, добавляемыми предложением CYCLE.

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

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

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

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

Дополнительную информацию см. в разделе Запросы WITH.

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

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

Предложение FROM может содержать следующие элементы:

имя_таблицы

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

псевдоним

Псевдоним, заменяющий имя для элемента FROM. Используется для краткости или для устранения неоднозначности с замкнутыми соединениями (где одна и та же таблица сканируется несколько раз). Когда задается псевдоним, он полностью скрывает фактическое имя таблицы или функции; например, если задано FROM foo AS f, остальная часть запроса SELECT должна обращаться к этому элементу FROM по имени f, а не foo. Если задан псевдоним таблицы, то также можно записать список псевдонимов для одного или нескольких столбцов таблицы, чтобы предоставить им альтернативные имена.

TABLESAMPLE метод_выборки ( аргумент [, ...] ) [ REPEATABLE ( затравка ) ]

Предложение TABLESAMPLE после имени_таблицы показывает, что для получения подмножества строк в этой таблице должен использоваться заданный метод_выборки. Эта выборка предшествует применению любых других фильтров, например предложений WHERE. Стандартный дистрибутив QHB включает два метода выборки, BERNOULLI и SYSTEM, а другие методы выборки можно установить в базе данных с помощью расширений.

Методы выборки BERNOULLI и SYSTEM принимают единственный аргумент, определяющий, какая часть таблицы должна попасть в выборку в диапазоне от 0% до 100%. Этот аргумент может быть любым выражением со значением типа real. (Другие методы выборки могут принимать большее число аргументов или другие аргументы.) Эти два метода возвращают случайную выборку таблицы, которая будет содержать приблизительно указанный процент строк таблицы. Метод BERNOULLI сканирует всю таблицу и выбирает или игнорирует отдельные строки независимо с заданной вероятностью. Метод SYSTEM делает выборку на уровне блоков, для каждого блока определяя шанс его включения; возвращаются все строки в каждом выбранном блоке. Метод SYSTEM значительно быстрее метода BERNOULLI, когда выбирается небольшой процент строк, но в результате эффектов кучности он может возвращать менее случайную выборку таблицы.

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

выборка

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

имя_запроса_WITH

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

имя_функции

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

Когда к вызову функции добавляется необязательное предложение WITH ORDINALITY, к результирующим столбцам функции добавляется новый столбец типа bigint. В нем нумеруются строки набора результатов функции, начиная с 1. По умолчанию этот столбец называется ordinality.

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

Несколько вызовов функций можно объединить в одном элементе предложения FROM, если заключить их в конструкцию ROWS FROM( ... ). Результатом такого элемента будет конкатенация первых строк из каждой функции, затем вторых строк из каждой функции и т. д. Если какие-то функции выдают меньше строк, чем другие, то отсутствующие данные заменяются значениями NULL, так что общее количество возвращаемых строк всегда такое же, как у функции, которая выдала наибольшее количество строк.

Если функция была определена как возвращающая тип данных record, для нее нужно указать псевдоним или ключевое слово AS, а затем список определений столбцов в форме ( имя_столбца тип_данных [, ... ]). Список определений столбцов должен соответствовать фактическому количеству и типам столбцов, возвращаемых функцией.

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

Для того чтобы использовать ORDINALITY вместе со списком определений столбцов, необходимо использовать синтаксис ROWS FROM( ... ) и поместить туда этот список.

тип_соединения

Может быть одним из:

  • [INNER ] JOIN
  • LEFT [OUTER ] JOIN
  • RIGHT [OUTER ] JOIN
  • FULL [OUTER ] JOIN

Для типов соединений INNER и OUTER должно быть указано условие соединения, а именно одно из предложений ON условие_соединения, USING (столбец_соединения[, ... ]) или NATURAL. См. их описания ниже.

Предложение JOIN объединяет в себе два элемента FROM, которые для удобства будем называть «таблицами», хотя на самом деле они могут быть любого типа, допустимого в качестве элемента FROM. При необходимости для определения порядка вложенности используйте круглые скобки. При отсутствии круглых скобок предложения JOIN вкладываются слева направо. В любом случае, JOIN связывает элементы сильнее, чем разделяющие запятые в списке FROM. Все варианты JOIN введены только для удобства записи, поскольку они не делают ничего, что нельзя было бы сделать с простыми FROM и WHERE.

LEFT OUTER JOIN возвращает все строки ограниченного декартова произведения (т. е. все объединенные строки, попадающие под условие соединения) плюс по одной копии для каждой строки в таблице слева, для которой не находится строки в таблице справа, удовлетворяющей условию соединения. Левая строка дополняется до полной ширины объединенной таблицы значениями NULL, вставляемыми в столбцы справа. Обратите внимание, что при принятии решения о том, какие строки двух таблиц соответствуют друг другу, учитывается только условие самого предложения JOIN. И только затем применяются внешние условия.

Напротив, RIGHT OUTER JOIN возвращает все соединенные строки плюс по одной строке для каждой строки справа, не имеющей соответствия слева (дополненной значениями NULL слева). Это предложение введено только для удобства записи, так как его можно преобразовать в LEFT OUTER JOIN, поменяв местами левую и правую таблицы.

FULL OUTER JOIN возвращает все соединенные строки плюс по одной строке для каждой несоответствующей строки слева (дополненной значениями NULL справа), плюс по одной строке для каждой несоответствующей строки справа (дополненной значениями NULL слева).

ON условие_соединения

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

USING ( столбец_соединения [, ...] ) [ AS псевдоним_использования_соединения ]

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

Если задается имя псевдоним_использования_соединения, оно предоставляет псевдоним таблицы для столбцов соединения. По этому имени можно обращаться только к столбцам соединения, перечисленным в предложении USING. В отличие от обычного псевдонима, этот не скрывает имена соединенных таблиц от остального запроса. Также в отличие от обычного псевдонима, с ним нельзя написать список псевдонимов столбцов — выходные имена столбцов соединения совпадают с теми, которые находятся в списке USING.

NATURAL

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

CROSS JOIN

Указание CROSS JOIN равнозначно INNER JOIN ON (TRUE), то есть никакие строки по условию не удаляются. Оба варианта формируют простое декартово произведение — тот же результат, какой можно получить, перечислив эти две таблицы на верхнем уровне FROM, но только ограниченный условием соединения (если таковое имеется).

LATERAL

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

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

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

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

Исходная таблица (или таблицы) столбца должна быть соединена с элементом LATERAL с помощью INNER или LEFT, иначе не образуется однозначно определенного набора строк, из которого можно было бы вычислить все наборы строк для элемента LATERAL. Таким образом, хотя такая конструкция, как X RIGHT JOIN LATERAL Y, синтаксически допустима, фактически Y не может обращаться к X.

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

Необязательное предложение WHERE имеет общую форму

WHERE условие

где условие — это любое выражение, которое при вычислении выдает результат типа boolean. Любая строка, которая не удовлетворяет этому условию, будет исключена из вывода. Строка удовлетворяет условию, если возвращает true, когда вместо ссылок на переменные подставляются фактические значения строк.

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

Необязательное предложение GROUP BY имеет общую форму

GROUP BY [ ALL | DISTINCT ] элемент_группирования [, ...]

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

Если в элементах группирования задается GROUPING SETS, ROLLUP или CUBE, то предложение GROUP BY в целом определяет некоторое количество независимых наборов_группирования. Эффект от этого равнозначен объединению подзапросов (с UNION ALL) с отдельными наборами группирования в качестве их предложений GROUP BY. Необязательное предложение DISTINCT удаляет перед обработкой повторяющиеся наборы; оно не превращает UNION ALL в UNION DISTINCT. Дополнительную информацию об обращении с наборами группирования см. в подразделе GROUPING SETS, CUBE и ROLLUP.

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

Когда имеется предложение GROUP BY или какая-либо агрегатная функция, выражения в списке SELECT не могут обращаться к негруппируемым столбцам, кроме как в агрегатных функциях или если негруппируемый столбец функционально зависит от группируемых столбцов, поскольку в противном случае в негруппируемый столбец вернулось бы более одного возможного значения. Функциональная зависимость появляется, если группируемые столбцы (или их подмножество) являются первичным ключом таблицы, содержащей негруппируемый столбец.

Имейте в виду, что все агрегатные функции вычисляются перед вычислением любых «скалярных» выражений в предложении HAVING или списке SELECT. Это означает, что, например, нельзя использовать выражение CASE, чтобы пропустить вычисление агрегатной функции; см. подраздел Правила вычисления выражений.

В настоящий момент вместе с GROUP BY нельзя указать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

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

Необязательное предложение HAVING имеет общую форму

HAVING условие

где условие задается так же, как и в предложении WHERE.

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

Наличие предложения HAVING превращает запрос в группируемый, даже если отсутствует предложение GROUP BY. То же самое происходит, когда запрос содержит агрегатные функции, но нет предложения GROUP BY. Все выбранные строки считаются образующими одну группу, а в списке SELECT и предложении HAVING можно обращаться к столбцам таблицы только из агрегатных функций. Такой запрос будет выдавать одну строку, если результат условия HAVING является true, и ноль строк в противном случае.

В настоящий момент вместе с HAVING нельзя указать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

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

Необязательное предложение WINDOW имеет общую форму

WINDOW имя_окна AS ( определение_окна ) [, ...]

где имя_окна — это имя, на которое можно ссылаться из предложений OVER или последующих определений окна, а определение_окна — это:

[ имя_существующего_окна ]
[ PARTITION BY выражение [, ...] ]
[ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ предложение_рамки ]

Если имя_существующего_окна указано, оно должно ссылаться на предшествующую запись в списке WINDOW; новое окно копирует предложение разбиения из этой записи, а также предложение сортировки, если таковое имеется. В этом случае для нового окна нельзя указать свое собственное предложение PARTITION BY, а предложение ORDER BY можно определить, только если его не было у копируемого окна. Новое окно всегда использует свое собственное предложение рамки; в копируемом окне предложение рамки не должно быть указано.

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

Аналогично элементы списка ORDER BY интерпретируется во многом так же, как элементы предложения ORDER BY, за исключением того, что эти выражения всегда воспринимаются как простые выражения и никогда как имя или номер выходного столбца.

Необязательное предложение_рамки определяет рамку окна для оконных функций, которые зависят от рамки (не все они зависят). Рамка окна представляет собой набор связанных строк для каждой строки запроса (называемой текущей строкой). предложением_рамки может быть:

{ RANGE | ROWS | GROUPS } начало_рамки [ исключение_рамки ]
{ RANGE | ROWS | GROUPS } BETWEEN начало_рамки AND конец_рамки [ исключение_рамки ]

где началом_рамки и концом_рамки могут быть:

UNBOUNDED PRECEDING
смещение PRECEDING
CURRENT ROW
смещение FOLLOWING
UNBOUNDED FOLLOWING

и исключением_рамки может быть:

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

Если конец_рамки опущен, то по умолчанию подразумевается CURRENT ROW. Ограничения заключаются в том, что в качестве начала_рамки нельзя указать как UNBOUNDED FOLLOWING, конец_рамки — как UNBOUNDED PRECEDING, и конец_рамки не может находиться перед началом_рамки в приведенном выше списке параметров начала_рамки и конца_рамки — например, синтаксис RANGE BETWEEN CURRENT ROW AND смещение PRECEDING не допускается.

По умолчанию рамка окна образуется предложением RANGE UNBOUNDED PRECEDING, которое равнозначно RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; оно устанавливает рамку так, чтобы та включала все строки от начала партиции до последней строки, родственной текущей (строка, которая, согласно указанному для окна предложению ORDER BY, считается равнозначной текущей строке; если предложение ORDER BY отсутствует, все строки являются родственными). В целом, UNBOUNDED PRECEDING означает, что рамка начинается с первой строки партиции, и подобным же образом UNBOUNDED FOLLOWING означает, что рамка заканчивается последней строкой партиции независимо от того, какой установлен режим: RANGE, ROWS или GROUPS. В режиме ROWS указание CURRENT ROW означает, что рамка начинается или заканчивается текущей строкой; но в режиме RANGE или GROUPS это означает, что рамка начинается или заканчивается первой или последней строкой, родственной текущей, согласно порядку ORDER BY. Параметры смещение PRECEDING и смещение FOLLOWING различаются по смыслу в зависимости от режима рамки. В режиме ROWS смещение является целым числом, показывающим количество строк, на которое начало или конец рамки смещается вверх или вниз от текущей строки соответственно. В режиме GROUPS смещение является целым числом, показывающим, количество родственных групп, на которое начало или конец рамки смещается вверх или вниз от текущей строки соответственно, где родственная группа — это группа строк, равнозначных согласно предложению ORDER BY данного окна. В режиме RANGE использование параметра смещение требует, чтобы в определении окна присутствовал ровно один столбец ORDER BY. Тогда рамка содержит те строки, где значение упорядочивающего столбца не более чем на смещение меньше (для PRECEDING) или больше (для FOLLOWING) значения упорядочивающего столбца текущей строки. В этих случаях тип данных выражения смещение зависит от типа данных упорядочивающего столбца. Для числовых столбцов это обычно тот же тип, а для столбцов с типом дата/время — тип interval. Во всех этих случаях значение смещения должно быть отличным от NULL и неотрицательным. Кроме того, несмотря на то, что смещение необязательно должно быть простой константой, оно не может содержать переменные, агрегатные или оконные функции.

Параметр исключение_рамки позволяет исключать из рамки строки, окружающие текущую строку, даже если они могли бы быть включены в соответствии с параметрами начала и конца рамки. Предложение EXCLUDE CURRENT ROW исключает из рамки текущую строку. EXCLUDE GROUP исключает из рамки текущую строку и родственные ей согласно порядку сортировки. EXCLUDE TIES исключает из рамки только строки, родственные текущей строке, но не саму текущую строку. EXCLUDE NO OTHERS просто явно указывает поведение по умолчанию: не исключать ни текущую строку, ни родственные ей.

Учтите, что в режиме ROWS могут выдаваться непредсказуемые результаты, если согласно порядку, указанному в ORDER BY, строки сортируются неоднозначно. Режимы RANGE и GROUPS предназначены для обеспечения того, чтобы строки, являющиеся родственными в порядке ORDER BY, обрабатывались одинаково: все строки данной родственной группы попадут в одну рамку или будут исключены из нее.

Назначение предложения WINDOW — управление поведением оконных функций, фигурирующих в параметрах списка SELECT или предложения ORDER BY запроса. Эти функции могут ссылаться на записи предложения WINDOW по имени в своих предложениях OVER. Однако на запись предложения WINDOW не обязательно нигде ссылаться: если она не используется в запросе, то просто игнорируется. Можно использовать оконные функции вообще без предложения WINDOW, так как в вызове оконной функции можно указать ее определение окна непосредственно в предложении OVER. Тем не менее предложение WINDOW позволяет сократить текст запроса, когда одно и то же определение окна требуется для более чем одной оконной функции.

В настоящий момент вместе с WINDOW нельзя указать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

Оконные функции подробно описаны в разделе Что такое оконные функции и подразделах Вызовы оконных функций и Обработка оконных функций.

Список SELECT

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

Так же, как и в таблице, каждый выходной столбец SELECT имеет имя. В простом SELECT это имя используется только для обозначения столбца для отображения, но когда SELECT является подзапросом большего запроса, имя рассматривается большим запросом как имя столбца виртуальной таблицы, созданной подзапросом. Чтобы указать имя, используемое для выходного столбца, нужно после выражения столбца написать AS имя_вывода. (Можно опустить AS, но только если желаемое имя вывода не совпадает с ключевыми словами QHB (см. раздел Ключевые слова SQL). Для защиты от возможных будущих добавлений ключевых слов рекомендуется всегда либо писать AS, либо заключать имя вывода в кавычки.) Если не задать имя столбца, QHB выберет его автоматически. Если выражение столбца является простой ссылкой на столбец, то выбранное имя совпадет с именем этого столбца. В более сложных случаях может использоваться имя функции или типа, либо система может сгенерировать имя, например ?column?.

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

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

Согласно стандарту SQL, выражения в выходном списке должны быть вычислены перед применением DISTINCT, ORDER BY или LIMIT. Безусловно, это необходимо при использовании DISTINCT, так как иначе неясно, какие значения должны выдаваться как уникальные. Однако во многих случаях удобнее, если выходные выражения вычисляются после ORDER BY и LIMIT; особенно если выходной список содержит какие-либо изменчивые или затратные функции. В этом случае порядок вычисления функций является более интуитивным, а для строк, которые не попадут в результат, вычисления производиться не будут. QHB фактически будет вычислять выходные выражения после сортировки и ограничения количества строк, если эти выражения не фигурируют в DISTINCT, ORDER BY или GROUP BY. (В качестве примера, в запросе SELECT f(x) FROM tab ORDER BY 1 функция f(x), несомненно, должна вычисляться перед сортировкой.) Выходные выражения, содержащие функции, возвращающие множества, фактически вычисляются после сортировки и перед ограничением количества строк, так что LIMIT будет отбрасывать строки, выдаваемые функцией, возвращающей множество.

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

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

SELECT DISTINCT ON ( выражение [, ...] ) сохраняет только первую строку из каждого набора строк, в котором заданные выражения вычисляются в одинаковые значения. Выражения DISTINCT ON интерпретируются по тех же правилам, что и ORDER BY (см. выше). Обратите внимание, что «первая строка» каждого набора непредсказуема, если для обеспечения того, чтобы желаемая строка появилась первой, не указано ORDER BY. Например:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

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

Выражение (или выражения) DISTINCT ON должно соответствовать самому левому выражению (или выражения) ORDER BY. Предложение ORDER BY обычно содержит дополнительное выражение (или выражения), определяющее желаемый приоритет строк в каждой группе DISTINCT ON.

В настоящий момент вместе с DISTINCT нельзя указать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

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

Предложение UNION имеет общую форму:

оператор_SELECT UNION [ ALL | DISTINCT ] оператор_SELECT

Где оператор_SELECT — это любой оператор SELECT без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE. (ORDER BY и LIMIT можно присоединить к вложенному выражению, если оно заключено в круглые скобки. Без скобок эти предложения будут восприняты для применения к результату UNION, а не к входному выражению в его правой части.)

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

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

Несколько операторов UNION в одном операторе SELECT вычисляются слева направо, если круглыми скобками не определен иной порядок.

В настоящий момент ни для результата с UNION, ни для любого из подзапросов UNION невозможно указать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

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

Предложение INTERSECT имеет общую форму:

оператор_SELECT INTERSECT [ ALL | DISTINCT ] оператор_SELECT

Где оператор_SELECT — это любой оператор SELECT без предложения ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE.

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

Результат INTERSECT не будет содержать повторяющихся строк, если не указан параметр ALL. С ALL строка, повторяющаяся m раз в левой таблице и n раз в правой, будет выдана в результирующем наборе min(m,n) раз. Можно написать DISTINCT, чтобы явно указать, что повторяющиеся строки должны быть удалены (это поведение по умолчанию).

Несколько операторов INTERSECT в одном операторе SELECT вычисляются слева направо, если круглыми скобками не определен иной порядок. INTERSECT связывает свои подзапросы сильнее, чем UNION. Иными словами, A UNION B INTERSECT C будет восприниматься как A UNION (B INTERSECT C).

В настоящий момент ни для результата с INTERSECT, ни для любого из подзапросов INTERSECT нельзя указать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

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

Предложение EXCEPT имеет общую форму:

оператор_SELECT EXCEPT [ ALL | DISTINCT ] оператор_SELECT

Где оператор_SELECT — это любой оператор SELECT без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE.

Оператор EXCEPT вычисляет набор строк, которые находятся в результате выполнения левого оператора SELECT, но не в результате выполнения правого.

Результат EXCEPT не будет содержать каких-либо повторяющихся строк, если не указан параметр ALL. С ALL строка, повторяющаяся m раз в левой таблице и n раз в правой, будет выдана в результирующем наборе max(m-n,0) раз. Можно написать DISTINCT, чтобы явно указать, что повторяющиеся строки должны быть удалены (это поведение по умолчанию).

Несколько операторов EXCEPT в одном операторе SELECT вычисляются слева направо, если круглыми скобками не определен иной порядок. EXCEPT связывает свои подзапросы так же сильно, как UNION.

В настоящий момент ни для результата с EXCEPT, ни для любого из подзапросов EXCEPT нельзя указать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE.

Предложение ORDER BY

Необязательное предложение ORDER BY имеет общую форму:

ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS { FIRST | LAST } ] [, ...]

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

Каждое выражения может быть именем или порядковым номером выходного столбца (из элемента списка SELECT) либо произвольным выражением, сформированным из значений входного столбца.

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

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

SELECT name FROM distributors ORDER BY code;

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

Если выражение ORDER BY — простое имя, соответствующее имени как выходного, так и входного столбца,ORDER BY будет интерпретировать его как имя выходного столбца. Это противоположно выбору, который в такой же ситуации сделает GROUP BY. Данная несогласованность допущена для совместимости со стандартом SQL.

Дополнительно после любого выражения в предложении ORDER BY можно добавить ключевое слово ASC (по возрастанию) или DESC (по убыванию). Если ничего не указано, по умолчанию предполагается ASC. Как вариант, в предложении USING можно указать имя конкретного оператора сортировки. Оператор сортировки должен быть членом «меньше» или «больше» некоторого семейства операторов B-дерева. ASC обычно равнозначно USING <, а DESC — USING >. (Но создатель пользовательского типа данных может точно определить порядок сортировки по умолчанию, который может соответствовать операторам с другими именами.)

Если указывается NULLS LAST, значения NULL при сортировке окажутся после всех значений не NULL; если указывается NULLS FIRST, значения NULL окажутся перед всеми значениями не NULL. Если ни то, ни другое не указано, то при явном или косвенном выборе порядка ASC по умолчанию подразумевается NULLS LAST, и наоборот, при явном или косвенном выборе порядка DESC по умолчанию подразумевается NULLS FIRST (следовательно, по умолчанию считается, что значения NULL больше значений не NULL). С предложением USING порядок NULL по умолчанию зависит от того, является ли заданный оператор оператором «меньше» или «больше».

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

Данные символьных строк сортируются в соответствии с правилом сортировки, установленным для сортируемого столбца. При необходимости это поведение можно переопределить путем включения в выражение предложения COLLATE, например, так: ORDER BY mycolumn COLLATE "en_US". Дополнительную информацию см. в подразделе Применение правил сортировки и разделе Поддержка правил сортировки.

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

Предложение LIMIT состоит из двух независимых вложенных предложений:

LIMIT { число | ALL }
OFFSET начало

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

Если выражение число вычисляется в значение NULL, это предложение обрабатывается как LIMIT ALL, то есть без ограничения числа строк. Если начало вычисляется в значение NULL, оно обрабатывается как OFFSET 0.

SQL:2008 представил другой синтаксис для получения того же результата, который также поддерживает QHB. Это:

OFFSET начало { ROW | ROWS }
FETCH { FIRST | NEXT } [ число ] { ROW | ROWS } { ONLY | WITH TIES }

В этом синтаксисе стандарт требует, чтобы значение начало или число было литеральной константой, параметром или именем переменной; в качестве расширения QHB разрешает использовать другие выражения, но, как правило, они должны быть заключены в круглые скобки, чтобы избежать неоднозначности. Если в предложении FETCH число опускается, то по умолчанию принимается значение 1. Параметр WITH TIES используется для возвращения дополнительных строк, согласно предложению ORDER BY совпадающих с последней позицией набора результатов; в этом случае предложение ORDER BY является обязательным, а SKIP LOCKED — недопустимым. Слова ROW и ROWS, а также FIRST и NEXT являются пропускаемыми и не влияют на действие этих предложений. Согласно стандарту, предложение OFFSET должно предшествовать предложению FETCH, если присутствуют оба; но QHB менее строга и позволяет любой порядок.

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

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

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

Предложение блокировки

Предложениями блокировки являются операторы FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE и FOR KEY SHARE; они влияют на то, как SELECT блокирует строки по мере их получения из таблицы.

Предложение блокировки имеет общую форму

FOR сила_блокировки [ OF имя_таблицы [, ...] ] [ NOWAIT | SKIP LOCKED ]

где силой_блокировки может быть:

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

Дополнительную информацию о каждом режиме блокировки на уровне строк см. в подразделе Блокировки на уровне строк.

Чтобы операция не ожидала фиксации других транзакций, используйте указание NOWAIT или SKIP LOCKED. С NOWAIT оператор сообщает об ошибке, а не ждет, если выбранную строку нельзя заблокировать немедленно. С указанием SKIP LOCKED все выбранные строки, которые нельзя немедленно заблокировать, пропускаются. Пропуск заблокированных строк формирует несогласованное представление данных, поэтому этот вариант не подходит для общего применения, но может быть использован для предотвращения конфликта блокировок при обращении множества потребителей к таблице типа очереди. Обратите внимание, что указания NOWAIT и SKIP LOCKED можно применить только к блокировке(ам) уровня строк — необходимая блокировка ROW SHARE уровня таблицы по-прежнему запрашивается обычным способом (см. главу Параллельный контроль). Если нужно запросить блокировку уровня таблицы без ожидания, можно использовать команду LOCK с указанием NOWAIT.

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

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

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

Когда предложение блокировки появляется на верхнем уровне запроса SELECT, блокируются именно те строки, которые возвращаются запросом; в случае запроса соединения блокируются те строки, которые участвуют в возвращаемых строках соединения. Кроме того, строки, удовлетворяющие условиям запроса на момент создания снимка запроса, будут заблокированы, хотя и не будут возвращены, если были изменены с момента снимка и больше не удовлетворяют условиям запроса. Если используется LIMIT, блокировка останавливается, как только возвращается достаточно строк, чтобы удовлетворить заданному пределу (но обратите внимание, что строки, пропускаемые указанием OFFSET, будут блокироваться). Аналогично если предложение блокировки используется в запросе курсора, то заблокируются только те строки, которые были фактически извлечены или пройдены курсором.

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

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

заблокирует только строки c col1 = 5, хотя в такой записи условие не относится к подзапросу.

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

BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;

не удалось бы сохранить блокировку FOR UPDATE после ROLLBACK TO.

ВНИМАНИЕ!
Возможно, команда SELECT, запущенная с уровнем изоляции транзакции READ COMMITTED и с указанием ORDER BY и предложением блокировки будет возвращать строки не по порядку. Это связано с тем, что ORDER BY применяется первым. Команда сортирует результат, но затем может быть заблокирована при попытке получить блокировку одной или нескольких строк. Как только блокировка с SELECT снимается, некоторые значения сортируемых столбцов могут быть изменены, приводя к нарушению порядка этих строк (хотя они упорядочены с точки зрения исходных значений столбцов). При необходимости эту проблему можно обойти, поместив в подзапрос предложение FOR UPDATE/SHARE, например:

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) SS ORDER BY column1;

Обратите внимание, что этот запрос приведет к блокировке всех строк таблицы mytable, тогда как с FOR UPDATE на верхнем уровне блокировались бы только фактически возвращаемые строки. Это может значительно повлиять на производительность, особенно если ORDER BY объединено с LIMIT или другими ограничениями. Таким образом, этот метод рекомендуется только в том случае, если ожидаются параллельные изменения сортируемых столбцов и требуется тщательно отсортированный результат.

На уровне изоляции транзакции REPEATABLE READ или SERIALIZABLE это привело бы к ошибке сериализации (с SQLSTATE '40001'), поэтому на этих уровнях изоляции нет никакой возможности получения строк не по порядку.

Команда TABLE

Команда

TABLE имя

равнозначна

SELECT * FROM имя

Ее можно использовать как команду верхнего уровня или как вариант более краткого синтаксиса внутри сложных запросов. С командой TABLE можно использовать только предложения WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH и предложение блокировки FOR; предложение WHERE и любые формы агрегирования использовать нельзя.

Примеры

Соединение таблицы films с таблицей distributors:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d, films f
    WHERE f.did = d.did

       title       | did |     name     | date_prod  |   kind
-------------------+-----+--------------+------------+----------
 The Third Man     | 101 | British Lion | 1949-12-23 | Drama
 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
 ...

Суммирование значений столбца len (продолжительность) для всех фильмов и группирование результатов по столбцу kind (тип фильма):

SELECT kind, sum(len) AS total FROM films GROUP BY kind;

   kind   | total
----------+-------
 Action   | 07:34
 Comedy   | 02:58
 Drama    | 14:28
 Musical  | 06:42
 Romantic | 04:38

Суммирование значений столбца len для всех фильмов, группирование результатов по столбцу kind и выведение только тех групп, общая продолжительность которых меньше 5 часов:

SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours';

   kind   | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38

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

SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;


 did |       name
-----+------------------
 109 | 20th Century Fox
 110 | Bavaria Atelier
 101 | British Lion
 107 | Columbia
 102 | Jean Luc Godard
 113 | Luso films
 104 | Mosfilm
 103 | Paramount
 106 | Toho
 105 | United Artists
 111 | Walt Disney
 112 | Warner Bros.
 108 | Westward

Следующий пример показывает объединение таблиц distributors и actors, с ограничением результатов значениями, начинающимися с буквы W в каждой таблице. Интерес представляют только неповторяющиеся строки, поэтому ключевое слово ALL опущено.

distributors:               actors:
 did |     name              id |     name
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

      name
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen

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

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney

Пример функции с добавленным столбцом нумерации:

SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
 unnest | ordinality
--------+----------
 a      |        1
 b      |        2
 c      |        3
 d      |        4
 e      |        5
 f      |        6
(6 rows)

Этот пример показывает, как использовать простое предложение WITH:

WITH t AS (
    SELECT random() as x FROM generate_series(1, 3)
  )
SELECT * FROM t
UNION ALL
SELECT * FROM t

         x          
--------------------
  0.534150459803641
  0.520092216785997
 0.0735620250925422
  0.534150459803641
  0.520092216785997
 0.0735620250925422

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

В этом примере WITH RECURSIVE применяется для поиска всех подчиненных (непосредственных или косвенных) работодателя Мэри и их уровня косвенности в таблице, показывающей только непосредственных подчиненных:

WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
  UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive er, employee e
    WHERE er.employee_name = e.manager_name
  )
SELECT distance, employee_name FROM employee_recursive;

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

В этом примере используется LATERAL для применения возвращающей множество функции get_product_names() для каждой строки таблицы manufacturers (производители):

SELECT m.name AS mname, pname
FROM manufacturers m, LATERAL get_product_names(m.id) pname;

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

SELECT m.name AS mname, pname
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;

Совместимость

Разумеется, команда SELECT совместима со стандартом SQL. Но есть некоторые расширения и некоторые отсутствующие функциональные возможности.

Пропуск предложений FROM

QHB позволяет опустить предложение FROM. Это упрощает вычисление результатов простых выражений:

SELECT 2+2;

 ?column?
----------
        4

Некоторые другие базы данных SQL могут сделать это только путем введения фиктивной таблицы с одной строкой, из которой нужно сделать SELECT.

Пустые списки SELECT

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

Пропуск ключевого слова AS

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

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

ONLY и наследование

Стандарт SQL требует заключать имя таблицы в круглые скобки при написании ONLY, например SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... QHB считает эти скобки необязательными.

QHB позволяет писать в конце *, чтобы явно указать что дочерние таблицы включаются в рассмотрение, в отличие от поведения с ONLY. Стандарт SQL этого не допускает.

(Эти соображения применяются ко всем командам SQL, поддерживающим параметр ONLY.)

Ограничения предложения TABLESAMPLE

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

Вызовы функций в предложении FROM

QHB позволяет записывать вызов функции непосредственно в качестве члена списка FROM. В стандарте SQL было бы необходимо обернуть такой вызов функции в подзапрос SELECT; то есть синтаксис FROM функция(...) псевдоним примерно равнозначен FROM LATERAL (SELECT функция(...)) псевдоним. Обратите внимание, что указание LATERAL считается неявным; это связано с тем, что стандарт требует поведения LATERAL для элемента UNNEST() в предложении FROM. QHB обрабатывает UNNEST() так же, как и другие функции, возвращающие множества.

Доступность пространств имен в GROUP BY и ORDER BY

В стандарте SQL-92 предложение ORDER BY может использовать только имена или номера выходных столбцов, в то время как предложение GROUP BY может использовать только выражения, основанные на именах входных столбцов. QHB расширяет оба этих предложения, позволяя также применять и другие варианты (но если есть неоднозначность, она использует интерпретацию стандарта). Кроме того, QHB позволяет указывать в обоих предложениях произвольные выражения. Обратите внимание, что имена, фигурирующие в выражениях, всегда будут восприниматься как имена входных столбцов, а не выходных.

В SQL:1999 и более поздних стандартах введено немного другое определение, которое не полностью совместимо с SQL-92. В большинстве случаев, однако, QHB будет интерпретировать выражение ORDER BY или GROUP BY так, как требует SQL:1999.

Функциональные зависимости

QHB распознает функциональную зависимость (позволяя пропускать столбцы из GROUP BY) только в том случае, если первичный ключ таблицы включен в список GROUP BY. Стандарт SQL задает дополнительные условия, которые должны быть учтены.

LIMIT и OFFSET

Предложения LIMIT и OFFSET являются специфичным синтаксисом QHB, также используемым в MySQL. Стандарт SQL:2008 для той же цели вводит предложения OFFSET ... FETCH {FIRST|NEXT} ..., рассмотренные ранее в параграфе Предложение LIMIT. Этот синтаксис также используется IBM DB2. (Приложения, написанные для Oracle, часто используют обходной путь, включая автоматически генерируемый столбец rownum (отсутствующий в QHB), чтобы реализовать эффект этих предложений.)

FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE

Хотя в стандарте SQL есть предложение блокировки FOR UPDATE, стандарт позволяет использовать его только как параметр в предложении DECLARE CURSOR. QHB позволяет использовать его в любом запросе SELECT, а также в подзапросах SELECT, но это является расширением. Варианты FOR NO KEY UPDATE, FOR SHARE и FOR KEY SHARE, а также параметры NOWAIT и SKIP LOCKED не отображены в стандарте.

Изменение данных в WITH

QHB позволяет использовать в в качестве запросов WITH команды INSERT, UPDATE и DELETE. Этого нет в стандарте SQL.

Нестандартные предложения

DISTINCT ON ( ... ) является расширением стандарта SQL.

ROWS FROM( ... ) является расширением стандарта SQL.

Параметры MATERIALIZED и NOT MATERIALIZED предложения WITH также являются расширениями стандарта SQL.

Copyright © KVANTOM LLC, 2019-2023