Язык манипуляции данными в SQL

Структурированный язык запросов – SQL – является единственным на сегодняшний день стандартным интерфейсом к реляционным базам дан-ных. Это означает, что любое взаимодействие приложения с базой данных осуществляется посредством передачи SQL-запросов и их исполнением СУБД. Даже если вы пользуетесь развитой инструментальной средой визуальной разработки приложения, все равно «за кадром» стоит SQL, операторы которого генерируются этой средой.

Любой производитель СУБД, заявляющий о поддержке языка SQL, обязан обеспечить определенный уровень соответствия стандарту этого языка. Для разработчика приложений это означает независимость от производителя, т.к. следование стандарту позволяет осуществить безболезненный переход к СУБД другого производителя без изменения или, по крайней мере, существенной переработки приложения.

Следует отметить, что ни один производитель не обеспечивает полного соответствия стандарту. Это проявляется не только в отсутствии тех или иных операторов или конструкций, но и в том, что зачастую используются операторы, отсутствующие в стандарте. Второе, собственно, является вполне естественным, т.к. стандарт разрабатывается не на пустом месте. Синтаксические конструкции, прошедшие апробацию в программных продуктах ведущих производителей и не нарушающие принципов реляционной теории, могут быть включены в очередное издание стандарта. На данный момент последняя версия стандарта называется SQL2003 (ANSI и ISO).

Можно выделить три составные части или подъязыка языка SQL:

Данная книга ориентирована на практическое применение языка SQL, т.е. в первую очередь на использование оператора SELECT, реализующего выборку данных из реляционных СУБД, и операторов INSERT, UPDATE и DELETE. Последние операторы служат для модификации данных и составляют вместе с оператором SELECT содержание языка DML. В этой «теоретической» части мы будем придерживаться тех синтаксических конструкций рассматриваемых операторов, которые, во-первых, соответствуют стандарту SQL-92, и, во-вторых, поддерживаются практически всеми коммерческими СУБД. Особенности реализации аналогичных конструкций в MS Access будут оговариваться особо.

Используемые примеры запросов адресуются к учебной базе данных, которая рассматривается в данной книге и которую можно загрузить с сайта http://msi77.narod.ru/Tables_2003.zip. Если вам потребуется практика после чтения этой главы, посетите сайт «Упражнения по SQL» (http://www.sql-ex.ru) и попробуйте решить представленные там упражнения. Если вам удастся решить все упражнения первого этапа, посвященные оператору SELECT и операторам модификации данных, то можно сказать, что вы приобрели базовые знания языка, которых окажется вполне достаточно во многих областях деятельности в сфере информационных технологий. Заодно вы сможете оценить значение стандарта, поскольку на сайте ис-пользуется MS SQL Server.

Мы сочли излишним (по крайней мере, в этом издании книги) излагать операторы языка определения данных. С помощью этих операторов в основном создаются и модифицируются базовые таблицы. Это обусловлено тем, что имеющийся в Access конструктор таблиц вполне восполнит этот пробел и избавит нас от необходимости несколько перегружать эту книгу. Если примеров в этой главе окажется недостаточно, можем порекомендовать дополнительно книгу Мартина Грабера [6].

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

Следует сказать несколько слов о том, где вы можете использовать язык SQL в Access. Во-первых, при создании сохраняемых запросов. Для этого в конструкторе запросов следует выбрать режим SQL. Здесь же можно просто тренироваться в написании запросов, не сохраняя их в базе данных. Режим SQL даст вам возможность писать любые запросы, включая те, которые невозможно создать при помощи построителя (например, запросы, использующие объединение – UNION). Заметим, что визуальный построитель можно использовать для обучения языку SQL, просто переключая режимы и анализируя автоматически генерируемый программой код SQL.

Во-вторых, вы можете использовать операторы SQL, встроенные в код процедур на языке VBA. Любой оператор, модифицирующий структуру или данные, вы можете исполнить с помощью следующей конструкции:

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

Глава 11. Оператор SELECT

Простой оператор SELECT

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

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

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

Ниже приводится результат выполнения этого запроса.

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

который вернет следующие данные

