Использование одного и того же псевдонима таблицы в запросе дважды
Мой коллега, новичок в синтаксисе присоединения ANSI, недавно написал такой запрос:
SELECT count(*)
FROM table1 t1
JOIN table2 t2 ON
(t1.col_a = t2.col_a)
JOIN table3 t3 ON
(t2.col_b = t3.col_b)
JOIN table3 t3 ON
(t3.col_c = t1.col_c);
Обратите внимание, что table3 объединяется с table1 и table2 в разных столбцах, но два предложения JOIN используют один и тот же псевдоним таблицы для table3.
Запрос выполняется, но я не уверен в его правильности. Это правильный способ написания этого запроса?
Я думал, что соединение должно быть так:
SELECT count(*)
FROM table1 t1
JOIN table2 t2 ON
(t1.col_a = t2.col_a)
JOIN table3 t3 ON
(t2.col_b = t3.col_b AND
t3.col_c = t1.col_c);
Являются ли две версии функционально идентичными? У меня действительно недостаточно данных в нашей базе данных, чтобы быть уверенным.
Благодарю.
2 ответа
Первый запрос - это объединение 4 таблиц, второй - объединение 3 таблиц. Поэтому я не ожидаю, что оба запроса вернут одинаковое количество строк.
SELECT *
FROM table1 t1
JOIN table2 t2 ON
(t1.col_a = t2.col_a)
JOIN table3 t3 ON
(t2.col_b = t3.col_b)
JOIN table3 t3 ON
(t3.col_c = t1.col_c);
Псевдоним t3 используется только в предложении ON. Псевдоним t3 ссылается на таблицу перед ключевым словом ON. Я выяснил это, экспериментируя. Таким образом, предыдущий запрос эквивалентен
SELECT *
FROM table1 t1
JOIN table2 t2 ON
(t1.col_a = t2.col_a)
JOIN table3 t3 ON
(t2.col_b = t3.col_b)
JOIN table3 t4 ON
(t4.col_c = t1.col_c);
и это может быть преобразовано в традиционное соединение
SELECT *
FROM table1 t1,
table2 t2,
table3 t3,
table3 t4
where (t1.col_a = t2.col_a)
and (t2.col_b = t3.col_b)
and (t4.col_c = t1.col_c);
Второй запрос
SELECT *
FROM table1 t1
JOIN table2 t2 ON
(t1.col_a = t2.col_a)
JOIN table3 t3 ON
(t2.col_b = t3.col_b AND
t3.col_c = t1.col_c);
Это также может превратиться в традиционное соединение
SELECT *
FROM table1 t1,
table2 t2,
table3 t3
where (t1.col_a = t2.col_a)
and (t2.col_b = t3.col_b)
AND (t3.col_c = t1.col_c);
Эти запросы кажутся разными. Для доказательства их различия мы используем следующий пример:
create table table1(
col_a number,
col_c number
);
create table table2(
col_a number,
col_b number
);
create table table3(
col_b number,
col_c number
);
insert into table1(col_a, col_c) values(1,3);
insert into table1(col_a, col_c) values(4,3);
insert into table2(col_a, col_b) values(1,2);
insert into table2(col_a, col_b) values(4,2);
insert into table3(col_b, col_c) values(2,3);
insert into table3(col_b, col_c) values(2,5);
insert into table3(col_b, col_c) values(7,9);
commit;
Мы получаем следующий вывод
SELECT *
FROM table1 t1
JOIN table2 t2 ON
(t1.col_a = t2.col_a)
JOIN table3 t3 ON
(t2.col_b = t3.col_b)
JOIN table3 t3 ON
(t3.col_c = t1.col_c)
| COL_A | COL_C | COL_A | COL_B | COL_B | COL_C | COL_B | COL_C |
|-------|-------|-------|-------|-------|-------|-------|-------|
| 1 | 3 | 1 | 2 | 2 | 3 | 2 | 3 |
| 4 | 3 | 4 | 2 | 2 | 3 | 2 | 3 |
| 1 | 3 | 1 | 2 | 2 | 5 | 2 | 3 |
| 4 | 3 | 4 | 2 | 2 | 5 | 2 | 3 |
SELECT *
FROM table1 t1
JOIN table2 t2 ON
(t1.col_a = t2.col_a)
JOIN table3 t3 ON
(t2.col_b = t3.col_b AND
t3.col_c = t1.col_c)
| COL_A | COL_C | COL_A | COL_B | COL_B | COL_C |
|-------|-------|-------|-------|-------|-------|
| 4 | 3 | 4 | 2 | 2 | 3 |
| 1 | 3 | 1 | 2 | 2 | 3 |
Количество извлеченных строк отличается и поэтому count(*) это отличается.
Использование псевдонимов было удивительным. по крайней мере для меня.
Следующий запрос работает, потому что t1 в where_clause Рекомендации table2,
select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_b<0;
Следующий запрос работает, потому что t1 в where_clause Рекомендации table1,
select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_c<0;
Следующий запрос вызывает ошибку, потому что оба table1 а также table2 содержать столбец col_a,
select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_a<0;
Выдается ошибка
ORA-00918: column ambiguously defined
Следующий запрос работает, псевдоним t1 относится к двум разным таблицам в одном where_clause,
select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_b<0 and t1.col_c<0;
Эти и другие примеры можно найти здесь: http://sqlfiddle.com/
Самый маленький встречный пример
Наименьший встречный пример
table1
col_a col_c
1 2
table2
col_a col_b
1 3
table3
col_b col_c
3 5
6 2
Здесь второй запрос имеет пустой набор результатов, а первый запрос возвращает одну строку. Можно показать, что count(*) второго запроса никогда не превышает count(*) первого запроса.
Более подробное объяснение
Такое поведение станет более понятным, если мы детально проанализируем следующее утверждение.
SELECT t.col_b, t.col_c
FROM table1 t
JOIN table2 t ON
(t.col_b = t.col_c) ;
Ниже приведен сокращенный синтаксис этого запроса в форме Бэкуса-Наура, полученный из описаний синтаксиса в справочнике по языку SQL Oracle 12.2. Обратите внимание, что под каждой синтаксической диаграммой есть ссылка на форму Бэкуса-Наура этой диаграммы, например, Описание иллюстрации select.eps. "Сокращенный" означает, что я упустил все возможности, которые не использовались, например,g. select определяется как
select::=subquery [ for_update_clause ] ;
Наш запрос не использует необязательный for_update_clause поэтому я уменьшил правило до
select::=subquery
Единственное исключение является необязательным where-clause, Я не удалил его, чтобы использовать эти сокращенные правила для анализа вышеуказанного запроса, даже если мы добавим where_clause,
Эти сокращенные правила будут определять только подмножество всех возможных операторов выбора.
select::=subquery
subquery::=query_block
query_block::=SELECT select_list FROM join_clause [ where_clause ]
join_clause::=table_reference inner_cross_join_clause ...
table_reference::=query_table_expression t_alias query_table_expression::=table
inner_cross_join_clause::=JOIN table_reference ON condition
Таким образом, наше утверждение является query_block и join_clause имеет тип
table_reference inner_cross_join_clause
где table_reference является table1 t а также inner_cross_join_clause является JOIN table2 t ON (t.col_b = t.col_c), Многоточие ... означает, что могут быть дополнительные inner_cross_join_clauses, но нам это здесь не нужно.
в inner_cross_join_clause псевдоним t относится к table2, Только если эти ссылки не могут быть удовлетворены, псевдоним следует искать во внешней области видимости. Таким образом, все следующие выражения в условии ON являются действительными:
t.col_b = t.col_c
Вот t.col_b является table2.col_b так как t относится к псевдониму его inner_cross_join_clause, t.col_c является table1.col_c, t из inner_cross_join_clause (Ссылаясь на table2) не имеет столбца col_c поэтому будет произведен поиск внешней области и найден соответствующий псевдоним.
Если у нас есть пункт
t.col_a = t.col_a
псевдоним можно найти как псевдоним, определенный в inner_cross_join_clause к которому это ON- condition принадлежит так t будет решен в table2,
если список выбора состоит из
t.col_c, t.col_b, t.col_a
вместо * тогда join_clause будет искать псевдоним и t.col_c будет решен в table1.col_c (table2 не содержит столбца col_c), t.col_b будет решен в table2.col_b (table1 не содержит col_b) но t.col_a поднимет ошибку
ORA-00918: column ambiguously defined
потому что для select_list ни одно из определений aias не имеет предшествующего значения перед другим. Если наш запрос также имеет where_clause тогда псевдонимы разрешаются так же, как если бы они использовались в select_list,
Чем больше данных, тем больше результатов. Ваш запрос коллеги такой же, как этот.
select * from table3 where t3.col_b = 'XX'
union
select * from table3 where t3.col_c = 'YY'
или же
select * from table3 where t3.col_b = 'XX' or t3.col_c = 'YY'
пока ваш запрос такой.
select * from table3 where t3.col_b ='XX' and t3.col_c='YY'
Первый - это данные, где (xx или yy), а второй - данные, где (xx и yy)