MySQL / 12. Использование нескольких таблиц

Сравнение таблицы с самой собой

Задача
Вы хотите сравнить записи таблицы с другими записями той же самой таблицы. Например, вы хотите найти в вашей коллекции все картины автора, написавшего «Едоки картофеля». Или хотите узнать, какие штаты из таблицы states вступили в Союз в том же году, что и Нью-Йорк (New York).Или хотите узнать, кто из людей, перечисленных в таблице profile, любит одни и те же блюда.

Решение
Задачи, требующие соединения таблицы с ней же самой, решаются с помощью операции самосоединения (self-join). Она во многом похожа на другие соединения, только вы должны всегда использовать псевдонимы таблиц, чтобы по-разному ссылаться на одну и ту же таблицу в запросе.

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

Указанием на необходимость самосоединения является вопрос о парах элементов таблицы, удовлетворяющих некоторым условиям. Например, предположим, что ваше любимая картина – это «Едоки картофеля» («The Potato Eaters»), и вы хотите определить все остальные экспонаты вашей коллекции, созданные тем же автором. Вы можете поступить так:

1. Идентифицируйте строку таблицы painting, содержащую название «The Potato Eaters», чтобы можно было ссылаться на ее значение a_id.

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

3. Выведите названия картин из найденных строк. Идентификаторы художников и названия картин, с которых мы начинаем работу, выглядят так:

mysql> SELECT a_id, title FROM painting ORDER BY a_id;

+-----+----------------------+
| a_id | title                       |
+-----+----------------------+
| 1     | The Last Supper    |
| 1     | The Mona Lisa       |
| 3     | Starry Night           |
| 3     | The Potato Eaters |
| 3     | The Rocks              |
| 5     | Les Deux Soeurs    |
+-----+----------------------+

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

mysql> SELECT @id := a_id FROM painting WHERE title = 'The Potato Eaters';

+-------------+
| @id := a_id |
+-------------+
| 3                  |
+-------------+

mysql> SELECT title FROM painting WHERE a_id = @id;

+----------------------+
| title                        |
+----------------------+
| Starry Night           |
| The Potato Eaters |
| The Rocks              |
+----------------------+

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

mysql> SELECT title FROM painting, painting
-> WHERE title = 'The Potato Eaters' AND a_id = a_id;
ERROR 1066 at line 1: Not unique table/alias: 'painting'

Проблема такого запроса в том, что ссылки на столбцы неоднозначны. MySQL не может определить, на какой экземпляр таблицы painting ссылается каждый из столбцов. Решение состоит в присвоении хотя бы одному экземпляру таблицы псевдонима, чтобы можно было отличать столбцы с помощью спецификаторов таблиц. Покажем, как это можно сделать, используя псевдонимы p1 и p2 для ссылок на таблицы painting:

mysql> SELECT p2.title
-> FROM painting AS p1, painting AS p2
-> WHERE p1.title = 'The Potato Eaters'
-> AND p1.a_id = p2.a_id;

+----------------------+
| title                        |
+----------------------+
| Starry Night           |
| The Potato Eaters |
| The Rocks              |
+----------------------+

Результат запроса типичен для самосоединения: если вы используете некоторое значение в одном экземпляре таблицы («The Potato Eaters») для нахождения соответствующих записей в другом экземпляре (картины того же художника), то вывод содержит и первоначальное значение. В этом есть смысл – в конце концов, значение соответствует самому себе. Если же вы хотите найти только другие картины того же автора, необходимо явно исключить исходное значение из вывода:

mysql> SELECT p2.title-> FROM painting AS p1, painting AS p2
-> WHERE p1.title = 'The Potato Eaters' AND p2.title != 'The Potato Eaters'
-> AND p1.a_id = p2.a_id;

+--------------+
| title              |
+--------------+
| Starry Night |
| The Rocks    |
+--------------+

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

mysql> SELECT p2.title
-> FROM painting AS p1, painting AS p2
-> WHERE p1.title = 'The Potato Eaters' AND p1.title != p2.title
-> AND p1.a_id = p2.a_id;

+--------------+
| title              |
+--------------+
| Starry Night |
| The Rocks    |
+--------------+

Рассмотренные запросы использовали значения идентификаторов для сопоставления записей двух экземпляров таблиц, но можно использовать и любые другие типы значений. Например, чтобы при помощи таблицы states ответить на вопрос: «Какие штаты вступили в Союз в том же году, что и Нью-Йорк?», будем выполнять попарное сравнение времен, используя составляющую года даты из столбца statehood:

mysql> SELECT s2.name, s2.statehood
-> FROM states AS s1, states AS s2
-> WHERE s1.name = 'New York'
-> AND YEAR(s1.statehood) = YEAR(s2.statehood)
-> ORDER BY s2.name;