Следует отметить, что вертикальная выборка может содержать дубликаты строк в том случае, если она не содержит потенциального ключа, однозначно определяющего запись. В таблице Магазины потенциальным ключом (первичным ключом) является поле Код_магазина. Поскольку это поле отсутствует в запросе, в приведенном выше результирующем наборе имеются дубликаты строк (строки 4 и 7). Если требуется получить только уникальные строки (скажем, нас интересуют только имеющиеся варианты названий магазинов), то можно использовать ключевое слово DISTINCT:

что даст следующий результат:

Помимо DISTINCT, может применяться также ключевое слово ALL (все строки), которое принимается по умолчанию.

Чтобы упорядочить строки результирующего набора, можно выполнить сортировку по любому количеству полей, указанных в предложении SELECT. Для этого используется предложение ORDER BY список полей>, являющееся всегда последним предложением в операторе SELECT. При этом в списке полей могут указываться как имена полей, так и их порядковые позиции в списке предложения SELECT. Так, если требуется упорядочить результирующий набор по названиям магазинов в обратном алфавитном порядке, можно записать

или

Результат, приведенный ниже, будет одним и тем же.

Сортировку можно проводить по возрастанию (параметр ASC принимается по умолчанию) или по убыванию (параметр DESC).

Сортировка по двум полям

даст следующий результат

Горизонтальную выборку реализует предложение WHERE предикат>, которое записывается после предложения FROM. При этом в результирующий набор попадут только те строки из источника записей, для каждой из которых значение предиката равно TRUE. То есть предикат проверяется для каждой записи. Например, запрос «получить код поставщика и наименование для тех товаров, оптовая цена которых ниже 2 рублей» можно сформулировать следующим образом:

Результат выполнения этого запроса имеет вид:

В последнем запросе был применен предикат сравнения с использованием операции сравнения “” (меньше). Кроме этой операции сравнения, могут использоваться: “=” (равно), “>” (больше), “>=” (больше или равно), “=” (меньше или равно) и “>” (не равно). Выражения в предикатах сравнения могут содержать константы и любые поля из таблиц, указанных в предложении FROM. Символьные строки и константы типа дата/время записываются в апострофах.

Примеры простых предикатов сравнения:

Предикаты I

Предикаты представляют собой выражения, принимающие истинностное значение. Они могут представлять собой как простое выражение, так и любую комбинацию из любого конечного количества выражений, построенную с помощью булевых операторов AND, OR или NOT. Кроме того, в этих комбинациях может использоваться SQL-оператор IS, а также круглые скобки для изменения порядка выполнения операций.

Предикат в языке SQL может принимать одно из трех значений: TRUE (истина), FALSE (ложь) или UNKNOWN (неизвестно). Исключение составляют следующие предикаты: IS NULL (отсутствие значения), EXISTS (существова-ние), UNIQUE (уникальность) и MATCH (совпадение), которые не могут при-нимать значение UNKNOWN.

Правила комбинирования всех трех истинностных значений легче за-помнить, обозначив TRUE как 1, FALSE как 0 и UNKNOWN как 1/2 (где-то между истинным и ложным) [5].

Предикаты сравнения

Предикат сравнения представляет собой два выражения, соединяемых оператором сравнения. Имеется шесть традиционных операторов сравнения: =, >, , >=, =, >.

Данные типа NUMERIC (числа) сравниваются в соответствии с их алгеб-раическим значением.

Данные типа CHARACTER STRING (символьные строки, в Access этот тип называется Текстовый) сравниваются в соответствии с их алфавитной после-довательностью. Если а1а2…аn и в1 в2…вn - две последовательности символов, то первая «меньше» второй, если а1 в1, или а1 = в1 и а2 в2 и т.д. Считается также, что а1а2…аn в1в2…вm, если n m и а1а2…аn = в1в2…вn, т.е. если первая строка является префиксом второй. Например, ‘folder’ ‘for’, т.к. первые две буквы этих строк совпадают, а третья буква строки ‘folder’ предшествует третьей букве строки ‘for’. Также справедливо неравенство ‘bar’ ‘barber’, поскольку первая строка является префиксом второй.

Данные типа Дата/время сравниваются в хронологическом порядке.

Пример 11.1. Получить информацию о фактах продажи товара в количестве, превышающем 1000 штук:

Запрос возвращает следующие данные

Пример 11.2.Получить информацию о фактах списания и возврата товара в количестве, превышающем 1200 штук:

Результат выполнения запроса:

Предикат BETWEEN

