Тема 2.2.4 Выборка данных
- Оператор SELECT
- Оператор UNION
- Операторы EXCEPT и INTERSECT
- Примеры выборки данных
1. Оператор SELECT
В операторе SELECT так же, как и во многих других операторах DML, можно использовать обобщенное табличное выражение CTE.
Синтаксис оператора SELECT
[ <предложение обобщенного табличного выражения> ]
SELECT [ ALL | DISTINCT ] [ TOP (<выражение>) [ PERCENT ] [ WITH TIES ] ]
<элемент выбора> [, <элемент выбора>]... [ INTO <новая таблица> ]
[ FROM <исходная таблица> [, <исходная таблица>]... ]
[ WHERE <условие выборки> ]
[ GROUP BY <условие группировки> ]
[ HAVING <условие поиска> ]
[ ORDER BY <список упорядочения> ] [ <предложение FOR> ]
[ OPTION ( <подсказка запроса> [, <подсказка запроса>]... ) ] ;
Выражение задает временный именованный набор данных. К этому набору данных можно обращаться в любом предложении оператора SELECT.
После ключевого слова SELECT можно указать ALL или DISTINCT. Ключевое слово ALL (значение по умолчанию) указывает, что в результирующем наборе данных будут включены все выбранные строки, в том числе и те, которые содержат одинаковые данные. Если задано ключевое слово DISTINCT, то в набор данных будут помещаться только уникальные строки. Иными словами, в наборе данных не будет двух строк, все столбцы которых имеют одинаковые значения. При этом (внимание!) значения NULL считаются равными.
В предложении TOP указывается количество либо процент (ключевое слово PERCENT) выбираемых строк.
Далее идет список выбора, который содержит список элементов, разделенных запятыми.
Синтаксис элемента выбора
<элемент выбора> ::=
{ *| { <имя таблицы> | <имя представления> | <псевдоним таблицы> }.*
| {[{ <имя таблицы> | <имя представления> | <псевдоним таблицы> }. ]
{ <имя столбца> | $IDENTITY | $ROWGUID }
| <выражение> } [ [ AS ] <псевдоним столбца> ]
}
Если список содержит символ *, это означает, что должны выбираться все столбцы из таблицы или представления, заданного в предложении FROM. Если перед символом * стоит отделенное точкой имя таблицы или представления, то будут выбраны все столбцы указанной таблицы, представления. Таблица или представление могут быть заданы своим псевдонимом, который указывается в предложении FROM. Рекомендуется не использовать символ *, а явно задавать имена выбираемых столбцов. В списке выбора можно задавать список столбцов. Если столбец с указанным именем присутствует в более чем одной таблице, представлении, принимающих участие в операторе выборки данных, то во избежание двусмысленности (выполнение операции будет завершено с ошибкой) перед именем столбца нужно через точку указать имя таблицы, представления или псевдоним.
Если задано ключевое слово $IDENTITY, то в список выбора помещается столбец таблицы, представления со свойством IDENTITY.
Ключевое слово $ROWGUID означает, что в список выбора включается столбец, заданный со свойством ROWGUIDCOL.
После необязательного ключевого слова AS можно указать псевдоним столбца. Этот псевдоним будет использован в качестве заголовка отображаемого результирующего набора данных при выполнении запроса в командной строке или в Management Studio.
В списке выбора помимо столбцов могут присутствовать выражения. Это константы, функции, вложенные запросы, т. е. любое допустимое в языке Transact-SQL выражение, возвращающее одно значение.
Если после списка выбора присутствует предложение INTO, то в файловой группе по умолчанию создается новая таблица и в нее помещаются выбранные данные. Структура создаваемой таблицы, состав и типы данных элементов определяются в соответствии с составом элементов в списке выбора.
Таблица не может быть секционированной. В новую таблицу также не перемещаются существующие для исходной таблицы триггеры, ограничения и индексы.
В необязательном предложении FROM указываются объекты, из которых выбираются данные. Если предложение FROM не задано, то список выбора может содержать лишь константы, выражения. Такой оператор вернет только одну строку.
Несколько упрощенный синтаксис конструкции <исходная таблица> в предложении
FROM
<исходная таблица> ::=
{ <таблица> | <представление> } [ [ AS ] <псевдоним> ]
[ WITH (<подсказка таблицы> [, <подсказка таблицы>]...) ]
| <функция> [ [ AS ] <псевдоним> ]
| <таблица> <соединение> [ <соединение> ]...
}
<соединение> ::=
{ <тип соединения> <таблица> ON <условие>
| CROSS JOIN <таблица>
| { CROSS | OUTER } APPLY <таблица>
}
<тип соединения> ::=
{ [ INNER ] JOIN
| { LEFT | RIGHT | FULL } [ OUTER ] } [ <подсказка соединения> ] JOIN
}
В предложении FROM исходная таблица может быть задана таблицей или представлением. Объект может находиться в том же или в другом экземпляре сервера, и вне экземпляра сервера SQL. После необязательного ключевого слова AS может быть указан псевдоним таблицы (представления), который возможно применять при обращении к данному объекту.
После ключевого слова WITH в скобках указываются используемые в выборке данных подсказки. Подсказки включают варианты оптимизации запроса и виды применяемых блокировок. Эти указания будут использованы оптимизатором запросов.
Вместо таблицы или представления может быть указана функция с ее псевдонимом. Это может быть функция наборов строк или определенная пользователем функция. Функции должны возвращать объект, на который можно ссылаться как на таблицу. Функции набора строк — это функции OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML.
Замечательной возможностью оператора SELECT является соединение (join). Когда мы выполняем нормализацию таблиц, то часто таблица разделяется на две или более. Данные из нескольких таблиц могут быть вновь объединены в процессе выборки при помощи средств соединения. В предложении соединения после ключевого слова ON задается условие соединения.
Существует несколько видов соединения.
- Внутреннее соединение (INNER JOIN) — оператор возвращает только все совпадающие пары строк.
- Внешнее соединение (OUTER JOIN) — бывает левым (LEFT), правым (RIGHT) и полным (FULL):
- левое внешнее соединение (LEFT OUTER JOIN) — возвращает все строки левой (основной в операторе) таблицы, куда добавляются соответствующие условию соединения значения столбцов правой таблицы;
- правое внешнее соединение (RIGHT OUTER JOIN) — является зеркальным отражением левого внешнего. В результирующий набор помещаются все строки правой таблицы с добавлением значений указанных столбцов левой таблицы;
- полное внешнее соединение (FULL OUTER JOIN) — возвращает все строки левой и правой таблицы.
- Перекрестное соединение (CROSS JOIN) — в варианте CROSS JOIN возвращается перекрестное соединение двух таблиц, т. е. декартово произведение всех строк обеих таблиц. В этом варианте не используется условие соединения (ключевое слово ON).
Вариант APPLY с ключевыми словами CROSS и OUTER выполняет соединение левой таблицы с данными из правой таблицы. Для правой таблицы, как правило, используется функция с табличным значением, которой в качестве параметра передается значение столбца из левой таблицы. Функция использует полученный аргумент для выделения строки из правой таблицы.
При задании ключевого слова CROSS будут возвращены строки, для которых найдено соответствие в правой таблице, если задано ключевое слово OUTER, то возвращаются строки, для которых соответствие не было найдено.
В необязательном предложении WHERE указывается условие, на основании которого в результирующий набор выбираются данные. Если предложение не задано, то возвращаются все строки. Синтаксис конструкции <условие выборки>, которую также называют и условием поиска.
Синтаксис условия выборки
<условие выборки> ::=
{ (<условие выборки>) | [ NOT ] <предикат> | <условие выборки> { AND | OR } [ NOT ] <условие выборки> }
<предикат> ::=
{ <выражение> <операция сравнения> <выражение>
| <строковое выражение> [ NOT ] LIKE <строковое выражение>
[ ESCAPE '<символ>' ]
| <выражение> [ NOT ] BETWEEN <выражение> AND <выражение>
| <выражение> IS [ NOT ] NULL
| CONTAINS ( { <столбец> | <список столбцов> | * } )
, <условие CONTAINS> [ , LANGUAGE <язык> ]
| FREETEXT ( { <столбец> | <список столбцов> | * } )
, <строка FREETEXT> [ , LANGUAGE <язык> ]
| <выражение> [ NOT ] IN ( { <подзапрос> | <выражение> }
[ , { <подзапрос> | <выражение> } ] ... )
| <выражение> <операция сравнения>
{ ALL | SOME | ANY } ( <подзапрос> )
| EXISTS ( <подзапрос> )
}
Оператор SELECT выбирает строки из таблицы (таблиц) или из представления. Строки также могут быть получены при вызове функции, обращающейся к таблице (таблицам) или к представлению базы данных. Условие выборки — это конструкция, выражение, возвращающее логическое значение "истина", "ложь" или NULL для конкретного столбца или группы столбцов основной таблицы запроса для каждой исходной строки. На основании этого предложения в результирующий набор попадают, как правило, не все строки, а только та часть, которая соответствует условию выборки, т. е. для которых было получено значение "истина".
Предикат — это логическое выражение, довольно сложное в SQL, которое для каждой исходной строки возвращает значение "истина", "ложь" или неизвестное значение NULL. Здесь используются такие конструкции, как сравнение, конструкции LIKE, BETWEEN, CONTAINS и некоторые другие.
В условии выборки естественным образом присутствуют классические логические операции отрицания (NOT), конъюнкции (AND) и дизъюнкции (OR).
Первой конструкцией в синтаксисе предиката является
<выражение> <операция сравнения> <выражение>.
Выражением может быть любое допустимое выражение языка SQL. Чаще всего это имя столбца таблицы. В правой части предложения может использоваться оператор SELECT, который возвращает ровно одно значение.
Операциями сравнения являются операции:
= — равно;
!=, <> — не равно;
< — меньше;
> — больше;
<=, !> — меньше или равно, не больше;
>=, !< — больше или равно, не меньше.
Конструкция LIKE позволяет проверить совпадение строки с указанным шаблоном или отсутствие такого совпадения при указании ключевого слова NOT. В самом шаблоне могут присутствовать шаблонные символы.
Шаблонные символы
% Произвольная строка, содержащая ноль или любое количество символов
_ Любой один символ
[ ] Задание диапазона допустимых значений. Подходит любой символ из указанного диапазона. Символы диапазона указываются через дефис, например, [a-z]. Это означает, что допустимыми являются все буквы латинского алфавита [^] Задание диапазона недопустимых значений. Исключается любой символ из указанного диапазона. Указание [^a-z] означает, что все буквы латинского алфавита являются недопустимыми. В варианте [^abc] недопустимыми будут только перечисленные символы — a, b и c.
Ключевое слово ESCAPE позволяет задать символ, который дает возможность использовать шаблонные символы как обычные символы шаблона, а не как шаблонные символы. Если шаблонный символ нужно в строке использовать как обычный символ, который принимает участие в операции сравнения, то ему должен предшествовать символ, указанный после ключевого слова ESCAPE.
Конструкция BETWEEN возвращает истину, если значение находится в указанном диапазоне (значение меньше или равно второму значению диапазона и больше или равно первому значению) или, наоборот, не находится в этом диапазоне, если задано ключевое слово NOT.
Конструкция IS NULL (IS NOT NULL) выполняет проверку, является ли выражение неизвестным значением.
Конструкция CONTAINS позволяет выполнять полнотекстовый поиск в строковых типах данных CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, XML, VARBINARY, VARBINARY(MAX) полнотекстового индекса.
Полнотекстовый поиск позволяет выполнить очень сложную выборку данных не только на основании точного совпадения символов, но и с довольно сложными вариантами поиска. Здесь используются различные формы одного и того же слова.
Язык, на основании которого определяется словообразование, задается после ключевого слова LANGUAGE.
Конструкция FREETEXT также позволяет выполнять полнотекстовый поиск, но не на основании формы текста (синтаксиса), а на основании содержания (семантики).
Конструкция IN (NOT IN) задает выбор тех строк, значения столбцов которых находятся в указанном списке или не находятся в списке, при задании ключевого слова NOT. Элементами в списке могут быть константы, выражения, возвращающие одно значение, или подзапросы, также возвращающие ровно одно значение.
Следующий вариант в определении предиката содержит выражение, операцию сравнения, одно из ключевых слов ALL, SOME, ANY (их часто называют функциями, и с этим можно согласиться) и подзапрос, возвращающий одно или несколько значений. Этот вариант определяет список строк на основании операции сравнения, полученного списка из подзапроса и заданного ключевого слова.
Функция ALL вернет значение "истина", если выражение будет истинным для всех значений, полученных из подзапроса. Подзапрос должен возвращать любое количество значений одного столбца. Такие подзапросы называют в литературе скалярными.
Функции SOME и ANY — это синонимы. Подзапрос также является скалярным, т. е. возвращает произвольное количество значений одного столбца. Функции вернут значение "истина", если выражение будет истинным хотя бы для одного значения, полученного из подзапроса.
Функция EXISTS вернет значение "истина", если результирующий набор подзапроса будет содержать хотя бы одну строку. В этом случае подзапрос не обязательно должен быть скалярным.
Предложение GROUP BY позволяет выполнить группирование найденных строк для получения результата. Часто при этом используется и предложение HAVING.
Синтаксис предложения GROUP BY
GROUP BY <элемент GROUP BY> [, <элемент GROUP BY>] ...
<элемент GROUP BY> ::=
{ <элемент> | { ROLLUP | CUBE | GROUPING SETS } ( <элемент> [, <элемент>]... )
Часто предложение GROUP BY используется при появлении в списке выбора оператора SELECT агрегатных функций AVG (среднее значение), COUNT (подсчет количества строк), MIN (минимальное значение столбца в группе строк), MAX (максимальное значение столбца в группе строк), SUM (сумма числовых значений). В этом случае предложение GROUP BY должно содержать имя каждого столбца в списке выбора, который не присутствует в агрегатной функции. Имена разделяются запятыми.
В предложении GROUP BY также могут присутствовать функции ROLLUP(), CUBE(), GROUPING SETS(). Они создают довольно сложные строки, содержащие некоторые итоговые данные, и обычно используются для формирования отчетов.
Функция ROLLUP() позволяет создавать иерархические итоговые данные по уровням элементов, указанных в параметрах функции.
Функция CUBE() позволяет создавать итоги с использованием перекрестных вычислений, а функция GROUPING SETS() задает несколько вариантов группировки данных в одном запросе.
Предложение HAVING позволяет ограничить количество выбранных строк с учетом предложения GROUP BY. Если же предложение GROUP BY не присутствует в операторе, то HAVING применяется как предложение WHERE для ограничения количества возвращаемых оператором строк.
Предложение ORDER BY задает упорядочение строк результата запроса.
Синтаксис предложения ORDER BY
ORDER BY <элемент упорядочения> [ , <элемент упорядочения> ]
... [ <предложение OFFSET> ]
<элемент упорядочения> ::=
<выражение> [ COLLATE <порядок сортировки> ] [ ASC | DESC ]
<предложение OFFSET> ::=
OFFSET <выражение> ROW[S]
[ FETCH { FIRST | NEXT } <выражение> ROW[S] ONLY ]
Элемент упорядочения — это имя или псевдоним столбца либо номер столбца в списке выбора, по которому выполняется упорядочение. Если столбец имеет строковый тип данных, то после ключевого слова COLLATE можно указать используемый для упорядочения порядок сортировки. Упорядоченность может быть по возрастанию значений (ключевое слово ASC, значение по умолчанию) или по убыванию (DESC). Элементы в списке отделяются запятыми.
В предложении OFFSET можно указать, какое число начальных строк не будет помещено в результирующий набор данных. Предложение FETCH указывает количество возвращаемых строк.
Ключевые слова FIRST и NEXT являются синонимами и используются лишь для совместимости со стандартом. В этих предложениях выражением является целочисленный литерал или любое выражение, возвращающее целочисленное значение.
Необязательное предложение FOR позволяет задать допустимость обновления данных во время их просмотра, а также указывает, что результат выборки должен возвращаться в виде XML-документа.
Предложение OPTION задает подсказки запроса
2. Оператор UNION
Оператор UNION позволяет объединить в один выходной набор данных результаты выборки несколькими операторами SELECT.
Синтаксис оператора UNION
<запрос> UNION [ ALL ] <запрос> [ UNION [ ALL ] <запрос> ] ... ;
Результаты запросов в операторе должны полностью соответствовать по структуре. Допускаются некоторые отличия в типах данных столбцов, возвращаемых разными запросами. При этом типы данных должны быть совместимыми, т. е. должна быть возможность приведения типов данных из разных результатов к одному.
Присутствие ключевого слова ALL означает, что будут возвращены все строки запросов. При его отсутствии дублирующие строки будут удалены из результата.
Важный момент: предложение ORDER BY может присутствовать только в последнем операторе SELECT.
3. Операторы EXCEPT, INTERSECT
Эти операторы работают с двумя запросами. Оператор EXCEPT возвращает недублированные строки первого запроса, из которых удаляются строки, присутствующие во втором запросе. Иными словами, это теоретико-множественная операция вычитания из первого множества строк второго множества.
Оператор INTERSECT возвращает недублированные строки первого запроса, которые соответствуют строкам, полученным из второго запроса. Это операция пересечения двух множеств.
Синтаксис операторов EXCEPT и INTERSECT
<оператор EXCEPT, INTERSECT> ::=
<запрос> { EXCEPT | INTERSECT } <запрос>;
| 
