Когда нужно писать имя таблицы в запросе?

Question

[Евгений Иванов]

Вот простой пример объединения таблиц persons и positions
Рабочий запрос

SELECT id_person, name, id_pos, title 
FROM persons
INNER JOIN positions ON id_pos = position_ref

Но также мы можем написать и такой запрос

SELECT id_person, name, id_pos, title 
FROM persons
INNER JOIN positions ON positions.id_pos = position_ref

Когда нужно писать имя таблицы в запросе и с чем это связано?

PS
Некоторые запросы join (LEFT OUTER JOIN ??) у меня не работали без имени таблицы.

Answer 1

[galaxy]

Если в разных таблицах имеются имеются колонки с одинаковыми названиями.
Иногда SQL это терпит (например, можно написать SELECT * FROM table1 CROSS JOIN table2, даже если в таблицах есть такие колонки), но чаще нет. В предикате JOIN ON или в WHERE нужно знать, о колонке какой таблицы идет речь.

Answer 2

[Akina]

Каждый сам творец своего геморроя.

Настоятельная рекомендация. Если источником записей запроса (всего, целиком) является только одна таблица - имена полей можно не указывать. Во всех остальных случаях - указывайте алиас таблицы для абсолютно каждого поля (включая имена обобщённых полей в USING и при NATURAL JOIN), кроме имён полей выходного набора, используемых в пост-группировочных выражениях. Это позволит гарантировать отсутствие неоднозначностей.

Под понятием "таблица" следует понимать строго один экземпляр исходного набора данных. Иными словами, запрос, в котором используется две копии одной таблицы, либо используется дополнительная синтетическая таблица констант, запросом с источником данных из одной таблицы не является.

---

В некоторых диалектах указание алиаса может быть обязательно и по причине интерференции имён из разных пространств.

Например, в хранимых объектах MySQL при совпадении имени поля таблицы с именем локальной переменной обращение без указания алиаса - это всегда обращение к переменной. Поэтому для MySQL правило ещё строже - алиас указывай всегда.

И даже такая строгость порой недостаточна. Пример. Обратите внимание на сортировку - в ней id интерпретируется как имя переменной, а не имя поля выходного набора, и потому результат "странный". Кстати, это тот случай, когда вообще ничто не поможет, кроме использования выражения выходного поля вместо его имени.

Answer 3

[Евгений Иванов]

galaxy, вот ещё пример
(вспомнил где не работало)

У каждой таблицы есть колонка с именем id. Остальные имена колонок разные.
Это работает

SELECT ProductCode, Name FROM table_one 
LEFT OUTER JOIN table_two 
ON  table_two.id = table_one.id_client;

Это нет

SELECT ProductCode, Name FROM table_one 
LEFT OUTER JOIN table_two 
ON  id = table_one.id_client;

[Err] 1052 - Column 'id' in on clause is ambiguous

Достаточно просто совпадения имен любых колонок в структуре таблицы ()?
То есть смотрите - в ON сравнивается id и id_client. Имена разные.
Понятно что и с чем связывается.
И даже если ON id = id ведь понятно, что нужно сравнить id из первой таблицы со id из второй.
У нас 2 таблицы. И у каждой id. Больше не с чем сравнивать. Два одинаковых поля в таблице быть не может.

Comments

[galaxy]

понятно, что нужно сравнить id из первой таблицы со id из второй

вам понятно, а SQL непонятно

В JOIN ON (после, собственно, ON) есть предикат - логическое условие, которое (грубо говоря) вычисляется после декартова произведения таблиц (т.е. присоединения каждой строки первой таблицы к каждой строке второй) для каждой объдиненной строки. В результате останутся только объединенные строки, для которых условие дает TRUE. Заметьте, тут нет никаких ограничений на вид этого условия - любое выражение на колонках из двух таблиц, дающее результат булева типа.
Т.е. ничто не мешает написать FROM a JOIN b ON a.id = a.id (т.е. фактически JOIN ON TRUE - будет CROSS JOIN). В данном случае это выглядит надуманно, и кажется, что в вашем случае (JOIN ON id = id) СУБД могла бы быть и поумнее и догадаться, что вы хотите, но в более сложных условиях в JOIN и/или при соединении 3, 4 и более таблиц думать за вас становится все сложнее:

FROM a
JOIN b ON id = parent_id
JOIN c ON id = parent_id

допустим, во всех трех таблицах есть колонки id, а в b и c есть parent_id - что с чем тут соединять?

[Евгений Иванов]

galaxy, понятно. В общем всегда писать имя таблицы.поле_таблицы,.

[Akina]

Реальный случай из практики.

После очередного обновления одной программы в системе из нескольких связных программных комплексов легла насмерть другая программа. Поиск проблемы - это прыготня трёх человек в течение почти часа... и потом ещё два дня на лечение ситуации путём обновления локальных клиентов. Причиной было именно не-указание алиаса в одном из запросов, причём пропущен он был явно по невнимательности либо ошибке - правило "всегда указывай алиас" в общем соблюдалось... а в обновлении была изменена структура одной из таблиц, в неё было добавлено поле, одноимённое с полем другой таблицы, что и привело к неоднозначности. Если бы у поля был указан алиас - проблемы бы не возникло даже в теории.

Answer 4

[alexalexes]

Всегда обзывайте каждую таблицу в запросе кратким именем, и это краткое имя используйте в именовании колонок, тогда ни при каких условиях проблем с конфликтом имен не возникнет.

SELECT t1.ProductCode, t2.Name
FROM table_one as t1 -- t1 - краткий псевдоним таблицы table_one в пределах select-а
LEFT OUTER JOIN table_two as t2 -- t2 - краткий псевдоним таблицы table_two в пределах select-а
ON  t2.id = t1.id_client;

Comments

[Akina]

Крайне неудачный пример. Во многих диалектах To является зарезервированным словом, и использование его как алиаса приведёт к проблемам. t1 и t2 были бы гораздо удачнее.

[alexalexes]

Исправил. Я, обычно, беру пару букв от слов названия таблицы.
Если возникнет наложение на зарезервированное слово, то тут уж нужно включать соображалку и обходить этот момент.