Соединение строк одной таблицы со строками другой

Задача
Вы хотите написать запрос, который использует информацию более чем из одной таблицы.

Решение
Используйте соединение (join) – запрос, ссылающийся на несколько таблиц и указывающий MySQL на то, как сопоставлять их данные.

Обсуждение
Основная идея соединения в том, что оно комбинирует строки таблицы со строками одной или нескольких других таблиц. Полное соединение таблиц выводит все возможные сочетания строк. Например, соединение 100-строчной таблицы с 200-строчной выводит результат, состоящий из 100×200, то есть 20 000 строк. Для больших таблиц и для соединений более чем двух таблиц результирующее множество может быть просто гигантским и может даже вызвать переполнение временного табличного пространства сервера MySQL.

По этой причине, а также потому что абсолютно все комбинации требуются редко, соединение обычно содержит инструкцию WHERE, сужающую выборку запроса. В этом разделе представлены основы синтаксиса соединения, а в последующих разделах мы поговорим о том, как соединение может помочь получить ответы на определенные типы вопросов.

Предположим, что вы абсолютно лишены воображения при подборе одежды и каждый день испытываете проблемы с гардеробом.


Тогда вы решаете обратиться за помощью к MySQL. Для начала помещаем все ваши рубашки (shirt) в одну таблицу, а галстуки (tie) – в другую:

mysql> CREATE TABLE shirt (item CHAR(20));
mysql> INSERT INTO shirt (item)
-> VALUES('Pinstripe'),('Tie-Dye'),('Black');
mysql> CREATE TABLE tie (item CHAR(20));
mysql> INSERT INTO tie (item)
-> VALUES('Fleur de lis'),('Paisley'),('Polka Dot');

Чтобы вывести содержимое каждой из таблиц, можно использовать отдельные однотабличные запросы:

mysql> SELECT item FROM shirt;
+----------+
| item |
+----------+
| Pinstripe |
| Tie-Dye |
| Black |
+----------+

mysql> SELECT item FROM tie;

+-------------+
| item |
+-------------+
| Fleur de lis |
| Paisley |
| Polka Dot |
+-------------+

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


В простейшем соединении участвуют две таблицы, из которых выбираются все столбцы. Если инструкция WHERE не указана, то соединение генерирует вывод из всех комбинаций строк.

То есть чтобы найти все возможные сочетания рубашек и галстуков, используйте запрос, выполняющий полное соединение двух таблиц:

mysql> SELECT * FROM shirt, tie;

+----------+-------------+
| item | item |
+----------+-------------+
| Pinstripe | Fleur de lis |
| Tie-Dye | Fleur de lis |
| Black | Fleur de lis |
| Pinstripe | Paisley |
| Tie-Dye | Paisley |
| Black | Paisley |
| Pinstripe | Polka Dot |
| Tie-Dye | Polka Dot |
| Black | Polka Dot |
+----------+-------------+

Как видите, каждый элемент таблицы shirt образует пару с каждым элементом таблицы tie. Распечатайте список и повесьте на стену – теперь вам больше не надо мучиться по утрам. Каждый день одевайте то, что отображается в первой неиспользованной строке и вычеркивайте ее из списка.

Список столбцов вывода предыдущего запроса выглядит как *.


Для однотабличного запроса список вывода * означает «все столбцы указанной таблицы». Аналогично для соединения это значит «все столбцы всех указанных таблиц», поэтому запрос возвращает столбцы из двух таблиц: shirt и tie.

Можно использовать имя_таблицы.* для выбора всех столбцов определенной таблицы или имя_таблицы.имя_столбца для выбора одного столбца таблицы.

То есть все приведенные ниже запросы эквиваленты:

SELECT * FROM shirt, tie;
SELECT shirt.*, tie.* FROM shirt, tie;
SELECT shirt.*, tie.item FROM shirt, tie;
SELECT shirt.item, tie.* FROM shirt, tie;
SELECT shirt.item, tie.item FROM shirt, tie;

Нотация имя_таблицы.имя_столбца, указывающая имя столбца вместе с именем таблицы, всегда разрешена, но ее можно сократить до имя_столбца, если это имя присутствует только в одной из таблиц соединения. В этом случае MySQL может однозначно определить, к какой таблице относится столбец, и не требуется уточнять имя таблицы. Но при соединении таблиц shirt и tie сокращенные имена использовать нельзя, так как обе таблицы содержат столбец item, и поэтому следующий запрос неоднозначен:

mysql> SELECT item, item FROM shirt, tie;
ERROR 1052 at line 1: Column: 'item' in field list is ambiguous

Если столбцы имеют различные имена, такие как s_item и t_item, то запрос будет недвусмысленным и без указания имен таблиц:

SELECT s_item, p_item FROM shirt, tie;

Чтобы сделать запрос более удобочитаемым, полезно указывать имена столбцов, даже если они не необходимы для MySQL. Именно поэтому я стараюсь использовать уточненные имена в примерах соединений.

Если инструкция WHERE не ограничивает вывод, то соединение формирует строку вывода для каждой возможной комбинации строк. Для больших таблиц это не очень разумная операция, поэтому на выводимые строки обычно налагаются какие-то условия. Например, если вам надоели поддразнивания коллег относительно вашего галстука в горошек, выберите только отличные от данного сочетания предметы вашего туалета:

mysql> SELECT shirt.item, tie.item FROM shirt, tie
-> WHERE tie.item != 'Polka Dot';

+----------+--------------+
| item | item |
+----------+--------------+
| Pinstripe | Fleur de lis |
| Tie-Dye | Fleur de lis |
| Black | Fleur de lis |
| Pinstripe | Paisley |
| Tie-Dye | Paisley |
| Black | Paisley |
+----------+--------------+

Вывод можно ограничить и другими способами. Чтобы выбирать сочетания предметов случайным образом, запускайте каждое утро следующий запрос для выбора одной строки полного соединения:

mysql> SELECT shirt.item, tie.item FROM shirt, tie
-> ORDER BY RAND() LIMIT 1;

+---------+--------------+
| item | item |
+---------+--------------+
| Tie-Dye | Fleur de lis |
+---------+--------------+

Можно выполнять соединение более чем двух таблиц. Давайте создадим таблицу брюк pants:

mysql> SELECT * FROM pants;

+-----------+
| item |
+-----------+
| Plaid |
| Striped |
| Corduroy |
+-----------+

Теперь можно выбирать комбинации рубашек, галстуков и брюк:

mysql> SELECT shirt.item, tie.item, pants.item FROM shirt, tie, pants;

+-----------+--------------+----------+
| item | item | item |
+-----------+-------------+-----------+
| Pinstripe | Fleur de lis | Plaid |
| Tie-Dye | Fleur de lis | Plaid |
| Black | Fleur de lis | Plaid |
| Pinstripe | Paisley | Plaid |
| Tie-Dye | Paisley | Plaid |
| Black | Paisley | Plaid |
| Pinstripe | Polka Dot | Plaid |
| Tie-Dye | Polka Dot | Plaid |
| Black | Polka Dot | Plaid |
| Pinstripe | Fleur de lis | Striped |
| Tie-Dye | Fleur de lis | Striped |
| Black | Fleur de lis | Striped |
| Pinstripe | Paisley | Striped |
| Tie-Dye | Paisley | Striped |
| Black | Paisley | Striped |
| Pinstripe | Polka Dot | Striped |
| Tie-Dye | Polka Dot | Striped |
| Black | Polka Dot | Striped |
| Pinstripe | Fleur de lis | Corduroy |
| Tie-Dye | Fleur de lis | Corduroy |
| Black | Fleur de lis | Corduroy |
| Pinstripe | Paisley | Corduroy |
| Tie-Dye | Paisley | Corduroy |
| Black | Paisley | Corduroy |
| Pinstripe | Polka Dot | Corduroy |
| Tie-Dye | Polka Dot | Corduroy |
| Black | Polka Dot | Corduroy |
+----------+------------+-------------+

Естественно, чем больше таблиц вы объединяете, тем больше комбинаций строк, даже если каждая отдельная таблица не очень велика.

Если вы не хотите писать полные имена таблиц в списке столбцов вывода, сопоставьте каждой таблице короткий псевдоним и ссылайтесь на столбцы таблицы, используя псевдонимы:

SELECT s.item, t.item, p.item
FROM shirt AS s, tie AS t, pants AS p;

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление разности между последовательными строками
Вычисление рейтинга команд
Выявление и удаление несвязанных записей