ifima.ru

Описание всех видов Join в MySQL

Posted on Суббота, Сентябрь 25th, 2010 at 11:55 дп

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

При описании различных вариантов использования 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;

результат:

3. table_reference STRAIGHT_JOIN table_reference

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

SELECT * FROM sport STRAIGHT_JOIN music;

результат:

Покажите мне всевозможные сочетания пар, как если бы все ребята занимались И музыкой И спортом И соедините для меня данные именно так, как я прошу – сначала реестр спортсменов (таблица 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 |
+----+-------+

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

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

Tags: join mysql примеры, left join mysql, mysql inner join, mysql join, mysql outer join
Posted in MySQL |

You can leave a response, or trackback from your own site.

Летать? Из Германии дешевле!«

»Различные хитрости в MySQL

6 Responses to “Описание всех видов Join в MySQL”

gdever:

07.01.2011 в 4:23 пп

Good article! Thanks.
Cosmologist:

19.06.2011 в 7:47 пп

Спасибо, по Natural Join только у вас внятное описание нашел
Viktory:

21.07.2011 в 1:29 дп

Огромнейшее спасибо! То что надо, все кратко, четко и понятно.
snauka:

03.08.2011 в 4:16 пп

Очень понятное и подробное описание
dadli:

04.09.2011 в 10:46 пп

полезная статья спс
loze:

20.02.2012 в 10:00 дп

Спасибо, статья отличная.

Описание всех видов 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

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

6 Responses to “Описание всех видов Join в MySQL”

Leave a Reply

Meta