Форум пользователей MySQL

FiMko · 23.09.2010 17:41:13

Ребята, я попытался собрать информацию о различного рода JOIN'ах в сети и представить ее в виде сравнительной таблицы. Вы, как специалисты, не могли бы проверить правильность заполнения таблицы. Любые уточнения, дополнения принимаются!

Внимание: Приведенные данные актуальны только для MySQL и скорее всего будут отличаться для других СУБД!

Полезные ссылки:

1. Join (SQL). Wikipedia.
2. JOIN Syntax. dev.mysql.com.
2. A Visual Explanation of SQL Joins. codinghorror.com.
3. Объяснение SQL объединений JOIN/INNER/OUTER. skillz.ru.
4. Операции LEFT JOIN и RIGHT JOIN. vingrad.ru.

Отредактированно FiMko (23.09.2010 17:45:54)

Прикрепленные файлы:

joins.jpg, Размер: 147,368 байт, Скачано: 72,731

rgbeast · 23.09.2010 17:54:03

INNER JOIN = JOIN и не требует отдельной строчки в таблице.
Названия колонок не очень понятны. В таблице нарушена симметрия LEFT и RIGHT JOIN. Если поменять местами первую и вторую таблицу описание LEFT JOIN должно превратиться в описание RIGHT JOIN

FiMko · 23.09.2010 18:06:18

rgbeast написал:
INNER JOIN = JOIN и не требует отдельной строчки в таблице.
Названия колонок не очень понятны. В таблице нарушена симметрия LEFT и RIGHT JOIN. Если поменять местами первую и вторую таблицу описание LEFT JOIN должно превратиться в описание RIGHT JOIN

Чтобы было удобней меня редактировать переходим на текстовый вариант:

JOIN

ВЫПОЛНЕНИЕ УСЛОВИЯ СОЕДИНЕНИЯ: К ячейкам из первой (левой) таблицы присоединяются ячейки второй (правой). Записи из первой таблицы могут повторяться если им соответствует несколько записей во второй таблице.

НЕВЫПОЛНЕНИЕ УСЛОВИЯ СОЕДИНЕНИЯ: Строка игнорируется (не будет ячеек даже из первой таблицы).

ОТСУТСТВИЕ УСЛОВИЯ СОЕДИНЕНИЯ: Будет возвращено декартово произведение, в котором каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы. То есть будут получены все возможные сочетания из обеих таблиц.

LEFT JOIN

ВЫПОЛНЕНИЕ УСЛОВИЯ СОЕДИНЕНИЯ: К ячейкам из первой (левой) таблицы присоединяются ячейки второй (правой).

НЕВЫПОЛНЕНИЕ УСЛОВИЯ СОЕДИНЕНИЯ: Поля правой таблицы заполняются значениями NULL.

ОТСУТСТВИЕ УСЛОВИЯ СОЕДИНЕНИЯ: Невозможно.

RIGHT JOIN

ВЫПОЛНЕНИЕ УСЛОВИЯ СОЕДИНЕНИЯ: К ячейкам из второй (правой) таблицы присоединяются ячейки первой (левой).

НЕВЫПОЛНЕНИЕ УСЛОВИЯ СОЕДИНЕНИЯ: Поля левой таблицы заполняются значениями NULL.

ОТСУТСТВИЕ УСЛОВИЯ СОЕДИНЕНИЯ: Невозможно.

INNER JOIN

То же самое, что JOIN.

CROSS JOIN

То же самое, что JOIN.

Отредактированно FiMko (23.09.2010 18:14:15)

paulus · 23.09.2010 22:35:23

Мне не нравятся заявления «невозможно». Это по крайней мере неправда:

Код:

mysql> select * from a left join b on 1=1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    2 |
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
+------+------+
6 rows in set (0.00 sec)

Смысла в этом выражении, конечно, немного, но его немного и в случае с INNER JOIN

