Нахождение строк одной таблицы, соответствующих строкам другой

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

Решение
Используйте соединение с инструкцией WHERE для установления соответствия между строками разных таблиц.

Обсуждение
Записи таблиц shirt, tie и pants из рецепта 12.1 никак не связаны друг с другом, поэтому ни одна из их комбинаций не является более осмысленной, чем какая-то другая. Это нормально, так как те примеры были предназначены для иллюстрации выполнения соединения, а не для ответа на вопрос, зачем оно выполняется.

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

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

CREATE TABLE artist
(
a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # идентификатор художника
name VARCHAR(30) NOT NULL, # фамилия художника
PRIMARY KEY (a_id),
UNIQUE (name)
);
CREATE TABLE painting
(
a_id INT UNSIGNED NOT NULL, # идентификатор художника
p_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # идентификатор картины
title VARCHAR(100) NOT NULL, # название картины
state VARCHAR(2) NOT NULL, # штат покупки
price INT UNSIGNED, # цена покупки (доллары)
INDEX (a_id),
PRIMARY KEY (p_id)
);

Вы только что приступили к коллекционированию, поэтому таблицы содержат только следующие записи:

mysql> SELECT * FROM artist ORDER BY a_id;

+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
| 2 | Monet |
| 3 | Van Gogh |
| 4 | Picasso |
| 5 | Renoir |
+------+----------+

mysql> SELECT * FROM painting ORDER BY a_id, p_id;

+-----+------+---------------------+-------+-------+
| a_id | p_id | title | state | price |
+-----+-----+----------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | 5 | The Rocks | IA | 33 |
| 5 | 6 | Les Deux Soeurs | NE | 64 |
+-----+-----+-----------------------+------+------+

Невысокие цены в столбце price таблицы painting показывают, что пока что ваша коллекция включает в себя только дешевые копии, а не оригиналы.


Но это и понятно – кто же может позволить себе оригиналы?

Каждая из таблиц содержит частичную информацию о коллекции. Например, таблица artist не сообщает о том, какие картины написал каждый изхудожников, а painting приводит только идентификаторы художников, без фамилий. Для ответа на некоторые вопросы необходимо объединить две таблицы, причем сделать это так, чтобы их записи были сопоставлены друг другу корректно. Для установления такого соответствия необходимо правильно составить инструкцию WHERE. В рецепте 12.1 я упоминал о том, что выполнение полного соединения обычно неразумно, так как формируется очень большой вывод. Еще одна причина, по которой не стоит выполнять полное соединение, – результат может оказаться бессмысленным. Полное соединение таблиц artist и painting – явный тому пример. Оно не содержит инструкции WHERE, поэтому выводит строки с бесполезной информацией:

mysql> SELECT * FROM artist, painting;

+------+----------+------+------+----------------------+-------+------+
| a_id | name | a_id | p_id | title | state | price |
+------+-----------+-----+-----+----------------------+--------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | 1 | 1 | The Last Supper | IN | 34 |
| 3 | Van Gogh | 1 | 1 | The Last Supper | IN | 34 |
| 4 | Picasso | 1 | 1 | The Last Supper | IN | 34 |
| 5 | Renoir | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | The Mona Lisa | MI | 87 |
| 2 | Monet | 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | Van Gogh | 1 | 2 | The Mona Lisa | MI | 87 |
| 4 | Picasso | 1 | 2 | The Mona Lisa | MI | 87 |
| 5 | Renoir | 1 | 2 | The Mona Lisa | MI | 87 |
| 1 | Da Vinci | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 4 | Picasso | 3 | 3 | Starry Night | KY | 48 |
| 5 | Renoir | 3 | 3 | Starry Night | KY | 48 |
| 1 | Da Vinci | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Picasso | 3 | 4 | The Potato Eaters | KY | 67 |
| 5 | Renoir | 3 | 4 | The Potato Eaters | KY | 67 |
| 1 | Da Vinci | 3 | 5 | The Rocks | IA | 33 |
| 2 | Monet | 3 | 5 | The Rocks | IA | 33 |
| 3 | Van Gogh | 3 | 5 | The Rocks | IA | 33 |
| 4 | Picasso | 3 | 5 | The Rocks | IA | 33 |
| 5 | Renoir | 3 | 5 | The Rocks | IA | 33 |
| 1 | Da Vinci | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 2 | Monet | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 4 | Picasso | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 5 | Renoir | 5 | 6 | Les Deux Soeurs | NE | 64 |
+-----+-------------+-----+-----+-----------------------+----+------+

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


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

