Многотабличные запросы
Для иллюстрации многотабличных запросов создадим таблицу для хранения списка групп - Gr, а в таблицу Student добавим поле – внешний ключ – ссылающееся на таблицу Gr.
CREATE TABLE Gr(
idGr smallint IDENTITY(1,1) PRIMARY KEY,
nameGr varchar(20) NULL,
fioKur varchar(20) NULL,
kurs smallint NULL)
До сих пор все запросы извлекали данные только из одной таблицы. В реальной жизни большинство запросов извлекают информацию из нескольких таблиц, без чего концепция реляционной базы данных была бы нежизнеспособной.
Объединение — это операция SQL, указывающая способ связи двух источников данных и необходимость включения в результаты данных из одного источника в случае отсутствия соответствующих данных в другом.
Для объединения данных из двух источников следует выполнить операцию объединения по общему полю. Если значения этого поля совпадают, данные записей в результатах запроса объединяются.
select nameGr, snameSt
from Student, Gr
where Gr.idGr=Student.idGr
Данный запрос выбирает данные из двух таблиц, при этом в результирующий набор попадут только те строки из обеих таблиц, в которых заполнено поле idGr. Такой способ называется ОБЪЕДИНЕНИЕМ ТАБЛИЦ ПО РАВЕНСТВУ ЗНАЧЕНИЙ В СТОЛБЦАХ.
JOIN — оператор языка SQL, позволяющий соединять записи из двух таблиц реляционной базы данных. Входит в раздел FROM оператора SELECT и отдельно от него не используется.
Оператор JOIN является реализацией операции соединения реляционной алгебры.
В общем виде синтаксис оператора имеет следующий вид:
SELECT
<СПИСОК ПОЛЕЙ>
FROM
Таблица1
{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN
Таблица2
ON <условие_объединения>
Рис.1 Оператор JOIN.
На рис.1 приведены все возможные варианты оператора JOIN.
-
INNER JOIN – внутренне объединение. Объединяет две таблицы, где каждая строка обеих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор.
SELECT nameGr, snameSt
FROM Student INNER JOIN Gr
ON (Gr.idGr=Student.idGr)
Запрос вернет только тех студентов, у которых указана группа в поле idGr, т.е. множество записей, которые находятся на пересечении двух таблиц – Student и Gr.
-
LEFT OUTER JOIN - левое внешнее объединение. К левой таблице присоединяются все записи из правой, соответствующие условию, плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.
SELECT nameGr, snameSt
FROM Student LEFT JOIN Gr
ON (Gr.idGr=Student.idGr)
Запрос вернёт ВСЕХ студентов, включая тех, у кого не указана группа, для них в поле nameGr будет значение NULL.
В запросе слово OUTER умышленно пропущено, его указание необязательно.
-
LEFT OUTER JOIN с проверкой на NULL–значения ключевого поля позволяет отобрать записи, представленные только в левой таблице.
SELECT nameGr, snameSt
FROM Student LEFT JOIN Gr
ON (Gr.idGr=Student.idGr)
WHERE Gr.idGr is NULL
Запрос вернёт студентов, у которых НЕ УКАЗАНА группа.
-
RIGHT JOIN. Аналогично left join, но применяется для правой таблицы.
К правой таблице присоединяются все записи из левой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL.
SELECT nameGr, snameSt
FROM Student LEFT JOIN Gr
ON (Gr.idGr=Student.idGr)
Запрос вернёт ВСЕ группы, включая те, для которых нет студентов в левой таблице, для них в поле snameSt будет значение NULL.
-
RIGHT JOIN с проверкой на NULL–значения ключевого поля позволяет отобрать записи, представленные только в правой таблице.
SELECT nameGr, snameSt
FROM Student LEFT JOIN Gr
ON (Gr.idGr=Student.idGr)
WHERE Gr.idGr is NULL
Запрос вернёт группы, в которых НЕТ студентов.
-
FULL OUTER JOIN
К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL и плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.
SELECT nameGr, snameSt
FROM Student FULL OUTER JOIN Gr
ON (Gr.idGr=Student.idGr)
Запрос вернёт всех студентов, у которых есть группа + всех студентов без группы + все группы без студентов.
-
FULL OUTER JOIN с проверкой на NULL–значения ключевых полей. Все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.
SELECT nameGr, snameSt
FROM Student FULL OUTER JOIN Gr
ON (Gr.idGr=Student.idGr)
WHERE Student.idGR is NULL OR Gr.idGr is NULL
Запрос вернёт студентов без группы и группы без студентов.
Кроме того, существует оператор CROSS JOIN - Декартово произведение. При использовании CROSS JOIN генерируется точно тот же результат, что и при вызове двух таблиц (разделенных запятой) без всякого JOIN вообще. Это значит, что мы получим огромный набор результатов, где каждая запись из Table1 будет дублирована для каждой записи из Table2. Если в Table1 содержится N1 записей, а в Table2 – N2 записей, в результате будет N1 х N2 записей.
SELECT nameGr, snameSt
FROM Student CROSS JOIN Gr
Запрос вернёт набор комбинаций каждого студента с каждой группой.
Оператор JOIN можно использовать для комбинирования любой пары таблиц, включая комбинацию таблицы с самой собой. Это называется SELF JOIN.
Например, добавим в таблицу Student поле idStarosta, в котором будет храниться код студента, который является старостой. Для выборки данных используем запрос:
SELECT S1.snameSt as Студент, S.snameSt as Староста
FROM Student S1 LEFT JOIN Student S
ON (S.idSt=S1.idStarosta)
Таблица объединяется сама с собой, поэтому мы вынуждены использовать псевдонимы. S1 будет являться источником студентов, из неё выбираем фамилии всех студентов (поэтому S1.snameSt as Студент), S будет «поставщиком» старост (поэтому S.snameSt as Староста). Нас интересует полный список студентов с указанием старост (даже если староста не указан, студент попадет в результирующий набор с NULL-значением в поле Староста), поэтому S1 находится слева от LEFT JOIN. Условием объединения будет равенство значений в поле idSt и idStarosta
SQL JOIN всегда соединяет две таблицы и ищет связанные объекты, отвечающие заданным правилам. Можно соединить несколько таблиц. Например, чтобы соединить три таблицы, потребуется 2 JOIN. Для каждой следующей таблицы потребуется еще один JOIN. Таким образом, чтобы соединить N таблиц, потребуется N-1 JOIN. SQL позволяет использовать в одном выражении разные типы соединений.