Учитывая, что RIGHT JOIN — это LEFT JOIN с операндами, расставленными в обратном
порядке, его тоже расписывать бесполезно.

Кажется, в этой таблице было бы куда полезнее написать, как из этих примитивов
собрать, например, FULL OUTER JOIN

FiMko · 24.09.2010 18:07:55

paulus написал:
Мне не нравятся заявления «невозможно». Это по крайней мере неправда:
Код:
mysql> select * from a left join b on 1=1;

И указали условие ну да ладно сути в этом запросе действительно нет.
Кстати, спецификация MySQL также не предусматривает использование LEFT JOIN без join_condition:

http://dev.mysql.com/doc/refman/5.0/en/join.html написал:
table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition

paulus написал:
Кажется, в этой таблице было бы куда полезнее написать, как из этих примитивов
собрать, например, FULL OUTER JOIN

Скорей всего не буду рассматривать, т.к. синтаксис FULL [OUTER] JOIN в прямом виде в MySQL [пока] не поддерживается. Его эмулируют, используя UNION. Но так как целью было описать и разобраться только с существующими в рамках MySQL JOIN'ами, то не буду пока запутывать себя и последователей Аналогично для UNION JOIN.

Вопрос 1
Исторический [любознательный] вопрос: Известно, INNER JOIN - полный аналог JOIN. Так исторически сложилось, что JOIN был введен просто как alias для INNER JOIN для краткости записи?

Вопрос 2

http://dev.mysql.com/doc/refman/5.0/en/join.html написал:
The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

mysql> select * from sport_section;

+----+------+

| id | name |

+----+------+

|  1 | Jack |

|  2 | Emmy |

|  3 | Mike |

+----+------+

mysql> select * from music_section;

+----+-------+

| id | name  |

+----+-------+

|  1 | Mike  |

|  2 | Sam   |

|  3 | Garry |

+----+-------+

mysql> select * from sport_section natural left outer join music_section;

+----+------+

| id | name |

+----+------+

|  1 | Jack |

|  2 | Emmy |

|  3 | Mike |

+----+------+

mysql> select * from music_section left JOIN sport_section using(id,name);

+----+-------+

| id | name  |

+----+-------+

|  1 | Mike  |

|  2 | Sam   |

|  3 | Garry |

+----+-------+

mysql> select * from music_section INNER JOIN sport_section using(id,name);

Empty set (0.00 sec)

Ну и где же они эквиваленты

?

Вопрос 3