+------------------+---------------+
| name                | statehood    |
+------------------+---------------+
| Connecticut      | 1788-01-09 |
| Georgia             | 1788-01-02 |
| Maryland          | 1788-04-28  |
| Massachusetts | 1788-02-06  |
| New Hampshire | 1788-06-21 |
| New York           | 1788-07-26 |
| South Carolina  | 1788-05-23  |
| Virginia              | 1788-06-25  |
+------------------+---------------+

Исходное значение (New York) снова попадает в вывод. Если вы хотите изменить ситуацию, добавьте в инструкцию WHERE выражение, явно исключающее такое значение:

mysql> SELECT s2.name, s2.statehood
-> FROM states AS s1, states AS s2
-> WHERE s1.name = 'New York' AND s1.name != s2.name
-> AND YEAR(s1.statehood) = YEAR(s2.statehood)
-> ORDER BY s2.name;

+------------------+--------------+
| name                | statehood   |
+------------------+--------------+
| Connecticut      | 1788-01-09 |
| Georgia             | 1788-01-02 |
| Maryland           | 1788-04-28 |
| Massachusetts  | 1788-02-06 |
| New Hampshire | 1788-06-21 |
| South Carolina  | 1788-05-23  |
| Virginia              | 1788-06-25  |
+------------------+----------------+

Как и задача нахождения других картин художника, написавшего «Едоков картофеля», задача о государственности штатов может быть решена с помощью переменной SQL и двух запросов. Это верно для любых поисков соответствия конкретной строке таблицы. Но бывают задачи, требующие установления соответствия нескольких пар строк, для которых двухэтапный метод не работает. Предположим, что вы хотите определить, какие пары людей, перечисленных в таблице profile, любят одинаковые блюда. В этом случае вывод теоретически может содержать любую пару из таблицы. Фиксированного исходного значения нет, поэтому его невозможно сохранить в переменной.

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

• Результат сравнения истинен, только если оба значения foods указывают одинаковые наборы блюд, а нам достаточно совпадения элементов.

• Два пустых значения воспринимаются сравнением как равные, хотя на самом деле им не соответствуют общие блюда.

Чтобы выявить значения SET, имеющие общие элементы, используем тот факт, что MySQL представляет их как битовые поля и выполняет сравнение при помощи оператора & (побитовое «И»), ища пары, имеющие ненулевое пересечение:

mysql> SELECT t1.name, t2.name, t1.foods, t2.foods
-> FROM profile AS t1, profile AS t2
-> WHERE t1.id != t2.id AND (t1.foods & t2.foods) != 0
-> ORDER BY t1.name, t2.name;

+------+-------+-----------------------+------------------------+
| name | name | foods                      | foods                       |
+------+-------+-----------------------+------------------------+
| Alan  | Brit    | curry,fadge             | burrito,curry,pizza  |
| Alan  | Fred  | curry,fadge             | lutefisk,fadge,pizza |
| Alan  | Mara | curry,fadge             | lutefisk,fadge           |
| Alan  | Sean | curry,fadge             | burrito,curry            |
| Brit    | Alan  | burrito,curry,pizza  | curry,fadge             |
| Brit    | Carl   | burrito,curry,pizza  | eggroll,pizza            |
| Brit    | Fred  | burrito,curry,pizza  | lutefisk,fadge,pizza |
| Brit    | Sean | burrito,curry,pizza  | burrito,curry             |
| Carl   | Brit    | eggroll,pizza            | burrito,curry,pizza   |
| Carl   | Fred  | eggroll,pizza            | lutefisk,fadge,pizza |
| Fred  | Alan  | lutefisk,fadge,pizza | curry,fadge             |
| Fred  | Brit    | lutefisk,fadge,pizza | burrito,curry,pizza  |
| Fred  | Carl   | lutefisk,fadge,pizza | eggroll,pizza            |
| Fred  | Mara | lutefisk,fadge,pizza | lutefisk,fadge          |
| Mara  | Alan  | lutefisk,fadge          | curry,fadge             |
| Mara  | Fred | lutefisk,fadge           | lutefisk,fadge,pizza |
| Sean  | Alan  | burrito,curry            | curry,fadge              |
| Sean  | Brit    | burrito,curry            | burrito,curry,pizza   |
+-------+-------+------------------------+------------------------+

Некоторые задачи с участием самосоединений относятся к разновидности: «Для каких значений нет соответствий?». Примером такого вопроса может быть: «Кто из отправителей сообщений, указанных в таблице mail, ни разу не писал самому себе?». Сначала определим, кто кому отправлял сообщения:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
-> ORDER BY srcuser, dstuser;

