Внешние соединения
Не-эквисоединения
Не-эквисоединения возникают, когда соединения производится не по общим столбцам. В этом случае обычно используются другие операторы сравнения, кроме равенства (>=, <=, <>, BETWEEN…AND… и т.п.).
Пример: Предположим, в базе данных имеется еще одна таблица: goods, содержащая список некоторых товаров. Таблица имеет следующие столбцы: name – наименование товара и cost – цена товара. Необходимо для каждого служащего определить список товаров, которые он может приобрести на свою месячную зарплату[1].
-- Предположим, данные таблицы goods следующие:
SELECT *
FROM goods;
Результат:
NAME COST
----------- ---------
TV 2000
Kettle 500
Refrigerator 1500
-- Решением примера является запрос:
SELECT s_emp.last_name, s_emp.salary, goods.name, goods.cost
FROM s_emp, goods
WHERE s_emp.salary>=goods.cost;
Результат:
LAST_NAME SALARY NAME COST
-------------- --------- ------------ ---------
Velasquez 2500 TV 2000
Velasquez 2500 kettle 500
Velasquez 2500 refrigerator 1500
Ropeburn 1550 kettle 500
Ropeburn 1550 refrigerator 1500
Nguyen 1525 kettle 500
Nguyen 1525 refrigerator 1500
Sedeghi 1515 kettle 500
Sedeghi 1515 refrigerator 1500
Giljum 1490 kettle 500
Ngao 1450 kettle 500
…
30 rows selected.
В случае, если строка таблицы не удовлетворяет условию соединения, она не включается в результат запроса. Например, при эквисоединении таблиц s_emp и s_customer клиент Sweet Rock Sports в выходном списке отсутствует, так как не имеет торгового представителя.
Строки, не удовлетворяющие условию соединения могут быть включены в результат запроса, если в условии соединения используется оператор внешнего соединения “(+)”. Этот оператор указывается на той стороне условия соединения, где не хватает информации. Он имитирует создание одной или нескольких строк с неопределенным значением, к которым можно присоединить одну или несколько строк, содержащих необходимые данные.
Пример: Для каждой фирмы-клиента выбрать ее номер и наименование, а также номер и фамилию ее торгового представителя. В список необходимо включить даже тех клиентов, которые не имеют торгового представителя.
Мы хотим получить всех фирм-клиентов без исключения, но стороны таблицы s_emp нам может не хватить информации: в таблице s_customer есть фирма-клиент, для которой не найдется торгового представителя в таблице s_emp. Поэтому знак внешнего соединения ставится в предложении WHERE со стороны таблицы s_emp.
SELECT c.id, c.name, e.id, e.last_name
FROM s_customer c, s_emp e
WHERE c.sales_rep_id=e.id(+);
Результат:
ID NAME ID LAST_NAME
--------- -------------------------- ---- ---------------
201 Unisports 12 Giljum
202 OJ Atheletics 14 Nguyen
203 Delhi Sports 14 Nguyen
204 Womansport 11 Magee
205 Kam's Sporting Goods 15 Dumas
206 Sportique 15 Dumas
207 Sweet Rock Sports
208 Muench Sports 15 Dumas
209 Beisbol Si! 11 Magee
210 Futbol Sonora 12 Giljum
211 Kuhn's Sports 15 Dumas
212 Hamada Sport 13 Sedeghi
213 Big John's Sports Emporium 11 Magee
214 Ojibway Retail 11 Magee
215 Sporta Russia 15 Dumas
15 rows selected.
-- Теперь фирма Sweet Rock Sports попала в результат запроса. Так как она не -- имеет торгового представителя, в полях о торговом представителе выводятся -- пустые значения.
Символ внешнего соединения может использоваться на любой стороне условия в предложении WHERE, но не по обеим сторонам. Он указывается после имени таблицы, в которой нет соответствующих строк. Условия, предполагающие внешнее соединение, не могут включать оператор IN и быть связанными с другими условиями с помощью оператора OR.
Соединения таблицы с собой
С помощью псевдонимов можно представить таблицу, как две отдельных таблицы. Это позволяет соединять строки таблицы с ее же строками.
Пример: Найти пары сотрудников, принятых на работу в один и тот же день.
В данном примере мы соединяем строки таблицы s_emp с ее же строками равенством по значению столбца start_date. Конечно, любой служащий принят в один день с самим собой, и чтобы исключить эту ненужную информацию, добавляется условие неравенства идентификаторов служащих.
SELECT one.last_name, two.last_name, one.start_date
FROM s_emp one, s_emp two
WHERE one.start_date=two.start_date
AND one.id<>two.id;
Результат:
LAST_NAME LAST_NAME START_DA
------------ --------------- --------
Smith Ngao 08.03.90
Ngao Smith 08.03.90
Biri Quick-To-See 07.04.90
Quick-To-See Biri 07.04.90
Magee Menchu 14.05.90
Menchu Magee 14.05.90
[1] Если Вы хотите проверить на практике действие команды из примера, таблицу goods можно создать и заполнить следующей последовательностью команд:
CREATE TABLE goods (name VARCHAR2(15), cost NUMBER(5));
INSERT INTO goods VALUES('TV', 2000);
INSERT INTO goods VALUES('Kettle', 500);
INSERT INTO goods VALUES('Refrigerator', 1500);