Форум программистов, компьютерный форум, киберфорум
Наши страницы

MySQL

Войти
Регистрация
Восстановить пароль
 
 
Рейтинг: Рейтинг темы: голосов - 1, средняя оценка - 5.00
Andruhin
132 / 132 / 8
Регистрация: 19.12.2011
Сообщений: 250
#1

Описание оператора JOIN - MySQL

08.02.2013, 23:10. Просмотров 29067. Ответов 15
Метки нет (Все метки)

MySQL оператор JOIN

Часто вижу на форуме проблемы связанные с объединением в одном запросе данных из нескольких таблиц. Например:
  • как одним запросом достать список категорий, и количество статей в каждой категории?
  • как одним запросом достать два уровня каталога с указанием родителя каждой категории?
  • как одним запросом достать название товара и описание к нему, если они лежать в разных таблицах?
В этом посте мы научимся все это делать, и многое другое.


Содержание

Введение
INNER (CROSS) JOIN
Используем USING
Используем ON
LEFT JOIN
RIGHT JOIN
Многотабличные запросы


Введение
Для начала нам нужен некий набор таблиц, на которых мы будем ставить эксперименты. Я выбрал четыре до безобразия простых таблицы:
SQL
1
2
3
4
5
6
7
8
9
10
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| catalogue      |
| item           |
| item_desc      |
| item_price     |
+----------------+
4 ROWS IN SET (0.00 sec)
Каталог наших товаров
SQL
1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM `catalogue`;
+----+-----------+------------------------------------------------------+
| id | parent_id | catalogue_name                                       |
+----+-----------+------------------------------------------------------+
|  1 |         0 | Телевизоры LED, ЖК, плазменные                       |
|  2 |         0 | DVD и Blu-ray плееры                                 |
|  3 |         1 | Кронштейны и подставки                               |
+----+-----------+------------------------------------------------------+
3 ROWS IN SET (0.00 sec)
Собственно сами товары:
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT * FROM `item`;
+----+--------------+--------------------------------------------------------------------------------+
| id | catalogue_id | item_name                                                                      |
+----+--------------+--------------------------------------------------------------------------------+
|  1 |            1 | плазменный телевизор LG 42PA4510                                               |
|  2 |            1 | ЖК-телевизор Philips 46PFL5507T/60                                             |
|  3 |            2 | Портативный DVD-плеер Mystery MPS-108 Black                                    |
|  4 |            2 | DVD-плеер Fusion FD-U157X Black                                                |
|  5 |            2 | Blu-ray-плеер Samsung BD-E5300                                                 |
|  6 |            2 | Проигрыватель Samsung BD-E5500 Black                                           |
|  7 |            3 | Кронштейн для ЖК и плазм Holder PTS-4006 металлик                              |
|  8 |            3 | Кронштейн для ЖК и плазм Holder LCDS-5004 металлик                             |
+----+--------------+--------------------------------------------------------------------------------+
8 ROWS IN SET (0.00 sec)
Описание товаров
SQL
1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM `item_desc`;
+----+------------------------------------------+
| id | item_description                         |
+----+------------------------------------------+
|  2 | Хорошо показывает                        |
|  5 | Читает много форматов                    |
|  9 | не известный товар                       |
+----+------------------------------------------+
3 ROWS IN SET (0.00 sec)
Цены на товары.
SQL
1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM `item_price`;
+---------+-------+
| item_id | price |
+---------+-------+
|       1 | 15890 |
|       2 | 31990 |
|       5 |  3190 |
|       7 |  1800 |
|       8 |   850 |
+---------+-------+
5 ROWS IN SET (0.00 sec)
Обратите внимание, цены и описания установлены не на все товары.


В зависимости от наших запросов, мы будем использовать три способа соединения таблиц:
  • INNER (CROSS) JOIN - внутреннее (перекрёстное) объединение
  • LEFT JOIN - левостороннее внешнее объединение
  • RIGHT JOIN - правостороннее внешнее объединение


INNER (CROSS) JOIN
Это объединение извлекает строки, которые обязательно присутствуют в объединяемых таблицах.
Без указания условий отбора, выборка вернет декартово произведение, где каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы:
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> SELECT * FROM `item`
    -> INNER JOIN `item_desc`;
