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 [ NATURAL ] тип_соединения элемент_FROM [ ON условие_соединения | USING ( столбец_соединения [, ...] ) ]

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

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

Где запрос_WITH может быть:

    имя_запроса_WITH [ ( имя_столбца [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )

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, кроме тех, которые не используют рекурсию или ссылки на последующие запросы.

Основной запрос и запросы 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. Когда к вызову функции добавляется необязательное предложение WITH ORDINALITY, после всех выдаваемых функцией столбцов функции добавляется новый столбец с номерами строк.

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

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

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

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

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

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

Один из

  • [INNER ] JOIN

  • LEFT [OUTER ] JOIN

  • RIGHT [OUTER ] JOIN

  • FULL [OUTER ] JOIN

  • CROSS JOIN

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

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

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

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

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

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

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

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

USING ( столбец_соединения [, ...] )

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

NATURAL

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

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 элемент_группирования [, ...]

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

Если в элементе группирования задается GROUPING SETS, ROLLUP или CUBE, то предложение GROUP BY в целом определяет некоторое количество независимых наборов группирования. Эффект от этого равнозначен объединению подзапросов (с UNION ALL) с отдельными наборами группирования в качестве их предложений GROUP BY. Дополнительную информацию об обработке наборов группирования см. в разделе 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.

Дополнительно можно добавить ключевое слово ASC (по возрастанию) или DESC (по убыванию) после любого выражения в предложении ORDER BY. Если ничего не указано, по умолчанию предполагается 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

В этом синтаксисе стандарт требует, чтобы значение начало или число было буквальной константой, параметром или именем переменной; QHB разрешает использовать другие выражения, но они обычно должны быть заключены в круглые скобки, чтобы избежать неоднозначности. Если в предложении FETCH число опускается, то по умолчанию принимается значение 1. Слова 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 может использоваться как команда верхнего уровня или как вариант более краткой записи внутри сложных запросов. С командой 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.

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

SELECT distributors.* WHERE distributors.name = 'Westward';

Пустые списки 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 позволяет использовать INSERT, UPDATE и DELETE в в качестве запросов WITH. Этого нет в стандарте SQL.

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

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

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

Указания MATERIALIZED и NOT MATERIALIZED предложения WITH также относятся к расширениям стандарта SQL.