SELECT - Извлекает строки из одной или нескольких таблиц

Извлекает строки из одной или нескольких таблиц

🕛 22.05.2009, 11:58

Список обозначений: Символ "::=" означает равенство по определению Необязательные элементы в квадратных скобках "[ ]" Фигурные скобки "{ }" означают, что все находящееся внутри является единым целым Круглые скобки "( )" являются литералами и используются при формировании оператора Вертикальная черта " | " указывает на то, что все предшествующие ей элементы необязательны и любой из них может быть заменен на другой, принадлежащий списку после этой черты Многоточие "..." - предшествующая часть оператора может быть повторена любое количество раз “,…” - предшествующая часть оператора, состоящая из нескольких элементов, может быть повторена любое количество раз

Синтаксис

SELECT [DISTINCT]
{ { функция агрегирования | выражение [AS имя столбца] },... }
| { спецификатор.* }
| *
FROM { { имя таблицы [AS] [имя корреляции] [(имя столбца,...)] }
| { подзапрос [AS] имя корреляции [имя столбца,…] }
| соединенная таблица },...
[ WHERE предикат ]
[ GROUP BY { { [имя таблицы | имя корреляции }.] имя столбца },... ]
[ HAVING предикат]
[ { UNION | INTERSECT | EXCEPT } [ALL]
[ CORRESPONDING [ BY ( имя столбца,...) ] ]
оператор select | {TABLE имя таблицы } | конструктор значений таблицы]
[ ORDER BY { { столбец-результат [ASC | DESC] },...}
| { { положительное целое [ASC | DESC] },...}

Использование

Данный оператор используется для формирования запроса и получения информации из базы данных. Исполнитель запроса должен обладать привилегией SELECT для всех таблиц, к которым осуществляется доступ. Запросы могут быть отдельными операторами или быть частью определения представления или курсора. Любой из запросов можно применять в качестве ползапроса, чтобы получить значения, которые впоследствии будут использованы другими операторами, включая сам оператор SELECT. Иногда подзапрос выполняется отдельно для каждой строки, обрабатываемой внешним запросом, при этом значения из этой внешней строки будут использоваться в подзапросе. Запросы этого этапа называются зависимыми (correlated, коррелированными) подзапросами.

Результат запроса представляет собой таблицу, столбцы которой определяются предложением SELECT как столбцы-результаты. Предложение FROM определяет одну или несколько таблиц, из которых извлекаются данные. Источником данных могут служить временные или постоянные базовые таблицы, представления или выходные данные подзапросов, а также другие операции с данными, результатом которых является таблица. В предложении WHERE определяются критерии, которым должны удовлетворять строки, чтобы их можно было использовать для получения результата. Предложение GROUP BY группирует выходные данные по одинаковым значениям указанных столбцов. При использовании GROUP BY в предложении SELECT могут быть указаны только те столбцы, которые поименованы в фразе GROUP BY, и любые агрегатные функции. Предложение GROUP BY применяется для выполнения агрегатных функций над группами строк, которые имеют одинаковые значения в указанных столбцах. Если GROUP BY отсутствует, то агрегатные функции либо не используются вообще, либо используются для всех выходных столбцов. Если все столбцы используют агрегатные функции, то все строки, удовлетворяющие предложению WHERE, или все строки, извлекаемые предложением FROM {если WHERE не задано), рассматриваются как одна группа для получения агрегатных (групповых) значений. Предложение HAVING определяет критерии, которым должны удовлетворять группы строк, формируемые предложением GROUP BY, чтобы их можно было поместить в выходные данные с помощью запроса. Операции UNION INTERSECT и EXCEPT применяются для объединения выходных данных нескольких запросов. Далее они будут описаны подробнее. С помощью предложения ORDER BY можно расположить результаты одного или нескольких запросов в определенном порядке.

Список, приведенный ниже, определяет последовательность обработки предложений в операторе SELECT:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. {UNION или EXCEPT}
7. INTERSECT
8. ORDER BY
Каждое предложение описано далее в отдельном подразделе в соответствии с порядком, определенным в синтаксической структуре оператора.

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

Предложение SELECT стоит первым в синтаксической структуре, однако не является первым логическим исполняемым шагом. Результатом выполнения других предложений оператора (источников строк) являются наборы строк, из которых должны быть получены выходные данные. Предложение SELECT определяет, какие столбцы этих строк относится к выходным данным. SELECT может возвращать как сами значения столбцов, так и использовать их в составе агрегатных функций или других выражений. Выражения значений могут относиться к данным типа NUMERIC, STRING, DATУTIME или INTERVAL. Кроме того, они могут включать в себя выражения CAST и CASE, агрегатные функции и подзапросы. Если задан квалификатор DISTINCT, строки сравниваются, и при обнаружении строки-дубликата в результате будет возвращена только одна копия строки. Предложение SELECT может содержать: Агрегатные функции, которые служат для получения единственного Значения из группы значений столбца. Например, SUM или COUNT. Символ "*", который означает, что все столбцы всех таблиц, приведенные в списке предложения FROM, являются выходными данными и размещаются в порядке их перечисления в предложении FROM. В последовательности "квалификатор.*", где квалификатор означает имя таблицы или имя корреляции, на которое имеется ссылка в предложении FROM. (Корреляция осуществляется через псевдоним для таблицы в зависимом подзапросе.) Все столбцы этой таблицы (возможно производной) являются выходными данными, исключая общие столбцы соединяемых таблиц. (В стандартном синтаксисе данного параметра есть некоторая неопределенность, однако это точное выражение, приведенное в стандарте ISO). Выражение для вычисления значения оператора SELECT обычно содержит имя столбца одной из таблиц, указанных в предложении FROM. При этом значения столбца либо являются выходными данными без преобразований, либо становятся частью выражения, например, AMOUNT*3. Имя столбца, указанное в предложении AS, является именем столбца выходных данных (столбец-результат). Если выходные столбцы получены из одною (и только одного) столбца, указанного в предложении FROM, то они наследуют имя этого столбца по умолчанию. При желании можно переопределить это имя с помощью предложения AS. Способы присвоения имен столбцов не "напрямую" от исходных зависят от конкретного приложения. Стандарт не требует присвоения имени каждому столбцу-результату, полученному с помощью предложения SELECT. Однако имена столбцов могут оказаться необходимыми для точного определения способов использования выходных данных (как, например, в представлении). Ключевое слово AS согласно стандарту можно и опустить, оно неявно подразумевается. Если агрегатные функции и выражения значений употребляются совместно, то все выражения должны указываться в предложении GROUP BY.

Агрегатные функции

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

AVG - возвращает среднее значение в указанном столбце

AVG ([DISTINCT] имя_столбца)
Вычисляет среднее значение в столбце из выборки. Среднее значение - это сумма значений столбца по всем строкам выборки, разделенная на число строк. При вычислении учитываются нулевые значения столбца. Если число строк, подходящих под условия поиска, равно нулю, AVG возвращает нулевое значение. DISTINCT игнорирует дублирующиеся значения указанного столбца при вычислении среднего значения.

COUNT - возвращает количество строк, удовлетворяющих условию поиска

COUNT ( * | [DISTINCT] имя_столбца)
Считает количество строк, получаемых оператором SELECT. DISTINCT игнорирует дублирующиеся значения указанного столбца при подсчете строк.

MAX - возвращает наибольшее значение в указанном столбце

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

MIN - возвращает наименьшее значение в указанном столбце

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

SUM - считает сумму значений в столбце

SUM ([DISTINCT] имя_столбца)
Вычисляет сумму значений в столбце из выборки. Если число строк, подходящих под условия поиска, равно нулю, SUM возвращает нулевое значение. DISTINCT игнорирует дублирующиеся значения указанного столбца при суммировании.

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

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

Соединения

Когда в предложении FROM поименованы несколько таблиц, то все они неявно считаются соединяемыми. По сути это означает, что можно получить все возможные комбинации строк (по одной из каждой таблицы), и именно с такой конкатенацией будут работать остальные операторы запроса. Эта конкатенированная таблица носит название декартово произведение (Cartesian product) или перекрестное соединение (cross join). Чаше всего пользователю нужно исключить большинство строк и выделить определенные данные, что обычно реализуется посредством установления отношений (или условий) при помощи предложения WHERE. Другой способ установить отношения - использование встроенных операций соединения, чтобы осуществить внутреннее соединение в предложении FROM. Результат этою соединения есть порожденная таблица, которая и должна обрабатываться остальными операторами запроса. Можно использовать оба способа совместно, однако, чаше всего сложности в "запутанной" логике исполнения оператора намного превышают полученные преимущества. В предыдущем стандарте встроенные операции соединения не поддерживались, поэтому те, кто уже постиг искусство соединения таблиц "вручную", возможно, обнаружат, что удобнее использовать новый способ, так как для любых групп стандартных операций встроенные соединения реализовать намного проще.

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

перекрестное соединение ::=
таблица A CROSS JOIN таблица В

естественное соединение ::=
таблица A [NATURAL] [тип соединения] JOIN таблица В

соединение объединения ::=
таблица A UNION JOIN таблица В

объединение посредством предиката ::=
таблица А [тип соединения] JOIN таблица В ON предикат

объединение посредством имен столбцов ::=
таблица А [тип соединения] JOIN таблица В USING (имя столбца.,..)

тип соединения ::=
INNER
| { { LEFT | RIGHT | FULL | [OUTER] }

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

Предложение WHERE содержит предикат, который может включать одно или несколько выражений и принимать одно из трех значений: TRUE, FALSE или UNKNOWN. Сравнение значения NULL с другим значением (в том числе и NULL) дает результат UNKNOWN. Другие значения сравниваются в соответствии с последовательностями сортировки для строк текста, с порядком числовых значений для числовых типов, хронологическим порядком для данных типа дата-время или по величине значения (для данных типа INTERVAL). Сравнения осуществляются с помощью операторов =, <, <=, >, >= и <> (не равно). Применение таких операторов, как * (умножение) или | (конкатенация), зависит от типа данных. В большинстве случаев вместо простых выражений можно использовать конструкторы значений строк.

B BETWEEN A AND C
Это выражение эквивалентно: (A<=B) AND (B<=C). Параметр A должен быть меньше C. Выражение B BETWEEN С AND A будет интерпретироваться как (C<=B) AND (B<=A), и оно имело бы значение FALSE при значении выражения (A<=B) AND (B<=C) равном TRUE, за исключением случая, когда все три величины одинаковы. Если один из параметров равен NULL, значение предиката не определено.

A IN (C, D, …)
Это выражение будет истинным, если A равняется одному из значений, включенных в список.

A LIKE ‘строка’
В этом случае подразумевается, что A - строка символов, и операция заключается в поиске указанной подстроки. При этом можно использовать строку фиксированной длины или строку с шаблоном подстановки.

A IS NULL
Это выражение проверяет, является ли A значением NULL, В отличие от большинства других предикатов результат данного предиката может быть только TRUE или FALSE (не UNKNOWN).

A оператор_сравнения SOME | ANY подзапрос
SOME и ANY имеют одинаковый смысл. Результатом подзапроса является набор величин. Если для какого-нибудь значения X, получаемого из подзапроса, результат операции «А оператор_сравнения X» равняется TRUE, то предикат ANY также равен TRUE.

A оператор_сравнения ALL подзапрос
Исполняется так же, как и ANY, но для всех значений X, получаемого из подзапроса, результат операции «А оператор_сравнения X» должен равняться TRUE.

EXISTS подзапрос
Если в результате подзапроса найдена хотя бы одна строка, предикат равняется TRUE, в противном случае - FALSE. Результат никогда не может быть UNKNOWN. Это выражение имеет смысл только для зависимого подзапроса.

UNIQUE подзапрос
Если подзапрос не находит идентичных строк, то значение UNIQUE равняется TRUE, в противном случае - FALSE. В этом предикате подразумевается, что идентичные строки не содержат значения NULL. В противном случае строки не идентичны.

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

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, M1N, AVG и т.д.). Если это предложение отсутствует и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT, должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют запросу. В противном случае все столбцы списка SELECT, не вошедшие в агрегатную функцию, должны быть "сгруппированы" с помощью предложения GROUP BY. Все выходные строки запроса, которые сгруппированы по равенству значений столбцов, образуют единую группу (для GROUP BY все значения NULL трактуются, как равные). Агрегатная функция будет применяться к каждой из таких групп. Рассмотрим простой пример:

SELECT snum, AVG (amount), MAX (amount)
FROM Salespeople
GROUP BY snum;

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

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

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

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

UNION, INTERSECT, EXCEPT, и CORRESPONDING

Эти операторы используют и качестве аргумента весь оператор SELECT (запрос), исключая предложение ORDER BY, в соответствии со следующим синтаксисом:

запрос A {UNION | INTERSECT | EXCEPT} [ALL] запрос В

Предложение TABLE является традиционным способом записи простого запроса:

TABLE Salespeople

Этот оператор эквивалентен следующему оператору:

SELECT * FROM Salespeople

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

Предложение UNION включает все выходные строки каждого из запросов. Если определен параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае оставляется только одна строка. Если определено предложение EXCEPT, то из результата исключаются все строки второго запроса, а в выходных данных остается только одна из дублированных строк. Если определено EXCEPT ALL, то число дублированных строк запроса В вычитается из числа дублированных строк запроса А, и полученное число строк появляется на выходе, если, конечно, оно не равно 0. Если определено INTERSECT, то нее строки из запросов А и В появляются на выходе в одном экземпляре. Если определено INTERSECT ALL, то дублированные строки будут появляться на выходе то число раз. которое является минимумом числа их дублей в запросах A и В.

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

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

И, наконец, предложение ORDER BY используется для сортировки выходных данных. Строки сортируются в соответствии со значениями столбцов, указанных в списке. Первый столбец имеет наивысший приоритет, второй столбец и задает порядок сортировки дублируемых значений мерного столбца, третий столбец вступает в действие, если совпадают значения но втором столбце, и т.д. Можно задать параметр сортировки ASC (по возрастанию, используется по умолчанию) или DESC (по убыванию) отдельно для каждого столбца. Сортировка набора символов будет осуществляться в соответствии с его упорядочивающей последовательностью. Вместо имен столбцов можно указывать целые числа. Эти числа указывают па местоположение столбца в выходных данных, так что 1 будет указывать на первый столбец, а 5 на пятый столбец и т.д. Если выходные столбцы не имеют имен, то будут использоваться номера.

Пример:

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

SELECT snum, SUM (amount), AVG (amount), odate
FROM Orders
WHERE odate BETWEEN '10-01-1992' AND '10-01-1994'
GROUP BY snum, odate
HAVING SUM (amount) > 100.00;