Многотабличные и вложенные запросы
Лекция 22. Многотабличные и вложенные запросы
Как правило, запросы выполняют обработку данных, расположенных во множестве таблиц. Попытка соединить таблицы по «умолчанию», приведет к декартовому произведению двух таблиц и вернет бессмысленный результат, например, если построить запрос по таблицам 7.3 и 7.4, следующим образом:
SELECT *
FROM А, В
Из раздела реляционной алгебры известно, что под соединением двух таблиц (будем рассматривать экви-соединение) понимается последовательность выполнения операции декартового произведения и выборки, т.е.:
SELECT *
FROM А, В
WHERE А.Код_товара = В.Код_тов
Рекомендуемые материалы
Использование подобного метода возвратит верный результат, приведенный в таблице 7.6. Описанный способ соединения, был единственным в первом стандарте языка SQL.
Стандарт SQL2 расширил возможности соединения до так называемого внешнего соединения (внутренним принято считать соединение с использованием предложения WHERE).
В общем случае синтаксис внешнего соединения выглядит следующим образом:
FROM <Таблица1> <вид соединения> JOIN <Таблица2> ON <Условие соединения>
Вид соединения определяет главную (ведущую) таблицу в соединении и может определяться следующими служебными словами:
§ LEFT – левое внешнее соединение, когда ведущей является таблица слева от вида соединения;
§ RIGHT – правое внешнее соединение, когда ведущей является таблица справа от вида соединения;
§ FULL - полное внешнее соединение, когда обе таблица равны в соединении;
§ INNER – вариант внутреннего соединения.
По правилу внешних соединений, ведущая таблица должна войти в результат запроса всеми своими записями, независимо от наличия соответствующих записей в присоединяемой таблице.
Приведем пример реализации внутреннего соединения для стандарта SQL2:
SELECT *
FROM А INNER JOIN В ON А.Код_товара = В.Код_тов
Вариант внешнего соединения, когда левая таблица является главной (ведущей):
SELECT *
FROM А LEFT JOIN В ON А.Код_товара = В.Код_тов
Продемонстрируем соединение нескольких таблиц на основе проекта «Библиотека». Пусть требуется получить информацию о принадлежности книг к тем или иным предметным областям. Известно, что каталог областей знаний и таблица «Книга» соединяются через промежуточную таблицу «Связь», в этом случае запрос может выглядеть следующим образом:
SELECT Каталог.Наименование as Область_знаний, Книги.ISBN, Книги.Название as Книга
FROM Книги INNER JOIN (Каталог INNER JOIN Связь ON Каталог.Код_ОЗ = Связь.Код_ОЗ) ON Книги.ISBN = Связь.ISBN;
Группировка по соединяемым таблицам не отличается от группировки по данным одной таблицы. Пусть требуется отобразить перечень читателей библиотеки с указанием количества книг, находящихся у них на руках, тогда запрос может выглядеть следующим образом:
SELECT DISTINCT Читатели.ФИО, Count(*) AS (Количество_книг)
FROM Читатели INNER JOIN Экземпляры ON Читатели.Номер_ЧБ = Экземпляры.Номер_ЧБ
GROUP BY Читатели.ФИО, Читатели.Номер_ЧБ, Экземпляры.Наличие
HAVING Экземпляры.Наличие = No;
Следующий запрос возвращает информацию о должниках и книгах, которые они должны были сдать в библиотеку с сортировкой по дате возврата:
SELECT Книги.Название, Книги.Автор, Экземпляры.Инв_номер, Экземпляры.Дата_возврата, Читатели.Номер_ЧБ, Читатели.ФИО, Читатели.Тел_дом, Читатели.Тел_раб
FROM Книги INNER JOIN (Читатели INNER JOIN Экземпляры ON Читатели.Номер_ЧБ = Экземпляры.Номер_ЧБ) ON Книги.ISBN = Экземпляры.ISBN
WHERE Экземпляры.Дата_возврата < Now() AND Экземпляры.Наличие=No
ORDER BY Экземпляры.Дата_возврата;
Вложенные запросы
Язык SQL позволяет вкладывать запросы друга в друга, это относится к оператору SELECT. Оператор SELECT, вложенный в другой оператор SELECT, INSERT, UPDATE или DELETE., называется вложенным запросом.
Вложенный оператор SELECT может употребляться в разделах WHERE или HAVING основного запроса и возвращать наборы данных, которые будут использоваться для формирования условий выборки требуемых значений основным запросом.
Средства языка SQL для создания и использования вложенных запросов можно считать избыточными, т.е. вложенность может быть реализована разными способами.
Если вложенный запрос возвращает одно значение (например, агрегат), то оно может использоваться в любом месте, где применяется подобное значение соответствующего типа. Если вложенный запрос возвращает один столбец, то его можно использовать только в директиве WHERE. Во многих случаях вместо вложенного запроса используется оператор объединения, однако некоторые задачи выполняются только с помощью вложенных запросов.
Вложенный запрос всегда заключается в скобки и, если только это не связанный вложенный запрос, завершает выполнение раньше, чем внешний запрос. Вложенный запрос может содержать другой вложенный запрос, который, в свою очередь, тоже может содержать вложенный запрос, и так далее. Глубина вложенности ограничивается только ресурсами системы. Синтаксис вложенного оператора SELECT более короткий и имеет следующий вид:
(SELECT [ALL | DISTINCT] слисок_ столбцов _вложенного_запроса
[FROM список_ таблиц]
[WHERE директива]
[GROUP BY директива]
[HAVING директива])
Приведем пример сложного вложенного запроса. Пусть необходимо получить список читателей имеющих максимальное число книг на руках, при условии, что максимальное количество книг у читателя не ограничено:
SELECT Читатели.Номер_ЧБ, Читатели.ФИО, COUNT(*) AS Количество
FROM Читатели INNER JOIN Экземпляры ON Читатели.Номер_ЧБ = Экземпляры.Номер_ЧБ
GROUP BY Читатели.Номер_ЧБ, Читатели.ФИО, Экземпляры.Наличие
HAVING Экземпляры.Наличие = No AND COUNT(*) =
(SELECT MAX(Количество)
FROM
(SELECT COUNT(*) AS Количество
FROM Экземпляры
GROUP BY Экземпляры.Номер_ЧБ, Экземпляры.Наличие
HAVING Экземпляры.Наличие = No))
Как и положено вложенным запросом первым выполняется самый «глубокий» по уровню вложенности подзапрос, который определяет количество книг на руках у каждого читателя:
SELECT COUNT(*) AS Количество
FROM Экземпляры
GROUP BY Экземпляры.Номер_ЧБ, Экземпляры.Наличие
HAVING Экземпляры.Наличие = No
Результат работы этого подзапроса список – количество книг по каждому номеру читательского билета. Особенным является то, что результат этого запроса используется в качестве источника строк для запроса более высокого уровня, который находит максимальное значение в этом списке, соответствующее максимальному количеству книг на руках у одного из читателей:
(SELECT MAX(Количество) FROM (SELECT …))
И, наконец, внешний запрос, выполняется в последнюю очередь, подсчитывает количество книг на руках у конкретного читателя и сравнивает его с максимальным количеством книг, полученным в результате работы вложенного запроса. Таким образом, может оказаться несколько читателей имеющих на руках максимальное количество книг.
В данном примере вложенный запрос возвращает агрегированное значение, полученное в результате итоговой функции MAX, поэтому условное выражение COUNT(*) = (SELECT MAX(Количество)…) имеет смысл. Если вложенный подзапрос может вернуть множество значений, то простое сравнение не подходит, необходимо использование служебных слов ANY или предиката IN, множество значений которого будет формировать вложенный подзапрос. Служебное слово ANY указывает на необходимость использования условного выражения для каждого значения, полученного во вложенном запросе.
Контрольные вопросы
1. Как можно получить декартово произведение двух таблиц?
2. Чем отличается соединение от объединения?
3. Какие виды соединений предусмотрены первым стандартом SQL?
4. Какой синтаксис имеют соединения?
5. Какие виды соединений вы знаете?
6. Для чего необходимы вложенные запросы?
7. Какие ограничения налагаются на вложенные запросы?
8. Как можно использовать предикат IN или служебное слово ANY?
Задания для самостоятельной работы
Рекомендуем посмотреть лекцию "3.4. Примеры по прогнозированию инженерной обстановки".
Задание 1. Запишите запрос для определения:
1. к каким предметным областям относится какая-либо книга;
2. какие книги относятся к определенной предметной области.
Задание 2. Дана таблица «Книг_у_читателей», содержащая поля «ФИО_читателя» и «Книг_на_руках». Запишите текст запроса для определения:
1. читателей держащих больше всего книг на руках;
2. читателей держащих меньше всего книг на руках.
