Являются ли левые внешние объединения ассоциативными?
Легко понять, почему левые внешние соединения не являются коммутативными, но у меня возникли проблемы с пониманием, являются ли они ассоциативными. Некоторые интернет-источники предполагают, что это не так, но мне не удалось убедить себя, что это так.
Предположим, у нас есть три таблицы: A, B и C.
Пусть A содержит два столбца: ID и B_ID, где ID - первичный ключ таблицы A, а B_ID - внешний ключ, соответствующий первичному ключу таблицы B.
Пусть B содержит два столбца, ID и C_ID, где ID - первичный ключ таблицы B, а C_ID - внешний ключ, соответствующий первичному ключу таблицы C.
Пусть C содержит два столбца, ID и VALUE, где ID - это первичный ключ таблицы C, а VALUE просто содержит некоторые произвольные значения.
Тогда не должен (A left outer join B) left outer join C быть равным A left outer join (B left outer join C)?
3 ответа
Если вы предполагаете, что вы ПРИСОЕДИНЯЕТЕСЬ к внешнему ключу, как, по-видимому, подразумевает ваш вопрос, то да, я думаю, что OUTER JOIN гарантированно будет ассоциативным, как это отражено в ответе Пшемыслава Крюглея.
Однако, учитывая, что вы на самом деле не указали условие JOIN, педантически правильный ответ таков: нет, они не гарантируют ассоциативность. Есть два простых способа нарушить ассоциативность с извращенным ON статьи.
1. Одно из условий JOIN включает столбцы из всех трех таблиц.
Это довольно дешевый способ нарушить ассоциативность, но, строго говоря, ничего в вашем вопросе не запрещает это. Используя имена столбцов, предложенные в вашем вопросе, рассмотрите следующие два запроса:
-- This is legal
SELECT * FROM (A JOIN B ON A.b_id = B.id)
JOIN C ON (A.id = B.id) AND (B.id = C.id)
-- This is not legal
SELECT * FROM A
JOIN (B JOIN C ON (A.id = B.id) AND (B.id = C.id))
ON A.b_id = B.id
Нижний запрос даже не является действительным, но верхний - Очевидно, что это нарушает ассоциативность.
2. Одно из условий JOIN может быть выполнено, несмотря на то, что все поля одной таблицы равны NULL.
Таким образом, мы можем даже иметь разное количество строк в нашем наборе результатов в зависимости от порядка соединений. Например, пусть условие для СОЕДИНЕНИЯ А на В будет A.b_id = B.id, но условие СОЕДИНЕНИЯ B на C будет B.id IS NULL,
Таким образом, мы получаем эти два запроса с очень разным выводом:
SELECT * FROM (A LEFT OUTER JOIN B ON A.b_id = B.id)
LEFT OUTER JOIN C ON B.id IS NULL;
SELECT * FROM A
LEFT OUTER JOIN (B LEFT OUTER JOIN C ON B.id IS NULL)
ON A.b_id = B.id;
Вы можете увидеть это в действии здесь: http://sqlfiddle.com/
В этой теме говорится, что они не ассоциативны: ассоциативно ли левое соединение?
Тем не менее, я нашел в Интернете какую-то книгу, в которой говорится, что ВНЕШНИЕ СОЕДИНЕНИЯ ассоциативны, когда таблицы на левой стороне и на правой стороне не имеют общих атрибутов ( здесь).
Вот графическое представление (MSPaint ftw):
Еще один способ взглянуть на это:
Поскольку вы сказали, что таблица A соединяется с B, а B соединяется с C, то:
- Когда вы впервые соединяете A и B, у вас остаются все записи из A. Некоторые из них имеют значения из B. Теперь, для некоторых из тех строк, для которых вы получили значение из B, вы получаете значения из C.
- Когда вы впервые соединяете B и C, вы и вся таблица B, где некоторые записи имеют значения из C. Теперь вы берете все записи из A и соединяете некоторые из них со всеми строками из B, соединенными с C. Здесь опять же, вы получаете все строки из A, но некоторые из них имеют значения из B, некоторые из которых имеют значения из C.
Я не вижу возможности, чтобы в описанных вами условиях произошла потеря данных в зависимости от последовательности соединений LEFT.
Основываясь на данных, предоставленных Тилаком в его ответе (который теперь удален), я построил простой тестовый пример:
CREATE TABLE atab (id NUMBER, val VARCHAR2(10));
CREATE TABLE btab (id NUMBER, val VARCHAR2(10));
CREATE TABLE ctab (id NUMBER, val VARCHAR2(10));
INSERT INTO atab VALUES (1, 'A1');
INSERT INTO atab VALUES (2, 'A2');
INSERT INTO atab VALUES (3, 'A3');
INSERT INTO btab VALUES (1, 'B1');
INSERT INTO btab VALUES (2, 'B2');
INSERT INTO btab VALUES (4, 'B4');
INSERT INTO ctab VALUES (1, 'C1');
INSERT INTO ctab VALUES (3, 'C3');
INSERT INTO ctab VALUES (5, 'C5');
SELECT ab.aid, ab.aval, ab.bval, c.val AS cval
FROM (
SELECT a.id AS aid, a.val AS aval, b.id AS bid, b.val AS bval
FROM atab a LEFT OUTER JOIN btab b ON (a.id = b.id)
) ab
LEFT OUTER JOIN ctab c ON (ab.bid = c.id)
ORDER BY ab.aid
;
ПОМОЩЬ AVAL BVAL CVAL
---------- ---------- ---------- ----------
1 A1 B1 C1
2 A2 B2
3 А3 SELECT a.id, a.val AS aval, bc.bval, bc.cval
FROM
atab a
LEFT OUTER JOIN (
SELECT b.id AS bid, b.val AS bval, c.id AS cid, c.val AS cval
FROM btab b LEFT OUTER JOIN ctab c ON (b.id = c.id)
) bc
ON (a.id = bc.bid)
ORDER BY a.id
;
ID AVAL BVAL CVAL
---------- ---------- ---------- ----------
1 A1 B1 C1
2 A2 B2
3 А3 В этом конкретном примере кажется, что оба решения дают одинаковый результат. Я не могу представить ни одного другого набора данных, который бы заставлял эти запросы возвращать разные результаты.
Проверьте в SQLFiddle:
В дополнение к предыдущим ответам: эта тема хорошо обсуждается в статье Michael M. David, Advanced ANSI SQL, Моделирование данных и обработка структуры, Artech House, 1999, стр. 19-21. Страницы доступны онлайн.
Я считаю особенно примечательным тот факт, что он обсуждает, что таблицы (LEFT JOIN ...) и предложения объединения (ON ...) должны рассматриваться отдельно, поэтому ассоциативность может относиться к обоим (реорганизация табличных предложений и реорганизация объединить условия, т. е. по пунктам). Таким образом, понятие ассоциативности не такое, как, например, для сложения чисел, оно имеет два измерения.