mysql> SELECT * FROM artist, painting
-> WHERE artist.a_id = painting.a_id;

+------+----------+------+------+---------------------+-------+------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+---------------------+-------+------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 5 | The Rocks | IA | 33 |
| 5 | Renoir | 5 | 6 | Les Deux Soeurs | NE | 64 |
+-----+-----------+------+------+----------------------+------+------+

Имена столбцов в инструкции WHERE содержат спецификаторы таблиц, чтобы было понятно, какое именно значение a_id подлежит сравнению.


Вывод указывает, кто написал какое произведение и, наоборот, какие картины каждого художника есть в вашей коллекции. Но, возможно, вывод излишне многословен (например, в нем присутствуют два одинаковых столбца a_id; один из таблицы artist, второй – из таблицы painting). Вероятно, вы захотите видеть значения a_id один раз. Или вообще не захотите видеть столбцы идентификаторов. Чтобы исключить их, добавьте список столбцов вывода, в который включены только имена интересующих вас столбцов:

mysql> SELECT artist.name, painting.title, painting.state, painting.price
-> FROM artist, painting
-> WHERE artist.a_id = painting.a_id;

+-----------+-----------------------+-------+------+
| name | title | state | price |
+-----------+-----------------------+-------+------+
| Da Vinci | The Last Supper | IN | 34 |
| Da Vinci | The Mona Lisa | MI | 87 |
| Van Gogh | Starry Night | KY | 48 |
| Van Gogh | The Potato Eaters | KY | 67 |
| Van Gogh | The Rocks | IA | 33 |
| Renoir | Les Deux Soeurs | NE | 64 |
+-----------+-----------------------+-------+------+

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

• Какие произведения создал Ван Гог? Чтобы получить ответ, найдем запись таблицы artist, соответствующую фамилии художника, используем ее значение a_id для нахождения записей в таблице painting и выберем из этих записей название картины:

mysql> SELECT painting.title
-> FROM artist, painting-> WHERE artist.name = 'Van Gogh' AND artist.a_id = painting.a_id;

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

• Кто написал «Мону Лизу»? Здесь будем двигаться в обратном направлении, используя информацию из таблицы painting для нахождения данных в таблице artist:

mysql> SELECT artist.name
-> FROM artist, painting
-> WHERE painting.title = 'The Mona Lisa' AND painting.a_id = artist.a_id;

+----------+
| name |
+----------+
| Da Vinci |
+----------+

• Картины каких мастеров вы покупали в Кентукки и Индиане? Запрос похож на предыдущий, только проверяет другой столбец таблицы painting для нахождения множества записей, которое будет объединяться с таблицей artist:

mysql> SELECT DISTINCT artist.name
-> FROM artist, painting
-> WHERE painting.state IN ('KY','IN') AND artist.a_id = painting.a_id;

+-----------+
| name |
+-----------+
| Da Vinci |
| Van Gogh |
+-----------+

Запрос использует DISTINCT для однократного вывода фамилии каждого художника.


Попробуйте выполнить запрос без DISTINCT и вы увидите, что значение Van Gogh будет выведено дважды, так как вы купили две картины Ван Гога в Кентукки.

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

mysql> SELECT artist.name, COUNT(*) AS 'number of paintings'
-> FROM artist, painting
-> WHERE artist.a_id = painting.a_id
-> GROUP BY artist.name;

+-----------+------------------------+
| name | number of paintings |
+-----------+------------------------+
| Da Vinci | 2 |
| Renoir | 1 |
| Van Gogh | 3 |
+------------+-----------------------+

Усовершенствуем его, чтобы определить, сколько вы заплатили за картины каждого автора, всего (total price) и в среднем за картину (average price):

mysql> SELECT artist.name,
-> COUNT(*) AS 'number of paintings',
-> SUM(painting.price) AS 'total price',
-> AVG(painting.price) AS 'average price'
-> FROM artist, painting WHERE artist.a_id = painting.a_id
-> GROUP BY artist.name;

+-----------+------------------------+-------------+-----------------+
| name | number of paintings | total price | average price |
+-----------+------------------------+-------------+-----------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 3 | 148 | 49.3333 |
+-----------+------------------------+-------------+-----------------+

Обратите внимание на то, что суммарный запрос формирует вывод только для тех художников из таблицы artist, картины которых вы действительно приобретали (например, Моне есть в таблице artist, но в выводе его нет, так как у вас еще нет ни одной его картины).


Если вы хотите, чтобы итоги представляли всех авторов, даже если их произведений нет в вашей коллекции, используйте другой вид соединения – LEFT JOIN.

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

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