+----+--------------+--------------------------------------------------------------------------------+----+------------------------------------------+
| id | catalogue_id | name                                                                           | id | description                              |
+----+--------------+--------------------------------------------------------------------------------+----+------------------------------------------+
|  1 |            1 | плазменный телевизор LG 42PA4510                                               |  2 | Хорошо показывает                        |
|  1 |            1 | плазменный телевизор LG 42PA4510                                               |  5 | Читает много форматов                    |
|  1 |            1 | плазменный телевизор LG 42PA4510                                               |  9 | не известный товар                       |
|  2 |            1 | ЖК-телевизор Philips 46PFL5507T/60                                             |  2 | Хорошо показывает                        |
|  2 |            1 | ЖК-телевизор Philips 46PFL5507T/60                                             |  5 | Читает много форматов                    |
|  2 |            1 | ЖК-телевизор Philips 46PFL5507T/60                                             |  9 | не известный товар                       |
|  3 |            2 | Портативный DVD-плеер Mystery MPS-108 Black                                    |  2 | Хорошо показывает                        |
|  3 |            2 | Портативный DVD-плеер Mystery MPS-108 Black                                    |  5 | Читает много форматов                    |
|  3 |            2 | Портативный DVD-плеер Mystery MPS-108 Black                                    |  9 | не известный товар                       |
|  4 |            2 | DVD-плеер Fusion FD-U157X Black                                                |  2 | Хорошо показывает                        |
|  4 |            2 | DVD-плеер Fusion FD-U157X Black                                                |  5 | Читает много форматов                    |
|  4 |            2 | DVD-плеер Fusion FD-U157X Black                                                |  9 | не известный товар                       |
|  5 |            2 | Blu-ray-плеер Samsung BD-E5300                                                 |  2 | Хорошо показывает                        |
|  5 |            2 | Blu-ray-плеер Samsung BD-E5300                                                 |  5 | Читает много форматов                    |
|  5 |            2 | Blu-ray-плеер Samsung BD-E5300                                                 |  9 | не известный товар                       |
|  6 |            2 | Проигрыватель Samsung BD-E5500 Black                                           |  2 | Хорошо показывает                        |
|  6 |            2 | Проигрыватель Samsung BD-E5500 Black                                           |  5 | Читает много форматов                    |
|  6 |            2 | Проигрыватель Samsung BD-E5500 Black                                           |  9 | не известный товар                       |
|  7 |            3 | Кронштейн для ЖК и плазм Holder PTS-4006 металлик                              |  2 | Хорошо показывает                        |
|  7 |            3 | Кронштейн для ЖК и плазм Holder PTS-4006 металлик                              |  5 | Читает много форматов                    |
|  7 |            3 | Кронштейн для ЖК и плазм Holder PTS-4006 металлик                              |  9 | не известный товар                       |
|  8 |            3 | Кронштейн для ЖК и плазм Holder LCDS-5004 металлик                             |  2 | Хорошо показывает                        |
|  8 |            3 | Кронштейн для ЖК и плазм Holder LCDS-5004 металлик                             |  5 | Читает много форматов                    |
|  8 |            3 | Кронштейн для ЖК и плазм Holder LCDS-5004 металлик                             |  9 | не известный товар                       |
+----+--------------+--------------------------------------------------------------------------------+----+------------------------------------------+
24 ROWS IN SET (0.00 sec)
Такое соединение таблиц редко нужно, чаще требуется выбрать только сопоставленные записи. Делается это установкой условия отбора, используя ON или USING

Используем USING
SQL
1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM `item` 
    -> INNER JOIN `item_desc` USING (`id`);
+----+--------------+------------------------------------------------+------------------------------------------+
| id | catalogue_id | item_name                                      | item_description                         |
+----+--------------+------------------------------------------------+------------------------------------------+
|  2 |            1 | ЖК-телевизор Philips 46PFL5507T/60             | Хорошо показывает                        |
|  5 |            2 | Blu-ray-плеер Samsung BD-E5300                 | Читает много форматов                    |
+----+--------------+------------------------------------------------+------------------------------------------+
2 ROWS IN SET (0.00 sec)
Запрос вернул только те записи, которые имеют одинаковые идентификаторы в обеих таблицах.
Мы использовали USING, потому что в обоих таблицах ключевой столбец имеет одно и тоже имя - id.

Используем ON
SQL
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM `item` `i`  
    -> INNER JOIN `item_price` `ip` ON `i`.`id`=`ip`.`item_id`;
+----+--------------+--------------------------------------------------------------------------------+---------+-------+
| id | catalogue_id | item_name                                                                      | item_id | price |
+----+--------------+--------------------------------------------------------------------------------+---------+-------+
|  1 |            1 | плазменный телевизор LG 42PA4510                                               |       1 | 15890 |
|  2 |            1 | ЖК-телевизор Philips 46PFL5507T/60                                             |       2 | 31990 |
|  5 |            2 | Blu-ray-плеер Samsung BD-E5300                                                 |       5 |  3190 |
|  7 |            3 | Кронштейн для ЖК и плазм Holder PTS-4006 металлик                              |       7 |  1800 |
|  8 |            3 | Кронштейн для ЖК и плазм Holder LCDS-5004 металлик                             |       8 |   850 |
+----+--------------+--------------------------------------------------------------------------------+---------+-------+
5 ROWS IN SET (0.00 sec)
Обратите внимание, во втором примере я использовал синонимы (alias) для имен таблиц:
  • `i` - для item
  • `ip` - для item_price