+---------+---------+
| srcuser | dstuser |
+---------+---------+
| barb     | barb      |
| barb     | tricia      |
| gene    | barb      |
| gene    | gene     |
| gene    | tricia     |
| phil       | barb     |
| phil       | phil       |
| phil       | tricia     |
| tricia     | gene    |
| tricia     | phil       |
+---------+---------+

Некоторые из этих пар показывают, что люди писали сами себе:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
-> WHERE srcuser = dstuser;

+---------+---------+
| srcuser | dstuser |
+---------+---------+
| phil       | phil        |
| barb     | barb      |
| gene    | gene      |
+--------+----------+

Нахождение людей, которые не отправляют письма самим себе, – это задача отсутствия соответствий, для решения которой обычно требуется использовать LEFT JOIN. В данном случае необходимо выполнить левое соединение таблицы mail с ней самой:

mysql> SELECT DISTINCT m1.srcuser
-> FROM mail AS m1 LEFT JOIN mail AS m2
-> ON m1.srcuser = m2.srcuser AND m2.srcuser = m2.dstuser
-> WHERE m2.dstuser IS NULL;

+---------+
| srcuser |
+---------+
| tricia     |
+---------+

Для каждой записи из таблицы mail запрос подбирает соответствия – совпадающих отправителя и получателя. Для записей, которым не найдено такого соответствия, LEFT JOIN включает в вывод строку, все столбцы m2 которой установлены в NULL. Такие строки определяют отправителей, которые не посылали себе писем.

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

mysql> CREATE TABLE tmp
-> SELECT a_id, MAX(price) AS max_price FROM painting GROUP BY a_id;
mysql> SELECT artist.name, painting.title, painting.price
-> FROM artist, painting, tmp
-> WHERE painting.a_id = tmp.a_id
-> AND painting.price = tmp.max_price
-> AND painting.a_id = artist.a_id;

+-----------+-----------------------+-------+
| name       | title                         | price |
+-----------+-----------------------+-------+
| Da Vinci    | The Mona Lisa        | 87    |
| Van Gogh | The Potato Eaters | 67     |
| Renoir      | Les Deux Soeurs    | 64     |
+-----------+-----------------------+-------+

А можно поступить по-другому, распознавая картины и извлекая значения из каждой такой строки при помощи LEFT JOIN. Следующий запрос идентифицирует картины:

mysql> SELECT p1.a_id, p1.title, p1.price
-> FROM painting p1
-> LEFT JOIN painting p2
-> ON p1.a_id = p2.a_id AND p1.price < p2.price
-> WHERE p2.a_id IS NULL;

+-----+----------------------+-------+
| a_id | title                        | price |
+-----+----------------------+-------+
| 1      | The Mona Lisa       | 87     |
| 3      | The Potato Eaters | 67     |
| 5      | Les Deux Soeurs   | 64      |
+-----+----------------------+-------+

Для вывода фамилий художников результат объединяется с таблицей artist:

mysql> SELECT artist.name, p1.title, p1.price
-> FROM (painting p1
-> LEFT JOIN painting p2
-> ON p1.a_id = p2.a_id AND p1.price < p2.price), artist
-> WHERE p2.a_id IS NULL AND p1.a_id = artist.a_id;

+-----------+----------------------+-------+
| name       | title                        | price |
+-----------+----------------------+-------+
| Da Vinci    | The Mona Lisa        | 87   |
| Van Gogh | The Potato Eaters | 67    |
| Renoir      | Les Deux Soeurs    | 64   |
+-----------+-----------------------+------+

Знайте, что задача сравнения таблицы с ней самой не обязательно требует использования самосоединения, даже если можно решить ее таким способом. Рассмотрим, например, таблицу mail. Можно определить, кто посылал письма самому себе, используя самосоединение:

mysql> SELECT DISTINCT m1.srcuser, m2.dstuser
-> FROM mail AS m1, mail AS m2
-> WHERE m1.srcuser = m2.srcuser AND m2.dstuser = m1.srcuser;

+---------+---------+
| srcuser | dstuser |
+---------+---------+
| phil       | phil        |
| barb     | barb      |
| gene    | gene      |
+---------+---------+

Но это неразумно. Запросу совсем не нужно сравнивать записи со всеми остальными. Достаточно просто сравнить различные столбцы внутри каждой строки, поэтому вполне можно выполнить обычный запрос без соединения:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
-> WHERE srcuser = dstuser;

+---------+---------+
| srcuser | dstuser |
+---------+---------+
| phil       | phil        |
| barb     | barb      |
| gene    | gene      |
+---------+---------+

Статьи по MySQL на эту тему:

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