Синтаксис этого предиката имеет вид:

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

Если значение предиката exp1 BETWEEN exp2 AND exp3 равно TRUE, в общем случае это отнюдь не означает, что значение предиката exp1 BETWEEN exp3 AND exp2

Пример 11.3. Найти наименования и розничные цены товаров, оптовая цена которых ле-жит в диапазоне от 8 до 9 рублей:

Предикат IN

Синтаксис:

Предикат IN определяет, будет ли значение проверяемого выражения обнаружено в наборе значений, который либо явно определен, либо получен с помощью табличного подзапроса. Здесь табличный подзапрос - это обычный оператор SELECT, который создает одну или несколько строк для одного столбца, совместимого по типу данных со значением проверяемого выражения. Если целевой объект эквивалентен хотя бы одному из указанных в предложении IN значений, истинностное значение предиката IN будет равно TRUE. Если для каждого значения Х в предложении IN целевой объект > X, истинностное значение будет равно FALSE. Если подзапрос выполняется, и результат не содержит ни одной строки (пустая таблица), предикат принимает значение FALSE. Когда не соблюдается ни одно из упомянутых выше условий, значение предиката равно UNKNOWN.

Пример 11.4.Найти информацию о товарах, списанных в третьем или пятом магазинах:

Обратите внимание на то, что номера магазинов заключены в апостро-фы. Это обусловлено тем, поле Магазин имеет строковый тип данных.

Пример 11.5.Найти информацию о товарах, которые были оприходованы в 4 или 5 магазинах и оптовая стоимость которых меньше 10 рублей:

Переименование столбцов и вычисления в результирующем наборе

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

Например, запрос

переименует столбец Количество в Продано, столбец Дата_продажи в Дата, а Магазин в В магазине.

Этот запрос для продаж объемом свыше 1200 возвратит идентификатор товара, количество проданных единиц, дату продажи и магазин, в котором эта продажа имела место:

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

Тогда будет получен следующий результат:

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

даст следующий результат:

Если же явно не указывать имя для выражения, то будет принят способ именования по умолчанию, который зависит от используемой СУБД. Так, в MS Access будут использованы имена типа выражение1 (или Expr1001) и т.д., а выходной столбец в MS SQL Server вообще не будет иметь заголовка.

Предикаты II

Предикат LIKE

Синтаксис

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

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

Пример 11.6Найти все товары, названия которых заканчиваются на букву ‘г’:

Пример 11.7Найти все названия товаров, которые заканчиваются на букву ‘г’, но не на ‘0г’:

Если искомая строка содержит трафаретный символ, то следует задать управляющий символ в предложении ESCAPE. Этот управляющий символ должен использоваться в образце перед трафаретным символом, сообщая о том, что последний следует трактовать как обычный символ. Например, если в некотором поле следует отыскать все значения, содержащие символ «_», то шаблон ‘%_%’ приведет к тому, что будут возвращены все записи из таблицы. В данном случае шаблон следует записать следующим об-разом:‘%#_%’ ESCAPE ‘#’

Для проверки значения на соответствие строке «25%» можно воспользоваться таким предикатом:

Истинностное значение предиката LIKE присваивается в соответствии со следующими правилами:

Использование значения NULL в условиях поиска

Предикат

позволяет проверить отсутствие (наличие) значения в полях таблицы. Использование в этих случаях простых предикатов сравнения может привести к неверным результатам, т.к. сравнение со значением NULL дает результат UNKNOWN (неизвестно).

Так, если требуется найти записи в таблице Товары, для которых в столбце Дата_поступления отсутствует значение (например, при поиске оши-бок ввода), можно воспользоваться следующим оператором:

Характерной ошибкой в подобных случаях является написание предиката в виде:

Этому предикату не соответствует ни одной строки, поэтому результи-рующий набор записей будет пуст, даже если имеются товары с неизвестной датой приема. Это происходит потому, что сравнение с NULL-значением согласно предикату сравнения оценивается как UNKNOWN. А строка попадает в результирующий набор только в том случае, если предикат в предложении WHERE есть TRUE. Это же справедливо и для предиката в предложении HAVING.

