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

Преобразование подзапросов в операции соединения

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

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

Обсуждение
Предположим, что у вас есть две таблицы t1 и t2 с таким содержимым:

mysql> SELECT col1 FROM t1;

+-----+
| col1 |
+-----+
| a     |
| b     |
| c      |
+-----+

mysql> SELECT col2 FROM t2;

+-----+
| col2 |
+-----+
| b      |
| c      |
| d      |
+-----+

Теперь предположим, что вы хотите найти те значения t1, которые содержатся и в t2, или значения t1, которые не содержатся в t2. На такие вопросы можно ответить при помощи подзапросов – вложения одного предложения SELECT внутрь другого, но MySQL не поддерживает подзапросы в версиях, предшествующих 4.1. Этот раздел посвящен тому, как обойти эту проблему.

Следующий запрос содержит подзапрос IN(), который выводит строки t1, значения столбца col1 которых совпадает со значениями col2 таблицы t2:

SELECT col1 FROM t1 WHERE col1 IN (SELECT col2 FROM t2);

Фактически это запрос поиска соответствий, так что его можно переписать при помощи простого соединения:

mysql> SELECT t1.col1 FROM t1, t2 WHERE t1.col1 = t2.col2;

+-----+
| col1 |
+-----+
| b      |
| c      |
+-----+

На обратный вопрос (строки t1, не имеющие соответствий в t2) можно ответить, используя подзапрос NOT IN():

SELECT col1 FROM t1 WHERE col1 NOT IN (SELECT col2 FROM t2);

Это задача отсутствия соответствий, которую можно решить при помощи LEFT JOIN, разновидности соединения. В нашем случае подзапрос NOT IN() эквивалентен такому левому соединению:

mysql> SELECT t1.col1 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2
-> WHERE t2.col2 IS NULL;

+-----+
| col1 |
+-----+
| a      |
+-----+

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

SELECT * FROM t1 WHERE col1 IN (SELECT col2 FROM t2);

Если вы ожидаете, что внутренний SELECT вернет достаточно небольшое количество значений col2, то того же результата, что и подзапрос, можно достичь путем извлечения этих значений и формирования инструкции IN(), которая будет искать их в col1. Например, запрос SELECT col2 FROM t2 выводит значения b, c и d. Используя этот результат, можно выбрать соответствующие значения col1 таким запросом:

SELECT col1 FROM t1 WHERE col1 IN ('b','c','d')

Рассмотрим фрагмент кода на Python:

cursor = conn.cursor ()
cursor.execute ("SELECT col2 FROM t2")
if cursor.rowcount > 0: # ничего не делать, если нет значений
val = [] # список для хранения значений данных
s = "" # строка для хранения заполнителей
# сформировать строку с заполнителями: %s,%s,%s,...
for (col2,) in cursor.fetchall (): # извлечь значение col2 из каждой строки
if s != "":
s = s + "," # разделить заполнители запятыми
s = s + "%s" # добавить заполнитель
val.append (col2) # добавить значение в список значений
stmt = "SELECT col1 FROM t1 WHERE col1 IN (" + s + ")"
cursor.execute (stmt, val)
for (col1,) in cursor.fetchall (): # извлечь значения col1 из результата
print col1
cursor.close ()

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

SELECT col1 FROM t1 WHERE col1 = 'b'
SELECT col1 FROM t1 WHERE col1 = 'c'
SELECT col1 FROM t1 WHERE col1 = 'd'
В программе это можно сделать так:
cursor = conn.cursor ()
cursor2 = conn.cursor ()
cursor.execute ("SELECT col2 FROM t2")
for (col2,) in cursor.fetchall (): # извлечь значение col2 из каждой строки
stmt = "SELECT col1 FROM t1 WHERE col1 = %s"
cursor2.execute ("SELECT col1 FROM t1 WHERE col1 = %s", (col2,))
for (col1,) in cursor2.fetchall (): # извлечь значения col1 из результата
print col1
cursor.close ()
cursor2.close ()

Если у вас так много значений col2, что вы не хотите писать одну огромную инструкцию IN(), но не хотите и выдавать несметное множество отдельных предложений SELECT, то можно скомбинировать два этих способа. Разбейте множество значений col2 на более мелкие группы и создайте для каждой из них инструкцию IN(). У вас появится ряд менее громоздких запросов, каждый из которых ищет несколько значений:

SELECT col1 FROM t1 WHERE col1 IN (первая группа значений col2)
SELECT col1 FROM t1 WHERE col1 IN (вторая группа значений col2)
SELECT col1 FROM t1 WHERE col1 IN (третья группа значений col2)
...

Комбинированный подход можно реализовать так:

grp_size = 1000 # количество идентификаторов, выбираемых одновременно
cursor = conn.cursor ()
cursor.execute ("SELECT col2 FROM t2")
if cursor.rowcount > 0: # ничего не делать, если нет значений
col2 = [] # список для хранения значений данных
for (val,) in cursor.fetchall (): # извлечь значение col2 из каждой строки
col2.append (val)
nvals = len (col2)
i = 0
while i < nvals:
if nvals < i + grp_size:
j = nvals
else:
j = i + grp_size
group = col2[i : j]
s = "" # строка для хранения заполнителей
val_list = []
# сформировать строку с заполнителями: %s,%s,%s,...
for val in group:
if s != "":s = s + "," # разделить заполнители запятыми
s = s + "%s" # добавить заполнитель
val_list.append (val) # добавить значение в список значений
stmt = "SELECT col1 FROM t1 WHERE col1 IN (" + s + ")"
print stmt
cursor.execute (stmt, val_list)
for (col1,) in cursor.fetchall (): # извлечь значение col1 из каждой строки
print col1
i = i + grp_size # перейти к следующей группе значений
cursor.close ()

Имитировать в программе подзапрос NOT IN() несколько сложнее, чем подзапрос IN(). Подзапрос выглядит так:

SELECT col1 FROM t1 WHERE col1 NOT IN (SELECT col2 FROM t2);

Описываемый метод лучше всего работает для небольшого количества значений col1 и col2, так как приходится в хранить в памяти как минимум значения, возвращенные внутренним SELECT, чтобы можно было сравнивать их со значениями, возвращенными внешним SELECT. Приведенный пример хранит в памяти оба множества. Сначала извлечем значения col1 и col2:

cursor = conn.cursor ()
cursor.execute ("SELECT col1 FROM t1")
col1 = []
for (val, ) in cursor.fetchall ():
col1.append (val)
cursor.execute ("SELECT col2 FROM t2")
col2 = []
for (val, ) in cursor.fetchall ():
col2.append (val)
cursor.close ()

Затем проверим каждое значение col1 на предмет присутствия в наборе значений col2. Если не присутствует, то оно удовлетворяет условию NOT IN() подзапроса:

for val1 in col1:
present = 0
for val2 in col2:
if val1 == val2:
present = 1
break
if not present:
print val1

Код выполняет просмотр (lookup) значений col2 в содержащем их массиве. Можно повысить эффективность этой операции, используя ассоциативную структуру данных. Например, в Perl или Python можно поместить значения col2 в хеш или словарь. Еще одним способом имитации подзапросов (по крайней мере, подзапросов типа IN()), является формирование необходимых предложений SQL внутриодного экземпляра mysql и передача их другому экземпляру на исполнение.

Посмотрите на результат такого запроса:

mysql> SELECT CONCAT('SELECT col1 FROM t1 WHERE col1 = \'', col2, '\';')
-> FROM t2;

+------------------------------------------------------------------------+
| CONCAT('SELECT col1 FROM t1 WHERE col1 = \'', col2, '\';') |
+------------------------------------------------------------------------+
| SELECT col1 FROM t1 WHERE col1 = 'b';                                |
| SELECT col1 FROM t1 WHERE col1 = 'c';                                 |
| SELECT col1 FROM t1 WHERE col1 = 'd';                                 |
+------------------------------------------------------------------------+

Этот запрос извлекает значения col2 из t2 и использует их для формирования набора предложений SELECT, которые ищут соответствующие значения col1 в t1. Если вы выдаете запрос в пакетном режиме и подавляете вывод заголовков, то mysql выводит только текст предложений SQL безо всякого обрамления. Этот вывод можно передать другому экземпляру mysql для выполнения запросов. Результат будет таким же, как при выполнении подзапроса. Предлагаю один из способов выполнения процедуры, в котором предполагается, что предложение SELECT, содержащее выражение CONCAT(), хранится в файле make_select.sql:

% mysql -N cookbook < make_select.sql > tmp

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

SELECT col1 FROM t1 WHERE col1 = 'b';
SELECT col1 FROM t1 WHERE col1 = 'c';
SELECT col1 FROM t1 WHERE col1 = 'd';

Чтобы выполнить запросы из файла и сформировать вывод, имитируя подзапрос, используйте такую команду:

% mysql -N cookbook < tmp
b
c

Второй экземпляр mysql также содержит опцию -N, поскольку в противном случае вывод будет содержать строку заголовка каждого выполненного предложения SELECT (попробуйте не указывать -N и посмотрите, что получится). Важным ограничением использования mysql для формирования предложений SQL является следующее: если значения col2 содержат кавычки или другие специальные символы, то генерируемые запросы будут построены некорректно.

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

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