Как работает соединение SQL?
Я пытаюсь понять, как объединение работает внутри. В чем будет разница между способами выполнения следующих двух запросов?
For example
(A)
Select *
FROM TABLE1
FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
FULL JOIN TABLE3 ON TABLE1.ID = TABLE3.ID
And
(B)
Select *
FROM TABLE1
FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
FULL JOIN TABLE3 ON TABLE2.ID = TABLE3.ID
Обновлено: здесь я говорю о оракуле. Рассмотрим некоторые записи, представленные в таблице 2 и таблице 3, но не в таблице 1, запрос A даст две строки для этой записи, но B даст только одну строку.
FULL OUTER соединение возвращает все строки из ОБЕИХ таблиц с NULL для любых столбцов из таблицы, которая не соответствует критериям соединения. Что-то вроде комбинации LEFT OUTER JOIN и RIGHT OUTER JOIN.
Ответы 4
Оптимизатор вашей СУБД определит, как лучше всего выполнить запрос. Обычно это делается с помощью «оптимизации на основе затрат», когда рассматривается несколько различных планов запросов и выбирается наиболее эффективный. Если ваши два запроса логически идентичны, наиболее вероятно, что оптимизатор в конечном итоге будет использовать один и тот же план запроса, независимо от того, как вы его напишете. Фактически, в наши дни было бы плохим оптимизатором, который создавал бы разные планы запросов на основе таких незначительных различий в SQL.
Однако полные внешние соединения - это другое дело (по крайней мере, в Oracle), поскольку способ соединения столбцов влияет на результат. то есть 2 запроса являются взаимозаменяемыми нет.
Вы можете использовать AUTOTRACE в SQL Plus, чтобы увидеть различные планы:
SQL> select *
2 from t1
3 full join t2 on t2.id = t1.id
4 full join t3 on t3.id = t2.id;
ID ID ID
---------- ---------- ----------
1 1
1 row selected.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 29 (11)|
| 1 | VIEW | | 3 | 117 | 29 (11)|
| 2 | UNION-ALL | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 142 | 15 (14)|
| 4 | VIEW | | 2 | 90 | 11 (10)|
| 5 | UNION-ALL | | | | |
|* 6 | HASH JOIN OUTER | | 1 | 91 | 6 (17)|
| 7 | TABLE ACCESS FULL| T1 | 1 | 52 | 2 (0)|
| 8 | TABLE ACCESS FULL| T2 | 1 | 39 | 3 (0)|
|* 9 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 10 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 11 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
| 12 | TABLE ACCESS FULL | T3 | 1 | 26 | 3 (0)|
|* 13 | HASH JOIN ANTI | | 1 | 26 | 15 (14)|
| 14 | TABLE ACCESS FULL | T3 | 1 | 13 | 3 (0)|
| 15 | VIEW | | 2 | 26 | 11 (10)|
| 16 | UNION-ALL | | | | |
|* 17 | HASH JOIN OUTER | | 1 | 39 | 6 (17)|
| 18 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)|
| 19 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
|* 20 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 21 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 22 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T3"."ID"(+) = "T2"."ID")
6 - access("T2"."ID"(+) = "T1"."ID")
9 - access("T2"."ID" = "T1"."ID")
13 - access("T3"."ID" = "T2"."ID")
17 - access("T2"."ID"(+) = "T1"."ID")
20 - access("T2"."ID" = "T1"."ID")
SQL> select *
2 from t1
3 full join t2 on t2.id = t1.id
4 full join t3 on t3.id = t1.id;
ID ID ID
---------- ---------- ----------
1
1
2 rows selected.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 29 (11)|
| 1 | VIEW | | 3 | 117 | 29 (11)|
| 2 | UNION-ALL | | | | |
|* 3 | HASH JOIN OUTER | | 2 | 142 | 15 (14)|
| 4 | VIEW | | 2 | 90 | 11 (10)|
| 5 | UNION-ALL | | | | |
|* 6 | HASH JOIN OUTER | | 1 | 91 | 6 (17)|
| 7 | TABLE ACCESS FULL| T1 | 1 | 52 | 2 (0)|
| 8 | TABLE ACCESS FULL| T2 | 1 | 39 | 3 (0)|
|* 9 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 10 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 11 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
| 12 | TABLE ACCESS FULL | T3 | 1 | 26 | 3 (0)|
|* 13 | HASH JOIN ANTI | | 1 | 26 | 15 (14)|
| 14 | TABLE ACCESS FULL | T3 | 1 | 13 | 3 (0)|
| 15 | VIEW | | 2 | 26 | 11 (10)|
| 16 | UNION-ALL | | | | |
|* 17 | HASH JOIN OUTER | | 1 | 39 | 6 (17)|
| 18 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)|
| 19 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
|* 20 | HASH JOIN ANTI | | 1 | 26 | 6 (17)|
| 21 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)|
| 22 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T3"."ID"(+) = "T1"."ID")
6 - access("T2"."ID"(+) = "T1"."ID")
9 - access("T2"."ID" = "T1"."ID")
13 - access("T3"."ID" = "T1"."ID")
17 - access("T2"."ID"(+) = "T1"."ID")
20 - access("T2"."ID" = "T1"."ID")
Фактически, планы запросов идентичны, за исключением информации о предикатах.
Да, но то, как это будет выполнено, также определит результат, который мы получим. Рассмотрим некоторые записи, представленные в таблице 2 и таблице 3, но не в таблице 1, запрос A даст две строки для этой записи, но B даст только одну строку.
Метод выполнения НИКОГДА не должен влиять на окончательные результаты правильно сформированного оператора SQL. Если это так, значит, в вашей СУБД есть ошибка. Причина, по которой они дадут вам разные результаты, заключается в том, что они задают два разных вопроса.
Правда, я не обратил внимание на ключевое слово FULL, я думал о внутренних соединениях.
Используйте EXPLAIN PLAN:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm
Вы заявили, что интересуетесь «внутренним устройством», а затем задали пример, иллюстрирующий «семантику». Отвечаю семантикой.
Рассмотрим эти таблицы.
Table1 : 1, 4, 6
Table2 : 2, 4, 5
Table3 : 3, 5, 6
В обоих примерах сначала выполняется одно и то же соединение, поэтому я выполню это здесь.
FirstResult = T1 FULL JOIN T2 : (T1, T2)
(1, null)
(4, 4)
(6, null)
(null, 2)
(null, 5)
Пример (А)
FirstResult FULL JOIN T3 ON FirstItem : (T1, T2, T3)
(1, null, null)
(4, 4, null)
(6, null, 6) <----
(null, 2, null)
(null, 5, null) <----
(null, null, 3)
Пример (Б)
FirstResult FULL JOIN T3 ON SecondItem : (T1, T2, T3)
(1, null, null)
(4, 4, null)
(6, null, null) <----
(null, 2, null)
(null, 5, 5) <----
(null, null, 3)
Это логически показывает, как получить результаты из объединений.
Для «внутреннего» есть нечто, называемое оптимизатором запросов, который выдаст те же самые результаты, но при этом будет делать выбор реализации для быстрого выполнения вычислений / io. Эти варианты включают:
- к каким таблицам обращаться в первую очередь
- смотреть в таблицу, используя индекс или сканирование таблицы
- какой тип реализации соединения использовать (вложенный цикл, слияние, хеш).
Также обратите внимание: из-за того, что оптимизатор делает этот выбор и меняет эти варианты в зависимости от того, что он считает оптимальным, порядок результатов может измениться. По умолчанию результаты всегда упорядочены по принципу «самый простой». Если вам не нужен порядок по умолчанию, вам нужно указать порядок в своем запросе.
Чтобы точно увидеть, что оптимизатор будет делать с запросом (в этот момент, потому что он может изменить свое решение), вам необходимо просмотреть план выполнения.
С запросом A то, что вы получаете, включает записи в таблице 1 с соответствующей записью в таблице 3 без соответствующих записей в таблице 3 (нули для столбцов t2)
С запросом B вы не получите эти записи, потому что вы переходите только к table3 через table2. Если у вас нет соответствующей записи в table2, table2.id будет нулевым и никогда не будет соответствовать table3.id
ПОЛНЫЙ то же самое, что ПОЛНЫЙ НАРУЖНЫЙ. OUTER неоднозначно (это LEFT, RIGHT или FULL?), Но большинство интерпретирует его как FULL.