Правда ли, что LEFT JOIN (и вообще JOIN'ы) работают медленнее чем вложенные селекты?

Отредактированно FiMko (24.09.2010 22:17:36)

paulus · 26.09.2010 19:53:29

1. Да.
2. Попробуйте явно выбирать названиетаблицы.name — там просто
выбираются разные таблицы
3. Обычно верно обратное — вложенные запросы работают не быст-
рее объединений, но пишутся, как правило, проще (интуитивнее).

FiMko · 26.09.2010 21:20:24

paulus написал:
2. Попробуйте явно выбирать названиетаблицы.name — там просто
выбираются разные таблицы

Не очень понял.. понял, что спутал местами таблицы sport_section и music_section.
Да, вижу, LEFT JOIN работает так же:

mysql> select * from sport_section left JOIN music_section using(id,name);

+----+------+

| id | name |

+----+------+

|  1 | Jack |

|  2 | Emmy |

|  3 | Mike |

+----+------+

Но INNER JOIN дает все же пустой набор:

mysql> select * from sport_section INNER JOIN music_section using(id,name);

Empty set (0.00 sec)

mysql> select sport_section.name from sport_section INNER JOIN music_section using(id,name);

Empty set (0.00 sec)

Отредактированно FiMko (26.09.2010 21:24:37)

paulus · 26.09.2010 22:52:33

А что Вас удивляет? Мой любимый пример — с корзинами яблок.
У Вас их две. В первой корзине у Вас зеленое яблоко с хвостиком
и красное без хвостика. Во второй — зеленое без хвостика и крас-
ное с хвостиком. И Ваша задача достать из корзин одинаковые
яблоки (сравните с внешним объединением: достать из первой кор-
зины яблоко, а потом из второй такое же, если найдется).

FiMko · 26.09.2010 23:43:07

paulus написал:
А что Вас удивляет? Мой любимый пример — с корзинами яблок.
У Вас их две. В первой корзине у Вас зеленое яблоко с хвостиком
и красное без хвостика. Во второй — зеленое без хвостика и крас-
ное с хвостиком. И Ваша задача достать из корзин одинаковые
яблоки (сравните с внешним объединением: достать из первой кор-
зины яблоко, а потом из второй такое же, если найдется).

Нее, ну к работе INNER JOIN'а претензий нет, работает как положено, но:

http://dev.mysql.com/doc/refman/5.0/en/join.html написал:
The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

Вот этого я не вижу...

Отредактированно FiMko (26.09.2010 23:43:35)

paulus · 27.09.2010 10:23:17

А что Вас смущает то?

FiMko · 27.09.2010 10:45:56

paulus написал:
А что Вас смущает то?

http://dev.mysql.com: NATURAL [LEFT] JOIN для двух таблиц является семантически эквивалентом INNER JOIN или LEFT JOIN с оператором USING, который перечисляет все поля, присутствующие в обеих таблицах.

mysql> select * from sport_section natural left outer join music_section;

+----+------+

| id | name |

+----+------+

|  1 | Jack |

|  2 | Emmy |

|  3 | Mike |

+----+------+

mysql> select * from sport_section left JOIN music_section using(id,name);

+----+------+

| id | name |

+----+------+

|  1 | Jack |

|  2 | Emmy |

|  3 | Mike |

+----+------+

mysql> select * from sport_section INNER JOIN music_section using(id,name);

Empty set (0.00 sec)

mysql> select sport_section.name from sport_section INNER JOIN music_section using(id,name);

Empty set (0.00 sec)

Видно, что INNER JOIN, являясь со слов http://dev.mysql.com семантически эквивалентом natural left join, возвращает другой результат. Не могли бы Вы привести пример, когда INNER JOIN с использованием USING действительно будет эквивалентом и вернет тот же результат, что NATURAL JOIN?

Отредактированно FiMko (27.09.2010 11:41:07)

paulus · 27.09.2010 12:16:27

Из Ваших примеров видно, что NATURAL LEFT JOIN ~ LEFT JOIN.
Осталось проверить, что NATURAL JOIN ~ JOIN. Т.е. убедиться,
что он выдает пустую выборку. Справитесь?

FiMko · 27.09.2010 14:37:54

paulus написал:
Из Ваших примеров видно, что NATURAL LEFT JOIN ~ LEFT JOIN.
Осталось проверить, что NATURAL JOIN ~ JOIN. Т.е. убедиться,
что он выдает пустую выборку. Справитесь?

всё понял. Я невнимательно посмотрел синтаксис для NATURAL JOIN и думал, что LEFT/RIGHT являются обязательными параметрами, а не опциональными. Спасибо!

http://dev.mysql.com написал:
table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

mysql> select * from music natural join sport;

Empty set (0.00 sec)

FiMko · 01.10.2010 12:23:25

В результате обсуждения JOIN'ов здесь я попытался в своем блоге собрать описание всех видов Join в MySQL. Здесь я привожу полученный результат на ваш строгий, но справедливый суд Ниже приведен синтаксис всех возможных форматов записи оператора JOIN при использовании в командах SELECT (см. http://dev.mysql.com/doc/refman/5.0/en/join.html). Все примеры и описания рассмотрены на примере MySQL и вероятнее всего будут отличаться для других типов СУБД!

1. table_reference [INNER] JOIN table_reference
2. table_reference [CROSS] JOIN table_reference
3. table_reference STRAIGHT_JOIN table_reference
4. table_reference [INNER] JOIN table_reference join_condition
5. table_reference [CROSS] JOIN table_reference join_condition
6. table_reference STRAIGHT_JOIN table_reference ON conditional_expr
7. table_reference LEFT [OUTER] JOIN table_reference join_condition
8. table_reference RIGHT [OUTER] JOIN table_reference join_condition
9. table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
10. table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

При описании различных вариантов использования JOIN будут использоваться две тестовые таблицы – sport и music. Первая содержит список людей, посещающих спортивные секции, вторая для людей, посещающих занятия по музыке.

sport

+----+------+

| id | name |

+----+------+

|  1 | Jack |

|  2 | Emmy |

|  3 | Mike |

+----+------+

music

+----+-------+

| id | name  |

+----+-------+

|  1 | Mike  |

|  2 | Sam   |

|  3 | Garry |

+----+-------+

1. table_reference [INNER] JOIN table_reference

Тип соединения "внутреннее". Внутренний тип соединения используется по умолчанию, когда тип явно не задан (то есть, опущено ключевое слово INNER). Если не приведено ни одного условия совпадения, то будет возвращено декартово произведение, в котором каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы. То есть будут получены всевозможные сочетания из обеих таблиц. Такое соединение в разделе FROM может быть заменено списком таблиц через запятую.

select * from sport inner join music;

эквивалентно:

select * from sport, music;

результат:

sport       music

+----+------+----+-------+

| id | name | id | name  |

+----+------+----+-------+

|  1 | Jack |  1 | Mike  |

|  2 | Emmy |  1 | Mike  |

|  3 | Mike |  1 | Mike  |

|  1 | Jack |  2 | Sam   |

|  2 | Emmy |  2 | Sam   |

|  3 | Mike |  2 | Sam   |

|  1 | Jack |  3 | Garry |

|  2 | Emmy |  3 | Garry |

|  3 | Mike |  3 | Garry |

+----+------+----+-------+

Покажите мне всевозможные сочетания пар, как если бы все ребята занимались И музыкой И спортом.
В данном случае, например, спортсмен Джек (Jack) ходил бы на занятия музыкой вместе с Майклом (Mike), Сэмом (Sam) и с Гарри (Garry). И аналогично для остальных ребят.

2. table_reference [CROSS] JOIN table_reference

Абсолютно то же самое, что table_reference [INNER] JOIN table_reference.

select * from sport CROSS JOIN music;

результат:

sport        music

+----+------+----+-------+

| id | name | id | name  |

+----+------+----+-------+

|  1 | Jack |  1 | Mike  |

|  2 | Emmy |  1 | Mike  |

|  3 | Mike |  1 | Mike  |

|  1 | Jack |  2 | Sam   |

|  2 | Emmy |  2 | Sam   |

|  3 | Mike |  2 | Sam   |

|  1 | Jack |  3 | Garry |

|  2 | Emmy |  3 | Garry |

|  3 | Mike |  3 | Garry |

+----+------+----+-------+

3. table_reference STRAIGHT_JOIN table_reference

То же самое, что table_reference [INNER] JOIN table_reference за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор соединения таблиц в MySQL располагает таблицы в неправильном порядке. То есть специальный оператор, по сути, заставляет соединять таблицы в том порядке, в котором это указано в SQL-запросе. В некоторых случаях это может позволить оптимизировать скорость выполнения запроса.

SELECT * FROM sport STRAIGHT_JOIN music;

результат:

sport        music

+----+------+----+-------+

| id | name | id | name  |

+----+------+----+-------+

|  1 | Jack |  1 | Mike  |

|  2 | Emmy |  1 | Mike  |

|  3 | Mike |  1 | Mike  |

|  1 | Jack |  2 | Sam   |

|  2 | Emmy |  2 | Sam   |

|  3 | Mike |  2 | Sam   |

|  1 | Jack |  3 | Garry |

|  2 | Emmy |  3 | Garry |

|  3 | Mike |  3 | Garry |

+----+------+----+-------+

Покажите мне всевозможные сочетания пар, как если бы все ребята занимались И музыкой И спортом И соедините для меня данные именно в том порядке, как я прошу - сначала реестр спортсменов (таблица sport), а рядом реестр музыкантов!
И снова, как и для table_reference [INNER] JOIN table_reference спортсмен Джек (Jack) ходит на занятия музыкой вместе с Майклом (Mike), Сэмом (Sam) и с Гарри (Garry). И аналогично для всех остальных.

4. table_reference [INNER] JOIN table_reference join_condition

INNER JOIN – внутреннее соединение. Объединяет две таблицы, где каждая строка обеих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор. Тип соединения "внутреннее". Внутренний тип соединения используется по умолчанию, когда тип явно не задан (т.е. если вы напишите просто JOIN).

select * from sport inner join music on sport.name=music.name;

эквивалентно:

select * from sport, music where sport.name=music.name;

результат:

sport        music

+----+------+----+------+

| id | name | id | name |

+----+------+----+------+

|  3 | Mike |  1 | Mike |

+----+------+----+------+

Покажите мне всех ребят, которые ходят И на занятия музыкой И на занятия спортом.
Из таких ребят оказался только один – Майкл (Mike). Только он присутствует и в таблице music и в таблице sport.

5. table_reference [CROSS] JOIN table_reference join_condition

Абсолютно то же самое, что table_reference [INNER] JOIN table_reference join_condition.

select * from sport cross join music on sport.name=music.name;

результат:

sport       music

+----+------+----+------+

| id | name | id | name |

+----+------+----+------+

|  3 | Mike |  1 | Mike |

+----+------+----+------+

6. table_reference STRAIGHT_JOIN table_reference ON conditional_expr

То же самое, что table_reference table_reference [INNER] JOIN table_reference join_condition за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор соединения таблиц в MySQL располагает таблицы в неправильном порядке. То есть специальный оператор STRAIGHT_JOIN, по сути, заставляет соединять таблицы в том порядке, в котором это указано в SQL-запросе. В некоторых случаях это может позволить оптимизировать скорость выполнения запроса.

select * from sport STRAIGHT_JOIN music on sport.name=music.name;

результат:

sport        music

+----+------+----+------+

| id | name | id | name |

+----+------+----+------+

|  3 | Mike |  1 | Mike |

+----+------+----+------+

Покажите мне всех ребят, которые ходят И на занятия музыкой И на занятия спортом и соедините для меня данные именно в том порядке, как я прошу - сначала список спортсменов (таблица sport), а рядом список музыкантов!
Снова, как и в примере для INNER JOIN, из таких ребят оказался только один – Майкл (Mike).

7. table_reference LEFT [OUTER] JOIN table_reference join_condition

LEFT (OUTER) - Тип соединения "левое (внешнее)". Левое соединение таблиц А и В включает в себя все строки из левой таблицы А и те строки из правой таблицы В, для которых обнаружено совпадение. Для строк из таблицы А, для которых не найдено соответствия в таблице В, в столбцы, извлекаемые из таблицы В, заносятся значения NULL.

select * from sport left outer join music on sport.name=music.name;

результат:

sport        music

+----+------+------+------+

| id | name | id   | name |

+----+------+------+------+

|  1 | Jack | NULL | NULL |

|  2 | Emmy | NULL | NULL |

|  3 | Mike |    1 | Mike |

+----+------+------+------+

Покажите мне всех спортсменов, которые ходят И на занятия спортом И на занятия музыкой, а тех, что занимаются только спортом и не занимаются музыкой пометьте в разделе music как NULL.

8. table_reference RIGHT [OUTER] JOIN table_reference join_condition

Тип соединения "правое (внешнее)". Правое соединение таблиц А и В включает в себя все строки из правой таблицы В и те строки из левой таблицы А, для которых обнаружено совпадение. Для строк из таблицы В, для которых не найдено соответствия в таблице А, в столбцы, извлекаемые из таблицы А заносятся значения NULL.

select * from sport right outer join music on sport.name=music.name;

результат:

sport          music

+------+------+----+-------+

| id   | name | id | name  |

+------+------+----+-------+

|    3 | Mike |  1 | Mike  |

| NULL | NULL |  2 | Sam   |

| NULL | NULL |  3 | Garry |

+------+------+----+-------+

Покажите мне всех музыкантов, которые ходят И на занятия музыкой И на спортом, а тех, что занимаются только музыкой и не занимаются спортом пометьте в разделе sport как NULL.

9. table_reference NATURAL [LEFT [OUTER]] JOIN table_reference

Естественное соединение производится по всем столбцам таблиц А и В, имеющим одинаковые имена. В результирующую таблицу одинаковые столбцы вставляются только один раз. Выражение NATURAL [LEFT] JOIN для двух таблиц является эквивалентом INNER JOIN или LEFT JOIN с выражением USING, в котором указаны все столбцы, имеющиеся в обеих таблицах.

select * from sport natural left outer join music;

эквивалентно:

select * from sport left JOIN music using(id,name)

результат:

sport

+----+------+

| id | name |

+----+------+

|  1 | Jack |

|  2 | Emmy |

|  3 | Mike |

+----+------+

Дайте мне данные всех колонок из реестра спортсменов (именно спортсменов!), для которых в реестре музыкантов есть колонки с такими же названиями.

10. table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Естественное соединение производится по всем столбцам таблиц А и В, имеющим одинаковые имена. В результатирующую таблицу одинаковые столбцы вставляются только один раз.

select * from sport natural right outer join music;

результат:

music

+----+-------+

| id | name  |

+----+-------+

|  1 | Mike  |

|  2 | Sam   |

|  3 | Garry |

+----+-------+

Дайте мне данные всех колонок из реестра музыкантов (именно музыкантов!), для которых в реестре спортсменов есть колонки с такими же названиями.

Полезные ссылки

1. Описание синтаксиса JOIN на dev.mysql.com (английский)
2. Описание синтаксиса JOIN на mysql.ru (русский)
3. Синтаксис соединенных таблиц в руководстве "Введение в системы управления базами данных" на citforum.ru

Отредактированно FiMko (24.03.2012 22:32:43)

Форум пользователей MySQL

#1 23.09.2010 17:41:13

Разбираемся с различными типами JOIN

#2 23.09.2010 17:54:03

Re: Разбираемся с различными типами JOIN

#3 23.09.2010 18:06:18

Re: Разбираемся с различными типами JOIN

rgbeast написал:

#4 23.09.2010 22:35:23

Re: Разбираемся с различными типами JOIN

Код:

#5 24.09.2010 18:07:55

Re: Разбираемся с различными типами JOIN

paulus написал:

Код:

http://dev.mysql.com/doc/refman/5.0/en/join.html написал:

paulus написал:

http://dev.mysql.com/doc/refman/5.0/en/join.html написал:

#6 26.09.2010 19:53:29

Re: Разбираемся с различными типами JOIN

#7 26.09.2010 21:20:24

Re: Разбираемся с различными типами JOIN

paulus написал:

#8 26.09.2010 22:52:33

Re: Разбираемся с различными типами JOIN

#9 26.09.2010 23:43:07

Re: Разбираемся с различными типами JOIN

paulus написал:

http://dev.mysql.com/doc/refman/5.0/en/join.html написал:

#10 27.09.2010 10:23:17

Re: Разбираемся с различными типами JOIN

#11 27.09.2010 10:45:56

Re: Разбираемся с различными типами JOIN

paulus написал:

#12 27.09.2010 12:16:27

Re: Разбираемся с различными типами JOIN

#13 27.09.2010 14:37:54

Re: Разбираемся с различными типами JOIN

paulus написал:

http://dev.mysql.com написал:

#14 01.10.2010 12:23:25

Re: Разбираемся с различными типами JOIN

Board footer