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

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

Решение
Используйте LEFT JOIN. Начиная с версии MySQL 3.23.25, можно также использовать RIGHT JOIN.

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

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

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


Вы хотите определить игроков, не выполнивших такую перебежку. Необходимо найти тех игроков из первого списка, которые не попали во второй.

Для ответов на такие вопросы вам нужно использовать LEFT JOIN.

Давайте определим, какие художники из таблицы artist отсутствуют в таблице painting. Сейчас таблицы небольшие, так что вы без труда можете просмотреть их визуально и выявить, что у вас нет картин Моне и Пикассо (нет записей painting со значением a_id, равным 2 или 4):

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 |
+-----+------+----------------------+-------+------+

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


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

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

+------+-----------+------+-----+--------------------+-------+------+
| a_id | name | a_id | p_id | title | state | price |
+------+-----------+------+-----+--------------------+------+-------+
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
+-----+------------+------+-----+--------------------+-----+------+

Очевидно, что получен неверный результат! Запрос выводит список всех несовпадающих комбинаций значений двух строк, но вам-то на самом деле нужен список значений artist, которых вообще нет в painting.


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

Сталкиваясь с необходимостью нахождения значений одной таблицы, которым не найдено соответствий (или которые отсутствуют) в другой таблице, вы сразу же должны думать: «Ага! Все понятно. Нужно использовать LEFT JOIN».LEFT JOIN (левое соединение) похоже на обычное соединение тем, что оно связывает строки первой (левой) таблицы со строками второй (правой) таблицы. Но в дополнение, если строке левой таблицы не найдено соответствия в правой, LEFT JOIN все же выводит строку, в которой все столбцы правой таблицы установлены в NULL. То есть для того чтобы найти значения, отсутствующие в правой таблице, можно искать NULL. Давайте рассмотрим операцию поэтапно. Сначала выполним обычное соединение для нахождения совпадающих строк:

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 |
+-----+------------+------+------+----------------------+-------+------+

В этом выводе первый столбец a_id получен из таблицы artist, а второй – из painting.

Теперь сравним этот результат с выводом, полученным при выполнении LEFT JOIN.


Синтаксис запроса LEFT JOIN похож на синтаксис обычного соединения, только имена таблиц разделяются не запятой, а словами LEFT JOIN, а столбцы для сравнения задаются в инструкции ON, а не WHERE:

mysql> SELECT * FROM artist LEFT JOIN painting
-> ON 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 |
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
| 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 |
| 4 | Picasso | NULL | NULL | NULL | NULL | NULL |
| 5 | Renoir | 5 | 6 | Les Deux Soeurs | NE | 64 |
+-----+------------+-------+------+-----------------------+------+-------+

Вывод похож на вывод обычного соединения, но LEFT JOIN формирует и строку вывода для тех строк таблицы artist, которым не найдено соответствия в painting.


В этих строках вывода все столбцы painting установлены в NULL.

На следующем шаге ограничим вывод только теми строками таблицы artist, которым не найдено соответствия, добавив инструкцию WHERE для поиска значений NULL в столбце painting, имя которого указано в инструкции ON:

mysql> SELECT * FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id
-> WHERE painting.a_id IS NULL;

+-----+---------+------+------+-------+-------+
| a_id | name | a_id | p_id | title | price |
+-----+---------+-------+------+-------+-------+
| 2 | Monet | NULL | NULL | NULL | NULL |
| 4 | Picasso | NULL | NULL | NULL | NULL |
+-----+---------+-------+------+-------+-------+

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

mysql> SELECT artist.* FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id
-> WHERE painting.a_id IS NULL;

+------+---------+
| a_id | name |
+------+---------+
| 2 | Monet |
| 4 | Picasso |
+------+---------+

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

mysql> SELECT artist.name,
-> IF(COUNT(painting.a_id)>0,'yes','no') AS 'in collection'
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;

+-----------+--------------+
| name | in collection |
+-----------+--------------+
| Da Vinci | yes |
| Monet | no |
| Picasso | no |
| Renoir | yes |
| Van Gogh | yes |
+-----------+--------------+

Начиная с версии MySQL 3.23.25 вы также можете использовать RIGHT JOIN (правое соединение), которое аналогично LEFT JOIN, только в нем меняются местами левая и правая таблицы. Другими словами, вывод RIGHT JOIN содержит строку для каждой строки правой таблицы, даже если ей не найдено соответствие в левой таблице. Можно преобразовать предыдущее соединение LEFT JOIN в RIGHT JOIN с выводом того же результата так:

mysql> SELECT artist.name,
-> IF(COUNT(painting.a_id)>0,'yes','no') AS 'in collection'
-> FROM painting RIGHT JOIN artist ON painting.a_id = artist.a_id
-> GROUP BY artist.name;

+-----------+--------------+
| name | in collection |
+-----------+--------------+
| Da Vinci | yes |
| Monet | no |
| Picasso | no |
| Renoir | yes |
| Van Gogh | yes |
+-----------+--------------+

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

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