Аналогичной, но не такой очевидной ошибкой является сравнение с NULL в предложении CASE (см. ниже). Чтобы продемонстрировать эту ошибку, рассмотрим следующую проблему. В таблице Товары есть поле Срок_годн и Возврат. При этом реализация возврата (Возврат = TRUE) возможна только при наличии даты в поле Срок_годн, которая определяет последнюю дату реализации или дату расчета с поставщиком. В результате ошибок ввода может оказаться, что при возможности возврата не будет проставлена эта дата. Действительно, такие данные есть в нашей базе данных. Чтобы в этом убедиться, достаточно выполнить следующий запрос:

Вот результат выполнения вышеприведенного запроса:

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

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

А здесь мы получаем сравнение с NULL-значением, и в результате – UNKNOWN, что приводит к использованию ветви ELSE, и все остается, как и было. Правильным будет следующее написание:

т.е. проверка именно на присутствие в поле NULL-значения.

Получение итоговых значений

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

Все эти функции возвращают единственное значение. При этом функ-ции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(имя поля>) состоит в том, что вторая (как и остальные агрегатные функции) при подсчете не учитывает NULL-значения.

Пример 11.8. Найти минимальную и максимальную розничные цены на книги (категория товара = 1):

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

Пример 11.9.Определить количество товаров поставщика Смирновой Алевтины Марковны:

В результате получим

Пример 11.10. Определить количество товаров поставщика Смирновой Алевтины Мар-ковны, для которых установлен срок реализации.

Решение этой задачи легко получить из решения предыдущей задачи 11.9; для этого достаточно лишь указать аргумент в функции COUNT:

В данном случае подсчитываются только те строки, отвечающие предикату, которые имеют в поле Срок_годн любое не NULL значение.

В результате получим

Пример 11.11.Определить количество товаров поставщика Смирновой Алевтины Мар-ковны, имеющих различные розничные цены.

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

даст следующий результат:

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

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

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

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

Рассмотрим простой пример:

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

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

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

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

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

Пример 11.12.Получить количество товаров и среднюю цену для каждой категории товара, средняя цена в которой менее 200 рублей:

В результате выполнения запроса получим

Заметим, что в предложении HAVING нельзя использовать псевдоним (Avg_price), используемый для именования значений агрегатной функции в предложении SELECT. Дело в том, что предложение SELECT, формирующее выходной набор запроса, выполняется предпоследним перед предложением ORDER BY. Ниже приведен порядок обработки предложений в операторе SELECT:

Этот порядок не соответствует синтаксическому порядку общего пред-ставления оператора SELECT, который ближе к естественному языку:

Использование в запросе нескольких источников записей

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

A

B

Результат запроса

будет выглядеть следующим образом

Поэтому перечисление таблиц, как правило, используется совместно с условием соединения записей из разных таблиц, указываемым в предложении WHERE. Для приведенных выше таблиц таким условием может быть совпадение значений, скажем, в полях a и c:

Теперь результатом выполнения этого запроса будет следующая таблица

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

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

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

Пример 11.13. Найти имя поставщика и наименования поставляемых им товаров, имею-щих цену менее 2 рублей:

В результате каждый товар одного и того же поставщика выводится только один раз:

Иногда в предложении FROM требуется указать одну и ту же таблицу несколько раз. В этом случае обязательным является переименование.

Пример 11.14. Вывести пары разных канцелярских товаров (категория_товара = 9), имеющих одинаковые оптовые цены:

Здесь условие a.Код_товара b.Код_товара используется для того, чтобы не выводились одинаковые пары, отличающиеся только перестановкой, на-пример: {Ручка шариковая "Bic", Клей "ПВА"} и {Клей "ПВА", Ручка шариковая "Bic"}. Если в таблице Товары имеются одинаковые товары по одной и той же цене, то для исключения одинаковых строк следует применить DISTINCT. В результате получим следующую таблицу:

Переименование также является обязательным, если в предложении FROM используется подзапрос. Так, пример 11.13 можно переписать следующим образом:

Обратите внимание, что в этом случае в других предложениях опера-тора SELECT уже нельзя использовать квалификатор Поставщик, поскольку таблица Поставщик уже не используется. Вместо него используется псевдоним Post. Кроме того, ссылаться извне теперь можно только на те поля таблицы Поставщик, которые перечислены в подзапросе.

Явные операции соединения

В предложении FROM может быть указана явная операция соединения двух и более таблиц. Среди ряда операций соединения, описанных в стандарте языка SQL, рядом серверов баз данных поддерживается только операция соединения по предикату. Синтаксис соединения по предикату имеет вид:

Соединение может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом – LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение.

Предикат определяет условие соединения строк из разных таблиц. При этом INNER JOIN означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Как правило, предикат определяет эквисоединение по внешнему и первичному ключам соединяемых таблиц, хотя это не обязательно.

Различие между типами соединений демонстрирует следующий ролик.

Пример 11.15.Найти имя поставщика, наименование и оптовую цену каждого товара из категории «Фототовары» (Категория_товара = 15), имеющегося в базе данных:

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

Для визуального контроля в результирующий набор включен код по-ставщика как из таблицы Товары, так и из таблицы Поставщик:

Внешнее соединение LEFT JOIN (RIGHT JOIN) означает, что помимо строк, для которых выполняется условие предиката соединения, в результирующий набор попадут все остальные строки из левой (правой) таблицы. При этом отсутствующие значения полей из правой таблицы будут заменены NULL-значениями.

Пример 11.15.Найти поставщиков, у которых в базе данных нет ни одного товара:

Поскольку здесь используется правое соединение, то в результирующем наборе будут все поставщики из таблицы Поставщик, если для кого-нибудь из них нет ни одного товара в таблице Товары, то все поля из этой таблицы будут содержать NULL-значение. Это и используется в предложении WHERE для отбора именно таких поставщиков. Ниже представлен результат выполнения этого запроса:

Правое соединение RIGHT JOIN всегда можно заменить левым соедине-нием. В нашем случае правое соединение

будет эквивалентно левому соединению

Запрос же

Наконец, при полном соединении (FULL JOIN) в результирующую таблицу попадут не только те строки, которые имеют одинаковые значения в сопоставляемых столбцах, но и все остальные строки исходных таблиц, не имеющие соответствующих значений в другой таблице. В этих строках все столбцы той таблицы, в которой не было найдено соответствия, заполняются NULL-значениями. То есть полное соединение представляет собой комбинацию левого и правого внешних соединений. Так, запрос для таблиц A и B, приведенных выше:

даст следующий результат:

Заметим, что это соединение симметрично (коммутативно), т.е. A FULL JOIN B эквивалентно B FULL JOIN A. Обратите также внимание на обозначение A.*, что означает вывести все поля таблицы А.

Традиционные операции над множествами и оператор SELECT

Традиционные операции над множествами – это объединение, пересечение, вычитание и декартово произведение.

Декартово произведение

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

Напомним, что при декартовом произведении каждая строка из первой таблицы соединяется с каждой строкой второй таблицы. В результате количество строк результирующего набора равно произведению количества строк операндов декартова произведения. В нашем примере таблица Поставщик содержит 41 строку, а таблица Товары – 388. В результате получается 41*388 = 15908 строк. Поэтому мы не приводим здесь результат выполнения этого запроса. Вы можете сами проверить это утверждение, выполнив приведенный выше запрос на учебной базе данных.

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

Объединение

Для объединения запросов используется служебное слово UNION:

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

Пример 11.17.Найти идентификаторы и имена, как поставщиков, так и сотрудников:

Мы не приводим здесь результат выполнения запроса из-за размеров результата, в котором будет 52 строки (11 сотрудников и 41 поставщик).

Пересечение и разность

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

Однако многие СУБД не поддерживают эти предложения в операторе SELECT. Это справедливо и для MS Access. Поэтому для выполнения операций пересечения и разности могут быть использованы другие средства. Здесь уместно заметить, что один и тот же результат можно получить с помощью различных формулировок оператора SELECT. В случае пересечения и разности можно воспользоваться предикатом суще-ствования EXISTS.

Предикат EXISTS

Синтаксис

Предикат EXISTS принимает значение TRUE, если подзапрос содержит любое количество строк, иначе его значение равно FALSE. Для NOT EXISTS все наоборот. Этот предикат никогда не принимает значение UNKNOWN.

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

Пример 11.18. (пересечение).Найти идентификаторы тех поставщиков, которые поставляют как книги (код категории 1), так и парфюмерию (код категории 4):

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

Пример 11.19.(разность).Найти идентификаторы тех поставщиков, которые поставляют книги (код категории 1), но не поставляют парфюмерии (код категории 4):

