Внутренние и внешние соединения таблиц.
Внутреннее соединение таблиц
В условии поиска может сравниваться значение столбца из одной таблицы со значением столбца из другой таблицы:
<имя столбца таблицы1> <оператор> <имя столбца таблицы2>
Такие условия часто используются для реализации механизма, аналогичного Lookup-полям локальных БД.
Например, извлечь информацию из таблицы лиц, с указанием реальных наименований типов лиц и подразделений, а не их условных номеров:
SELECT Lica.Name, Tipy.Name, Podrazd.Name
FROM Lica, Tipy, Podrazd
WHERE Tipy.NTip=Lica.Tip AND Lica.Podr=Podrazd.NPodr;
В соответствии с этим оператором, для каждой строки из таблицы лиц будет найдена строка с таким же условным номером типа лица в таблице типов лиц и строка с таким же условным номером подразделения в таблице подразделений. В результирующий НД будут включены не условные номера типов и подразделений, а их реальные наименования (при необходимости можно включить и условные номера).
Обратите внимание, что в предложении WHERE перечисляются все таблицы, участвующие в выборке, а ссылка на столбцы разных таблиц осуществляется путем включения в имя столбца префикса в виде имени его таблицы.
Такой способ объединения информации из разных таблиц получил название внутреннего соединения таблиц.
Замечание: Для внутреннего соединения порядок перечисления таблиц в условии поиска не имеет значения (не важно, столбец какой из таблиц упоминается слева, а какой – справа от знака равенства).
|
|
|
Логически внутреннее соединение таблиц выполняется следующим образом. Вначале строится полное декартово произведение перечисленных таблиц, то есть перебираются все возможные комбинации строк этих таблиц:
строка1 таблицы1, строка1 таблицы2, строка1 таблицы3
строка1 таблицы1, строка1 таблицы2, строка2 таблицы3
строка1 таблицы1, строка1 таблицы2, строка3 таблицы3
. . .
строка1 таблицы1, строка2 таблицы2, строка1 таблицы3
строка1 таблицы1, строка2 таблицы2, строка2 таблицы3
. . .
строка2 таблицы1, строка1 таблицы2, строка1 таблицы3
строка2 таблицы1, строка1 таблицы2, строка2 таблицы3
строка2 таблицы1, строка1 таблицы2, строка3 таблицы3
. . .
Затем из полученного (вероятно очень большого) НД выбираются строки, удовлетворяющие условиям поиска. (Фактически, в целях минимизации работы, SQL-сервер работает несколько иначе, но логически – процесс выглядит именно так.)
Например, пусть таблицы T1 и T2 имеют столбцы C1, C2, C3 и C1, C2, соответственно. И пусть T2 является родительской по отношению к T1, причем связаны они по значению столбцов T2.C1 и T1.C2:
Таблица T2
| C1 | C2 |
| 1 | x |
| 2 | y |
| 3 | z |
Тогда выполнение оператора:
SELECT T1.C1, T2.C2, T1.C3 FROM T1, T2 WHERE T1.C2=T2.C1;
приведет к построению следующего декартова произведения:
|
|
|
Замечание 1. При внутреннем соединении таблиц, в результирующий НД не включаются записи, для которых нет соответствия в парной таблице (в рассмотренном выше примере выборки из таблицы лиц с реальными наименованиями подразделений и типов в результирующий НД не включены поставщики и направления списания, так как для них нет соответствия в таблице подразделений). Если такие записи необходимо включить (например, для включения в выборку лиц поставщиков и направлений списания), следует использовать внешнее соединение таблиц (оно будет рассмотрено ниже).
Замечание 2. Рассмотренную в этом пункте форму записи внутреннего соединения таблиц называют неявной формой внутреннего соединения. Альтернативная явная форма предполагает использование предложения JOIN … ON для каждой присоединяемой таблицы. В этом случае приведенный выше пример будет выглядеть следующим образом:
SELECT Lica.Name, Tipy.Name, Podrazd.Name
FROM Lica
JOIN Tipy ON Tipy.NTip=Lica.Tip
JOIN Podrazd ON Lica.Podr=Podrazd.NPodr;
Рекомендуется использовать такую форму записи, поскольку она более информативна и позволяет четко разграничивать условия соединения таблиц (в предложениях JOIN после ключевого слова ON) от условий выбора строк (в предложении WHERE).
|
|
|
Внешние соединения таблиц
Выше было рассмотрено внутреннее соединение таблиц, при котором строится полное декартово произведение этих таблиц, из него отбираются записи, удовлетворяющие условию соединения, и в результирующий набор данных не включаются строки, не имеющие соответствия в парной таблице. Поэтому, например, если попытаться построить список всех лиц с указанием подразделений для подотчетных лиц:
SELECT L.Name, D.Name
FROM Lica L
JOIN Podrazd D ON D.NPodr=L.Podr;
то в результирующий набор не будут включены поставщики и направления списания, так как для них не указываются подразделения.
В таких случаях следует использовать внешние соединения таблиц, при которых в результирующий набор данных включаются и те записи ведущей таблицы, которым нет соответствия в ведомой таблице. Какая таблица является ведущей, определяется видом соединения:
SELECT <список столбцов>
FROM <таблица_1>
<вид_соединения> JOIN <таблица_2> ON <условие_поиска>;
где в качестве вида соединения может быть указано:
LEFT - левое внешнее соединение (ведущей является таблица_1);
RIGHT - правое внешнее соединение (ведущей является таблица_2).
Предложение ON используется, так же как и предложение WHERE для внутреннего соединения.
|
|
|
Приведенный выше пример может быть реализован с помощью следующего внешнего соединения:
SELECT L.Name, D.Name
FROM Lico L
LEFT JOIN Podrazd D ON D.NPodr=L.Podr;
Вложенные запросы.
При необходимости операторы SELECT могут быть вложенными. Например, дать информацию о наиболее дорогостоящем предмете:
SELECT Name, Cena FROM Predmety
WHERE Cena=(SELECT MAX(Cena) FROM Predmety);
Здесь необходим вложенный SELECT, так как в предложении WHERE нельзя использовать агрегатную функцию.
Замечание. Если вложенный запрос используется в сравнении со знаком равенства, то необходимо следить, чтобы он возвращал единственное значение (в противном случае будет сгенерирована ошибка). Если же нет уверенности в том, что будет возвращено единственное значение, то вместо знака равенства следует использовать ключевое слово включения в множество IN. Например, список проводок в отношении самого дорогостоящего предмета (следует учесть, что несколько различных предметов могут иметь одну и ту же максимальную цену):
SELECT * FROM Provodki WHERE Predmet IN (
SELECT NPredm FROM Predmety WHERE Cena=(
SELECT MAX(Cena) FROM Predmety));
Иногда для вложенного запроса важно знать, что существует хотя бы одна соответствующая запись, а количество этих записей не важно. В этом случае используется выражение:
[NOT] EXISTS (<запрос>)
Например, выбрать подотчетных лиц, у которых нет ни одной записи в таблице наличия (пусть условный номер типа лица для подотчетных лиц равен 2):
SELECT Name FROM Lica L WHERE Tip=2 AND NOT EXISTS(
SELECT * FROM Nalichie N WHERE N.Lico=L.NLic);
Если необходимо отобрать записи, для которых вложенный запрос возвращает одну и только одну строку, то используется выражение:
SINGULAR(<запрос>)
Например, выбрать подотчетные лица, у которых имеется единственная запись в таблице наличия:
SELECT Name FROM Lica L WHERE Tip=2 AND SINGULAR(
SELECT * FROM Nalichie N WHERE N.Lico=L.NLic);
В операторе SELECT имеется возможность сравнить значение некоторого выражения со всеми значениями, возвращаемыми вложенным запросом:
<выражение> <оператор> {ALL|SOME|ANY} (<запрос>)
В случае ALL сравнение дает истину, если значение выражения находится в отношении, указываемом оператором, со всеми значениями, возвращенными вложенным запросом. Для SOME (ANY – синоним) достаточно выполнения отношения хотя бы для одного значения, возвращенного вложенным запросом.
Например, выбрать подотчетные лица, у которых нет предметов, с ценой свыше тысячи рублей:
SELECT Name FROM Lica L WHERE 1000 > ALL(
SELECT P.Cena FROM Predmety P, Nalichie N
WHERE N.Lico=L.NLic AND P.NPredm=N.Predmet);
или выбрать лиц, у которых есть предметы с ценой выше 10 000 рублей:
SELECT Name FROM Lica L WHERE 10000 < SOME(
SELECT P.Cena FROM Predmety P, Nalichie N
WHERE N.Lico=L.NLic AND P.NPredm=N.Predmet);
Дата добавления: 2018-05-31; просмотров: 278; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!