Синонимы можно также использовать и для столбцов в выборке:
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT `i`.`id`, `i`.`item_name`, `ip`.`price` 
    -> FROM `item` AS `i`  
    -> INNER JOIN `item_price` AS `ip` ON `i`.`id`=`ip`.`item_id`;
+----+--------------------------------------------------------------------------------+-------+
| id | item_name                                                                      | price |
+----+--------------------------------------------------------------------------------+-------+
|  1 | плазменный телевизор LG 42PA4510                                               | 15890 |
|  2 | ЖК-телевизор Philips 46PFL5507T/60                                             | 31990 |
|  5 | Blu-ray-плеер Samsung BD-E5300                                                 |  3190 |
|  7 | Кронштейн для ЖК и плазм Holder PTS-4006 металлик                              |  1800 |
|  8 | Кронштейн для ЖК и плазм Holder LCDS-5004 металлик                             |   850 |
+----+--------------------------------------------------------------------------------+-------+
5 ROWS IN SET (0.00 sec)
Обратите внимание, в этот раз я использовал [AS] для указания синонимов, а в предыдущем варианте его не было. Оба варианта верные, и можно использовать любой вариант. Какой ближе, тот и используйте. Лично я не указываю [AS], экономлю время
31
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
08.02.2013, 23:10
Здравствуйте! Я подобрал для вас темы с ответами на вопрос Описание оператора JOIN (MySQL):

Особенности оператора Join - MySQL
Приветствую. В запросе мне необходимо выбрать данные из нескольких таблиц. Когда я подключаю оператором join вторую таблицу к первой, у...

Необычный join (применить условие where для первой таблицы, а потом where для результата join) - MySQL
мне нужно прежде чем джоинить, применить условие where для первой таблицы. а потом where для результат джойна. как быть? в строение join...

JOIN - MySQL
Помогите разобраться с оператором join! нужно связать 3 таблицы между собой. 1 с 2, 2 с 3. как это сделать? Select a FROM Table_1 JOIN...

Join VS in - MySQL
Люди, здравствуйте. До сих пор не знаю, как правильно искать в таблице группу значений. Через джойн двух таблиц или же через ИН. ...

JOIN - Базы данных
Как сделать запрос к БД : есть 2 таблицы с одинаковой структурой (a int, b text) Надо сделать чтобы выборка шла из 2-х таблиц...

Like в Inner Join - MySQL
Скажите, а можно ли использовать Like в конструкции Inner Join, для того, чтобы объединялись не польностью совпадающие данные. Например,...

15
ns16
93 / 93 / 58
Регистрация: 26.03.2015
Сообщений: 247
18.06.2016, 08:04 #16
Jefe, кстати, в MySQL нет оператора FULL OUTER JOIN. Его альтернативой в данной СУБД является конструкция из двух SELECT'ов, объединенных оператором UNION.

Таким образом, код
SQL
1
2
3
4
SELECT <select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.`Key`=B.`Key`;
можно заменить на
SQL
1
2
3
4
5
6
7
8
9
SELECT <select_list>
    FROM TableA A
    LEFT JOIN TableB B
    ON A.`Key`=B.`Key`
UNION
SELECT <select_list>
    FROM TableA A
    RIGHT JOIN TableB B
    ON A.`Key`=B.`Key`;
А код
SQL
1
2
3
4
5
6
SELECT <select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.`Key`=B.`Key`
WHERE A.`Key` IS NULL
OR B.`Key` IS NULL;
на
SQL
1
2
3
4
5
6
7
8
9
10
11
SELECT <select_list>
    FROM TableA A
    LEFT JOIN TableB B
    ON A.`Key`=B.`Key`
    WHERE B.`Key` IS NULL
UNION
SELECT <select_list>
    FROM TableA A
    RIGHT JOIN TableB B
    ON A.`Key`=B.`Key`
    WHERE A.`Key` IS NULL;
0
18.06.2016, 08:04
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
18.06.2016, 08:04
Привет! Вот еще темы с ответами:

Inner join - MySQL
Здравствуйте, происходит ошибка при выполнении запроса: SELECT * FROM `users` INNER JOIN `universities` INNER JOIN `university_directions`...

Self-join - MySQL
Вывести список всех режиссеров вместе с названием их кинофильма, каторий имеет самый высокий средний рейтинг среди других его фильмов. ...

JOIN - MySQL
Утро доброе! Смотрю урок по SQL, в нем показывают как можно использовать JOIN; Объясните пожалуйста, откуда взялись l и r в 1...

join по sequence - MySQL
Всем доброго времени суток. Вопрос в следующем: Пусть есть 3 таблички меня интересует в каком случае выборка с join будет...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
16
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Рейтинг@Mail.ru