В чем разница между «using» и «on» в соединениях таблиц в MySQL?
Это
... T1 join T2 using(ID) where T2.VALUE=42 ...
такой же как
... T1 join T2 on(T1.ID=T2.ID) where T2.VALUE=42 ...
для всех типов соединений?
Насколько я понимаю, using(ID) - это просто сокращение от on(T1.ID=T2.ID). Это правда?
Теперь еще один вопрос:
Это то же самое, что и
... T1 join T2 on(T1.ID=T2.ID and T2.VALUE=42) ...
Я не думаю, что это правда, но почему? Как условия в предложении on взаимодействуют с объединением vs, если оно находится в предложении where?
Ответы 7
Ваша интерпретация кажется правильной. эта статья может помочь.
Что касается второго вопроса, я не понимаю, почему результат вашего третьего примера должен отличаться от результата первых двух. Любое условие в предложении «ON» имеет такое же значение, как если бы оно было в предложении «WHERE».
Нет, в зависимости от типа соединения условия в предложении ON могут иметь совсем другое значение, чем предложение where.
Разве единственное слово «соединение», как в примерах, не является синонимом «внутреннего соединения»?
Я считаю, что вы правы - USING (xx) - это сокращение для объединения двух столбцов с одинаковыми именами.
Что касается второго вопроса, оба запроса мог одинаковы или могут отличаться в зависимости от реализации планировщика запросов, специфичной для базы данных. Чтобы выяснить это для себя (по крайней мере, в postgres), выполните EXPLAIN SELECT ..., чтобы увидеть, как будут выполняться планы запросов.
Если есть только одно соединение, разницы нет.
Обратной стороной предложения using является то, что обе таблицы должны иметь одинаковое имя столбца.
Я не использую синтаксис USING, так как
- большинство моих объединений не подходят для него (не то же имя поля, которое сопоставляется, и / или несколько совпадений в объединении) и
- не сразу очевидно, что это переводится в случае с более чем двумя таблицами
т.е. если предположить, что 3 таблицы со столбцами 'id' и 'id_2',
T1 JOIN T2 USING(id) JOIN T3 USING(id_2)
становиться
T1 JOIN T2 ON(T1.id=T2.id) JOIN T3 ON(T1.id_2=T3.id_2 AND T2.id_2=T3.id_2)
или же
T1 JOIN T2 ON(T1.id=T2.id) JOIN T3 ON(T2.id_2=T3.id_2)
или еще что-то?
Найти это для конкретной версии базы данных - довольно тривиальное упражнение, но у меня нет большой уверенности в том, что это согласовано для всех баз данных, и я не единственный человек, который должен поддерживать мой код (так что другие люди также должны будут знать, что это эквивалентно).
Очевидная разница с WHERE vs ON заключается в том, что соединение является внешним:
Предполагая, что T1 с одним полем ID, одна строка содержит значение 1, и T2 с полем ID и VALUE (одна строка, ID = 1, VALUE = 6), тогда мы получаем:
SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID) WHERE T2.VALUE=42
не дает строк, поскольку требуется соответствие WHERE, тогда как
SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID AND T2.VALUE=42)
даст одну строку со значениями
1, NULL, NULL
поскольку ON требуется только для сопоставления соединения, которое не является обязательным из-за внешнего.
Предложение USING является сокращением для равномерного объединения столбцов, предполагая, что столбцы существуют в обеих таблицах с одним и тем же именем:
A JOIN B USING (column1)
A JOIN B ON A.column1=B.column1
Вы также можете назвать несколько столбцов, что упрощает объединение составных ключей. Следующие соединения должны быть эквивалентными:
A JOIN B USING (column1, column2)
A JOIN B ON A.column1=B.column1 AND A.column2=B.column2
Обратите внимание, что USING (<columnlist>) должен иметь круглые скобки, тогда как ON <expr> не обязательно должен иметь круглые скобки (хотя скобки могут использоваться вокруг <expr>, только они могут быть включены вокруг выражения в любом другом контексте).
Кроме того, никакие другие таблицы, объединенные в запрос, не могут иметь столбец с таким именем, иначе запрос будет неоднозначным, и вы должны получить ошибку.
Что касается вашего вопроса о дополнительных условиях, предполагая, что вы используете INNER JOIN, он должен логически давать тот же результат запроса, но план оптимизации может быть затронут в зависимости от реализации СУБД. Кроме того, OUTER JOIN дает другой результат, если вы включаете условия в объединение по сравнению с предложением WHERE.
Как вы думаете, ON является преимуществом или особенностью языка?
Конечно. Вам нужно ON, если у вас есть какое-либо выражение соединения, кроме двух равных столбцов. Я предпочитаю использовать синтаксис SQL-92 вместо того, чтобы помещать условия соединения в предложение WHERE.
Здесь у вас есть ответы, мне не нужно что-то добавлять. Однажды я провел тест производительности на этом, и ИСПОЛЬЗОВАНИЕ постоянно и всегда работало быстрее, чем ВКЛ. Да, я говорю о 10-20 мсах :) MySQL, о которой я говорю
Есть разница в результате, о которой я не упоминаю в других ответах. Если вы сделаете это:
JOIN ... ON t1.common = t2.common
тогда в результирующем наборе будут два столбца с именем common, в частности t1.common и t2.common, и попытка сослаться на неквалифицированное имя common приведет к тому, что запрос будет отклонен как неоднозначный (даже если оба столбца обязательно содержат одно и то же значение).
Если, с другой стороны, вы сделаете это:
JOIN ... USING (common)
тогда в наборе результатов будет только один столбец с именем common, и это будет неполное имя - ни t1.common, ни t2.common не будут присутствовать.
Просто ради интереса, какую базу данных вы используете? Я просто попробовал синтаксис USING на SQL Server, и он, похоже, не работает.