ОП.08 - 15 - Вложенные запросы. Работа с дублями

Примеры данной темы используют учебные БД:

Вложенные запросы

Вложенные запросы являются одним из наиболее мощных инструментов в SQL, который можно использовать в любых видах запросов. Также их часто обозначают термином «Подзапросы».

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

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

• одна строка и один столбец;
• нескольких строк с одним столбцом;
• нескольких строк с несколькими столбцами.

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

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

SELECT title FROM books
WHERE shelves_id = (
  SELECT id FROM shelves
  WHERE title = 'Полка в кабинете'
  LIMIT 1
);

Таким образом, сначала выполнится запрос, который вернет id полки с названием 'Полка в кабинете', после чего выполнится внешний запрос с переданным значением. При этом важно понимать что в зависимости от типа запроса может вернуться несколько значений, поэтому если мы хотим убедиться что останется лишь одно, лучше "подстраховаться" и указать LIMIT 1.

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

SELECT title FROM books
WHERE shelves_id IN (
  SELECT id FROM shelves
  WHERE title LIKE '%в гостиной'
);

здесь не только отсутствует LIMIT, но также изменено условие с соответствия = на значения в списке IN.

Также подзапросы могут сочетаться и с JOIN-ами, например если нужно найти всех авторов, книги которых стоят на полке в спальне:

SELECT DISTINCT authors.name
FROM authors
JOIN authors_books
ON authors.id = authors_books.authors_id
JOIN books
ON books.id = authors_books.books_id
WHERE books.shelves_id = (
  SELECT id FROM shelves
  WHERE title = 'Полка в спальне'
  LIMIT 1
);

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

SELECT DISTINCT books.title
FROM books
JOIN authors_books ON authors_books.books_id = books.id
WHERE authors_books.authors_id IN (
  SELECT DISTINCT authors_books.authors_id
  FROM authors_books
  JOIN books
  ON books.id = authors_books.books_id
  WHERE books.shelves_id = (
    SELECT id
    FROM shelves
    WHERE title = 'Полка в кабинете'
    LIMIT 1
  )
);

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

[!TIP]
Обратите внимание что внутри скобок символ ; не ставится. Несмотря на то что подзапросы по своей сути это отдельные запросы, но синтаксически они ограничены скобками (), поэтому ; ставят только в конце основного запроса.

Работа с дублями

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

Существует несколько способов обнаружения и удаления дубликатов в SQL. Один из самых простых способов — это использование оператора DISTINCT в запросах SELECT. Он позволяет выбрать уникальные значения из столбца или нескольких столбцов таблицы.

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

SELECT DISTINCT name FROM authors;

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

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

SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

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

SELECT guid, email
FROM users
GROUP BY guid, email
HAVING COUNT(*) > 1

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

DELETE FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
);

но к сожалению именно в MySQL такой запрос не сработает из-за того же пресловутого 'Safe mode', и возникнет ошибка:

Error Code: 1093. You can't specify target table 'users' for update in FROM clause

Эта ошибка возникает при использовании подзапроса в операторе UPDATE или DELETE, когда таблица, которую необходимо изменить, упоминается в подзапросе. В таком случае MySQL не позволяет изменять таблицу, которая используется в подзапросе, чтобы избежать конфликтов с блокировками и транзакциями.

Для решения этой проблемы можно использовать временную таблицу или присвоить алиас таблице в подзапросе. Например, для удаления дубликатов из таблицы "users" по столбцу "email" можно использовать следующий запрос:

DELETE FROM users
WHERE email IN (
  SELECT email
  FROM ( SELECT email FROM users ) AS users_tmp
  GROUP BY email
  HAVING COUNT(*) > 1
) AND id > 0;

[!WARNING]
Важно понимать что такой метод прямого удаления фактически удалит все записи которые имеют дублирующиеся значения email!

В этом запросе мы создаем временную таблицу с помощью подзапроса SELECT email FROM users и присваиваем ей алиас "users_tmp". Затем мы группируем записи по столбцу "email" и выбираем только те группы, которые содержат более одной записи. Наконец, мы удаляем все записи из таблицы "users", у которых значение столбца "email" содержится в выбранных группах. Кроме того, не забываем об еще одном ограничении 'Safe mode' — необходимости указать явно первичный ключ, которое мы также учли через id > 0.

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

DELETE FROM users WHERE email IN (
  SELECT email FROM (
    SELECT email, MIN(id) AS min_id FROM users
    GROUP BY email
  ) AS tmp WHERE id > min_id
) AND id > 0;

Этот запрос удаляет дубликаты из таблицы users, оставляя только записи с наименьшим значением id для каждого уникального значения email. Таким образом, он решает проблему без необходимости отключения 'Safe mode'. Однако, такой запрос может быть более сложным для понимания и использования, особенно для новичков в SQL.

[!TIP]
В общем виде, нахождение и удаление дубликатов записей в автоматическом режиме — это довольно опасная задача, поэтому перед выполнением данной операции, как правило, правильнее для начала снять дамп с БД или с отдельной таблицы в которой планируется искать дубли. Как правило, решение дубль это или нет лучше принимать осознанно, держа в уме что чем больше массив данных тем больше вероятность встретить повторяющиеся значения, не являющиеся аномалиями БД, например — полных однофамильцев рожденных в один день, что на практике совершенно не такая фантастическая ситуация даже для небольших БД.

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