В этом случае достаточно заменить в предыдущем примере EXIST на NOT EXIST. Т.е. выходные данные составят только те уникальные строки основного запроса, для которых подзапрос не возвращает ни одной строки. В итоге получим

Использование ключевых слов SOME|ANY и ALL с предика-тами сравнения

В предикате

SOME и ANY являются синонимами, т.е. может использоваться любое из них. Результатом подзапроса является один столбец величин. Если хотя бы для одного значения V, получаемого из подзапроса, результат операции значение выражения> оператор сравнения> V равняется TRUE, то предикат ANY также равняется TRUE.

Предикат

исполняется так же, как и ANY, однако значение предиката с ALL будет истинным, если для всех значений V, получаемых из подзапроса, предикат значение выражения> оператор сравнения> V дает TRUE.

Пример 11.20.Найти такие фототовары, которые совсем не продавались (т.е. идентифика-торы этих товаров отсутствуют в таблице Продажи):

Вот результат выполнения этого запроса:

Рассмотрим подробно этот пример. Предикат

ограничивает множество товаров одной категорией – фототоварами. Предикат

вернет значение TRUE, если товар, определяемый полем Код_товара ос-новного запроса, найдется в списке идентификаторов товара таблицы Продажи (возвращаемом подзапросом). Поскольку предикат используется в запросе с отрицанием NOT, то значение TRUE будет получено, если данного товара не окажется в списке. Этот предикат проверяется для каждой записи основного запроса, которыми являются все фототовары из таблицы Товары. Результирующий набор состоит из двух столбцов – наименования товара и даты его поступления.

Пример 11.21.Найти наименования и оптовые цены товаров бытовой техники (код категории – 10), стоимость которых превышает стоимость любого товара из категории «Спортивный инвентарь» (код категории – 5):

Приведем формальные правила оценки истинности предикатов, использующих параметры ANY|SOME и ALL:

Еще о подзапросах

Заметим, что в общем случае запрос возвращает множество значений. Поэтому использование подзапроса в предложении WHERE без предикатов EXISTS, IN, ALL и ANY, которые дают булево значение, может привести к ошибке времени выполнения запроса (run time error).

Пример 11.22. Найти наименования и оптовые цены спортивных товаров (код категории – 5), стоимость которых превышает минимальную стоимость товара из категории «Бытовая техника» (код категории – 10):

Этот запрос вполне корректен, т.к. скалярное значение Цена_опт сравнивается с подзапросом, который возвращает единственное значение. В результате получим:

Однако, если в ответ на вопрос «найти наименования и оптовые цены спортивных товаров (код категории – 5), стоимость которых совпадает со стоимостью товара бытовой техники (код категории – 10) написать следую-щий запрос

то при его выполнении мы можем получить такое сообщение об ошибке:

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

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

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

Пример 11.23.. Выбрать имя поставщика, категорию и наименование товара, оптовая цена которых лежит в диапазоне от 100 до 110 рублей.

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

В результате получим:

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

Пример 11.24.Найти разницу между средними значениями цены на бытовые товары и спорттовары.

Здесь вообще можно обойтись одним предложением SELECT в основном запросе:

-

В результате получим

В реализациях языка SQL может быть выполнено неявное преобразование типов. Так, например, в Transact-SQL (процедурное расширение языка SQL в SQL Server) при сравнении или комбинировании значений типов smallint и int, данные типа smallint неявно преобразуются к типу int.

Пример 11.25.. Вывести среднюю цену на товары бытовой техники с предваряющим текстом «средняя цена = ».

Попытка выполнить запрос в SQL Server

приведет к сообщению об ошибке:

(Не допускается неявное преобразование типа varchar к типу money. Ис-пользуйте для выполнения этого запроса функцию CONVERT.)

Это сообщение означает, что система не может выполнить неявное преобразование типа varchar к типу money. В подобных ситуациях может помочь явное преобразование типов. При этом, как указано в сообщении об ошибке, можно воспользоваться функцией CONVERT. Однако эта функция не стандартизована, поэтому в целях переносимости рекомендуется использовать стандартное выражение CAST. Им и ограничимся.

Итак, если переписать наш запрос в виде

в результате получим то, что требовалось:

Мы использовали выражение явного преобразования типов CAST для приведения среднего значения цены к строковому представлению.

Синтаксис выражения CAST очень простой

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