Объединения таблиц
2.6. Объединения таблиц
В запросе можно объединить данные двух или более таблиц. Пусть, например, вы хотите получить список сотрудников всех производственных подразделений. В таблице Pers мы имеем список сотрудников с указанием в поле Dep подразделений, в которых они работают. А в таблице Dep мы имеем список всех подразделений в поле Dep и характеристику каждого подразделения в поле Proisv («n», если подразделение производственное, и «у», если оно относится к управлению). Тогда получить список сотрудников всех производственных подразделений можно оператором:
SELECT Pers.* FROM Pers, Dep
WHERE (Pers.Dep=Dep.Dep) AND (Dep.Proisv=’n’)
В нем мы обращаемся сразу к двум таблицам Pers и Dep, которые перечислены после ключевого слова FROM. Поэтому каждое имя поля предваряется ссылкой на таблицу, к которой оно относится. Впрочем, это надо делать только для полей, имя которых повторяется в разных таблицах (поле Dep). Перед полем Proisv ссылку на таблицу можно опустить. В конструкции WHERE условие Pers.DepHDep.Dep ищет запись в таблице Dep, в которой поле Dep совпадает с полем Dep текущей записи таблицы Pers. A условие Dep.Proisv= «n» отбирает те записи, в которых в таблице Dep найденному подразделению соответствует поле Proisv = «n».
В операторах, работающих с несколькими таблицами, обычно каждой таблице дается псевдоним, сокращающий ссылки на таблицы, а иногда придающий им некоторый смысл, вытекающий из данного применения. Псевдоним таблицы может записываться в списке таблиц после слова FROM, отделяясь от имени таблицы пробелом. Например, приведенный выше оператор может быть переписан следующим образом:
SELECT P.* FROM Pers P, Dep D
WHERE (P.Dep – D.Dep) AND (D.Proisv – «n»)
В этом примере таблице Pers дан псевдоним Р, а таблице Dep – D. Конечно, эти псевдонимы действуют только в данном операторе и не имеют никакого отношения к псевдонимам баз данных, которые мы постоянно используем.
Рекомендуемые материалы
Возможно самообъединение таблицы. В этом случае одной таблице даются два псевдонима. Пусть, например, мы хотим найти всех ровесников в организации. Это можно сделать оператором
SELECT pl.Fam, p2.Fam, pl.Year_b FROM Pers pi, Pers p2
WHERE (pl.Year_b = p2.Year_b) AND (pl.Fam != p2.Fam)
В этом примере для таблицы Pers мы ввели два псевдонима: pi и р2. В конструкции WHERE мы ищем в этих якобы разных таблицах записи с одинаковым годом рождения. Второе условие pl.fam != p2.fam нужно, чтобы сотрудник не отображался в результатах как ровесник сам себя. Правда, приведенный оператор выдает в результате по две записи на каждую пару ровесников, сначала, например, «Николаев – Андреев», а потом «Андреев – Николаев». Чтобы исключить такое дублирование можно добавить еще одно условие – pl.Fam < p2.Fam:
SELECT pl.Fam, p2.Fam, pl.Year) FROM Pers pi, Pers p2 WHERE (pl.Year_b = p2.Year_b) AND (pl.Fam != p2.Fam) and (pi.Fam < p2.Fam)
Дополнительное условие упорядочивает появление фамилий в р 1 и р2 и исключает дублирование результатов.
До сих пор мы рассматривали объединения, основанные на однозначном соответствии записей двух таблиц, когда каждой записи в первой таблице находилась соответствующая ей запись во второй таблице. Возможны и другие виды объединений, которые выдают записи независимо от того, есть ли соответствующее поле во второй таблице. Это внешние объединения (outer join). Их три типа: левое, правое и полное. Левое объединение (обозначается ключевыми словами LEFT OUTER JOIN ... ON) включает в результат все записи первой таблицы, даже те, для которых не имеется соответствия во второй. Правое объединение (обозначается ключевыми словами RIGHT OUTER JOIN ... ON) включает в результат все записи второй таблицы, даже если им нет соответствия в записях первой. Полное объединение (обозначается ключевыми словами FULL OUTER JOIN ... ON) включает в результат объединение записей обеих таблиц, независимо от их соответствия.
2.6.1. Левое объединение таблиц
Пусть, например, у вас есть таблица сотрудников некоей компании Pers и есть таблица Chef, в которой занесены данные на членов совета директоров этой компании. В число членов совета входят и сотрудники компании, и посторонние лица. Для определенности положим, что в таблице Pers имеются записи на сотрудников «Иванов» и «Петров», причем Петров является членом совета, а Иванов – нет. В таблице Chef имеются записи на членов совета «Петров» и «Сидоров», причем Сидоров – не сотрудник компании.
Тогда оператор
SELECT * FROM Pers LEFT OUTER JOIN Chef
ON Pers.Fam = Chef.Fam
выдаст результат вида:
Поля таблицы Pers | Поля таблицы Chef | ||
Иванов | …………………. | ||
Петров | Петров | …………….. |
Оператор задал левое объединение таблицы Pers (она указана после
ключевого слова FROM) с таблицей Chef (она указана после ключевых слов
LEFT OUTER JOIN). Условие объединения указано после ключевого слова ON
и заключается в совпадении фамилий.
Как показано, результат включает все поля и таблицы Pers, и таблицы Chef. Число строк соответствует числу записей таблицы Pers. В строках, относящихся к записям, для которых в Chef не нашлось соответствие, поля таблицы Chef остаются пустые.
2.6.2. Правое объединение таблиц
Оператор правого объединения
SELECT * FROM Pers RIGHT OUTER JOIN Chef
ON Pers.Fam = Chef.Fam
выдаст результат вида:
Поля таблицы Pers | Поля таблицы Chef | |||
Петров | ……… | Петров | …………….. | |
Сидоров | …………….. | |||
Число строк соответствует числу записей таблицы Chef. В строках, относящихся к записям, для которых в r,;rs не нашлось соответствие, поля таблицы Pers остаются пустые.
2.6.3. Полное объединение таблиц
Оператор полного объединения
SELECT * FROM Pers FULL OUTER JOIN Chef ON
Pers.Fam = Chef.Fam
выдаст результат вида:
Поля таблицы Pers | Поля таблицы Chef | ||
Иванов | …………………. | ||
Петров | Рекомендуем посмотреть лекцию "3 - Энтропия и информация". …………………. | Петров | …………….. |
Сидоров | …………….. |
В нем к строкам, относящимся к таблице Pers, добавлены строки, относящиеся к таблице Chef, для которых не нашлось соответствия в таблице Pers.