Внешние соединения

Не-эквисоединения

Не-эквисоединения возникают, когда соединения производится не по общим столбцам. В этом случае обычно используются другие операторы сравнения, кроме равенства (>=, <=, <>, 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);