 |
Раздел 7. Запросы с соединением
|
|
|
|
Ранее рассматривались запросы, которые извлекали данные из одной таблицы, но более интересным и имеющим большое практическое применение является выборка в запросе связанных данных из нескольких таблиц. Рассматриваемые далее примеры используют базу данных, схема которой приводится ниже. Она содержит данные о студентах, преподавателях, и читаемых курсах.
CREATE TABLE Student -- студенты
(name CHAR(20), -- имя студента
class INT); -- группа
CREATE TABLE Course -- читаемые курсы
(title CHAR(20), -- название курса
staff CHAR(20)); -- имя преподавателя
CREATE TABLE Staff -- преподаватели
(name CHAR(20), -- имя преподавателя
position CHAR(20)); -- должность
CREATE TABLE Attends -- посещаемые курсы
(student CHAR(20), -- имя студента
title CHAR(20)); -- название курса
Ниже приведены данные, содержащиеся в каждой из таблиц.
| NAME | CLASS |
| Gill | |
| Jane | |
| Bill | |
| Polie |
| NAME | POSITION |
| Won Kim | Full Professor |
| G.Anderson | Senior Lector |
| S.Abiteboul | Full Professor |
| TITLE | STAFF |
| DBMS | G.Anderson |
| Graphics | G.Anderson |
| OS | G.Anderson |
| C Programming | Won Kim |
| Cryptography | Won Kim |
| STUDENT | TITLE |
| Jane | DBMS |
| Bill | DBMS |
| Jane | OS |
| Bill | C Programming |
| Polie | Graphics |
Рассмотрим запрос, который осуществляет выборку строк из двух таблиц: SELECT * FROM Course, Attends; (7.1)
| TITLE | STAFF | STUDENT | TITLE |
| DBMS | G.Anderson | Jane | DBMS |
| DBMS | G.Anderson | Bill | DBMS |
| DBMS | G.Anderson | Jane | OS |
| DBMS | G.Anderson | Bill | C Programming |
| DBMS | G.Anderson | Polie | Graphics |
| Graphics | G.Anderson | Jane | DBMS |
| Graphics | G.Anderson | Bill | DBMS |
| Graphics | G.Anderson | Jane | OS |
| Graphics | G.Anderson | Bill | C Programming |
| Graphics | G.Anderson | Polie | Graphics |
| OS | G.Anderson | Jane | DBMS |
| OS | G.Anderson | Bill | DBMS |
| OS | G.Anderson | Jane | OS |
| OS | G.Anderson | Bill | C Programming |
| OS | G.Anderson | Polie | Graphics |
| C Programming | Won Kim | Jane | DBMS |
| C Programming | Won Kim | Bill | DBMS |
| C Programming | Won Kim | Jane | OS |
| C Programming | Won Kim | Bill | C Programming |
| C Programming | Won Kim | Polie | Graphics |
| Cryptography | Won Kim | Jane | DBMS |
| Cryptography | Won Kim | Bill | DBMS |
| Cryptography | Won Kim | Jane | OS |
| Cryptography | Won Kim | Bill | C Programming |
| Cryptography | Won Kim | Polie | Graphics |
|
|
|
С точки зрения формальной семантики языка SQL, значение данного запроса образовано следующим образом. Каждая строка таблицы Course (всего 5 строк) “объединяется” с каждой строкой из таблицы Attends (5 строк); всего получается 5*5 строк в результирующем множестве. Подобное действие, получившее название соединение двух таблиц, (точнее говоря, в данном случае - кросс-сединением), похоже на математическую операцию декартового произведения двух множеств. Этот запрос, однако, является десемантизированным (бессмысленным), поскольку с содержательной точки зрения, его формальное значение – результирующее множество – представляет собой механически построенный набор полей, не имеющий какого-либо содержания. Более продуктивным является использование запросов следующего вида:
SELECT Attends.student, Course.title, Course.staff
FROM Course, Attends
WHERE Course.title = Attends.title; (7.2)
| STUDENT | TITLE | STAFF |
| Jane | DBMS | G.Anderson |
| Bill | DBMS | G.Anderson |
| Jane | OS | G.Anderson |
| Bill | C Programming | Won Kim |
| Polie | Graphics | G.Anderson |
Этот запрос уже имеет вполне осмысленный результат – имена студентов, названия посещаемых ими курсов и фамилии преподавателей. Обратите внимание, что в исходной базе данных не существует таблицы, содержащей одновременно эти три поля. Важнейшее назначение многотабличных запросов – извлечение данных из строк, между которыми подразумевается некоторая связь, и которая обычно подразумевает совпадение значений некоторых полей в обеих таблицах, как поля title из таблицы Course и title из Attends. Впрочем, язык SQL допускает использование любых условий при многотабличных запросах, помимо проверки на равенство, но такие запросы будут в большинстве случаев бессмысленны.
В этом запросе имена полей квалифицированы именами таблиц, из которых эти поля извлекаются; это способствует наглядности запросов, и бывает необходимым для устранения неоднозначности, если в соединяемых таблицах существуют одноименные поля.
|
|
|
Формальное вычисление значение запроса, называемого запросом с эквисоединением двух таблиц, можно представить как (1) вычисление набора всех возможных пар строк из обеих таблиц и (2) удаление из этого набора строк, не удовлетворяющих условию WHERE.
Запросы 7.1 и 7.2 можно записать в соответствии с более современным синтаксисом языка, используя конструкцию (INNER) JOIN:
SELECT Attends.student, Course.title, Course.staff
FROM Course CROSS JOIN Attends; (7.3)
SELECT Attends.student, Course.title, Course.staff
FROM Course JOIN Attends
ON Course.title = Attends.title; (7.4)
SELECT student, title, staff
FROM Course JOIN Attends USING (title); (7.5)
SELECT title, staff, student
FROM Course NATURAL JOIN Attends; (7.6)
Запрос 7.5 использует конструкцию JOIN USING, предполагающую соединение двух таблиц по совпадающим именам полей в таблицах; вариант соединения в запросе 7.6 называется естественным соединением (NATURAL JOIN), при этом в паре таблиц предполагается наличие одноименных полей. При использовании естественного соединения и конструкции JOIN USING имена полей в выражениях после SELECT не могут быть квалифицированными. Конструкция CROSS JOIN в запросе 7.3 реализует декартово произведение.
Рассмотрим трех- и четырехтабличные запросы:
SELECT Student.name, Student.class, Attends.title, Course.staff
FROM Student, Attends, Course
WHERE Student.name = Attends.student
AND Attends.title = Course.title; (7.7)
| NAME | CLASS | TITLE | STAFF |
| Bill | DBMS | G.Anderson | |
| Jane | DBMS | G.Anderson | |
| Polie | Graphics | G.Anderson | |
| Jane | OS | G.Anderson | |
| Bill | C Programming | Won Kim |
SELECT Student.name, Student.class, Attends.title,
Course.staff, Staff.position
FROM Student, Attends, Course, Staff
WHERE Student.name = Attends.student
AND Attends.title = Course.title
AND Course.staff = Staff.name; (7.8)
| NAME | CLASS | TITLE | STAFF | POSITION |
| Jane | OS | G.Anderson | Senior Lector | |
| Jane | DBMS | G.Anderson | Senior Lector | |
| Bill | C Programming | Won Kim | Full Professor | |
| Bill | DBMS | G.Anderson | Senior Lector | |
| Polie | Graphics | G.Anderson | Senior Lector |
Запрос 7.7 с использованием JOIN ON записывается так:
SELECT Student.name, Student.class, Attends.title, Course.staff
FROM (Student JOIN Attends
ON Student.name = Attends.student) JOIN
Course ON (Attends.title = Course.title); (7.9)
Скобки в последнем примере не обязательны и использованы для наглядности текста.
Вернемся к запросу 7.2. В выборке запись, соответствующая записи «Cryptography Won Kim» из таблицы Course отсутствует, поскольку в таблице Attends нет ни одной записи со значением Cryptography в поле Course. Если в результирующем множестве необходимо присутствие таких “несвязанных” записей, следует использовать так называемые несимметричные соединения - правое (RIGHT JOIN) или левое (LEFT JOIN) внешние соединения, или полное соединение, в отличие от симметричного (внутреннего) соединения. Следующие два эквивалентных примера иллюстрируют использование левого соединения:
|
|
|
SELECT Student.name, Attends.title
FROM Student LEFT JOIN Attends
ON Student.name = Attends.student (7.10)
SELECT Student.name, Attends.title
FROM Student, Attends
WHERE Student.name = Attends.student (+) (7.11)
| NAME | TITLE |
| Jane | DBMS |
| Bill | DBMS |
| Jane | OS |
| Bill | C Programming |
| Polie | Graphics |
| Gill | - |
В последнем запросе 7.11 используется архаичная запись операции левого соединения, характерная для ранних версий языка SQL в системе Oracle.
В результирующем множестве строки действительно присутствует строка, соответствующая записи “Gill” исходной таблицы Student, а значение поля Title, для которого значения в таблице Course не существует, получило формальное значение NULL. Ниже приведен пример правого соединения:
SELECT student, title, staff
FROM Attends RIGHT JOIN Course USING (title); (7.12)
| STUDENT | TITLE | STAFF |
| Jane | DBMS | G.Anderson |
| Bill | DBMS | G.Anderson |
| Jane | OS | G.Anderson |
| Bill | C Programming | Won Kim |
| Polie | Graphics | G.Anderson |
| - | Cryptography | Won Kim |
Наконец, приведем пример полного соединения (FULL JOIN), которое предполагает наличие «несвязанные» записей в обеих таблицах.
SELECT name, title, staff
FROM Student LEFT JOIN Attends
ON Student.name = Attends.student
FULL JOIN Course USING (title); (7.13)
В этом запросе сначала образуется левое соединение таблиц Student и Attends по полям name и student соответственно, что, как видно из примера 7.10, приводит к появлению в выборке пустых значений в поле title. Затем выполняется полное соединение полученной выборки с таблицей Course:
| NAME | TITLE | STAFF |
| Bill | DBMS | G.Anderson |
| Jane | DBMS | G.Anderson |
| Polie | Graphics | G.Anderson |
| Jane | OS | G.Anderson |
| Bill | C Programming | Won Kim |
| Gill | - | - |
| - | Cryptography | Won Kim |
Несимметричные соединения полезны для выявления «несвязанных» записей; этот тип запросов с соединением часто называется антисоединением. Следующий характерный пример определяет количество студентов, посещающих проводимые курсы, включая курсы, которые никто не посещает:
SELECT title "Курс", staff "Преподавателя",
COUNT(student) "Посещают,чел."
FROM Attends RIGHT JOIN Course USING (title)
|
|
|
GROUP BY title, staff; (7.14)
| Курс | Преподавателя | Посещают,чел. |
| OS | G.Anderson | |
| Graphics | G.Anderson | |
| C Programming | Won Kim | |
| DBMS | G.Anderson | |
| Cryptography | Won Kim |
Модифицируя предыдущий запрос, можно получить название курса, не посещаемого студентами:
SELECT title "Курс", staff "Преподаватель"
FROM Attends RIGHT JOIN Course USING (title)
GROUP BY title, staff HAVING COUNT(student) = 0; (7.15)
| Курс | Преподаватель |
| Cryptography | Won Kim |
Соединение таблиц может выполняться не только по одному полю, но и по комбинации полей (два или более). Рассмотрим, например, следующий запрос:
SELECT A.student, A.title, CR.result
FROM Attends A JOIN CourseResult CR
ON CR.student = A.student AND CR.Course = A.title (7.16)
| STUDENT | TITLE | RESULT |
| Bill | C Programming | |
| Jane | OS | |
| Jane | DBMS | |
| Bill | DBMS | - |
| Polie | Graphics | - |
В этом запросе соединение таблиц Attends и CourseResult происходит по комбинации полей – имя студента и название курса, поскольку каждое из этих полей по отдельности не идентифицирует соответствующую запись в этих таблицах. Это свойство данных определяется предположением, что один студент может посещать несколько курсов, и наоборот, – один курс могут посещать несколько студентов. Кроме того, один и тот же студент не может посещать один и тот же курс «дважды» и, соответственно, в таблицах Attends и CourseResult не может быть более чем по одной записи с одинаковыми именами студента и названием курса. Поэтому при соединении действительно необходимо использовать сравнение по комбинации полей.
Однако, с формальной точки зрения нет основания считать, что каждой записи в таблице Attends обязательно должна соответствовать запись в таблице CourseResult (хотя это было бы естественное предположение). Если такая ситуация возможна, то запрос 7.16 должен использовать соединение этих двух таблиц.
Если в запросе с соединением некоторая таблица в конструкции FROM используется два и более раз, такой запрос называется запросом с самосоединением. Такие запросы удобны для работы с рекурсивными данными, представленными в виде записей в SQL-таблице. Например, в следующем примере в таблице "Трубы" представлена топология газовых коммуникаций, которая имеет древовидную структуру:
| Паспорт | Номер | Отвод из |
| ТВД-12А | - | |
| ТСД-234А1 | ||
| ТСД-32И | ||
| ТНД-43В2 | ||
| ТСД-73 |
CREATE TABLE "Трубы"
("Паспорт" CHAR(100),
"Номер" INT,
"Отвод из" INT);
Рисунок 1. Топология газовых коммуникаций
Запрос 7.17 формирует список коммуникаций между трубами:
SELECT Branch."Паспорт" || ' выходит из ' || MainPipe."Паспорт"
"Коммуникации"
FROM "Трубы" MainPipe RIGHT JOIN "Трубы" Branch
ON MainPipe."Номер" = Branch."Отвод"; (7.17)
| Коммуникации |
| ТСД-32И выходит из ТСД-234А1 |
| ТСД-73 выходит из ТВД-12А |
| ТСД-234А1 выходит из ТВД-12А |
| ТНД-43В2 выходит из ТСД-32И |
| ТВД-12А выходит из |
|
|
|
В этом запросе используемые имена MainPipe и Branch называются синонимами. Любую таблицу можно снабдить синонимом; обычно это делается, чтобы избежать в выражениях запроса использования длинных имен исходных таблиц. В случае запросов с самосоединением использование синонимов необходимо для избежания неоднозначности.
Список, созданный запросом 7.18, можно улучшить при помощи оператора CASE (см. раздел 9):
SELECT 'Труба ' || Branch."Паспорт" ||
CASE WHEN MainPipe."Паспорт" IS NULL
THEN ' начальная'
ELSE ' выходит из ' || MainPipe."Паспорт"
END "Коммуникации"
FROM "Трубы" MainPipe RIGHT JOIN "Трубы" Branch
ON MainPipe."Номер" = Branch."Отвод"
ORDER BY Branch."Отвод" DESC; (7.17)
| Коммуникации |
| Труба ТВД-12А начальная |
| Труба ТНД-43В2 выходит из ТСД-32И |
| Труба ТСД-32И выходит из ТСД-234А1 |
| Труба ТСД-73 выходит из ТВД-12А |
| Труба ТСД-234А1 выходит из ТВД-12А |
Раздел 8. Подзапросы
SQL-запросы могут содержать вложенные SELECT-выражения, называемые подзапросами. Подзапросы используются, в том числе, в логических выражениях в конструкциях WHERE и HAVING, и в качестве «динамических» таблиц в конструкции FROM.
|
|
|
