VPF::Описание всех видов JOIN в MySQL - Форум программистов

FiMa1

Дата 9.10.2010, 22:57 (ссылка)

(голосов:1)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 408
Регистрация: 23.9.2006

Репутация: 1
Всего: 6

Ребята, привет всем!

Благодаря неоценимой помощи администраторов сайта sqlinfo.ru (извините, не мог не упомянуть), я составил описание всех видов JOIN в MySQL с примерами их использования и хотел бы поделиться этой информацией с вами. Принимается любая критика, дополнения, исправления.
Все примеры и описания JOIN рассмотрены на примере 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 (русский)

Это сообщение отредактировал(а) FiMa1 - 9.10.2010, 23:00

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MySQL \| Следующая тема »