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

Параллельный выбор записей из нескольких таблиц

Задача
Вы хотите выбирать строки одну за другой из нескольких таблиц или выбирать несколько наборов строк из одной таблицы – все в одно результирующее множество.

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

Обсуждение
Соединение JOIN удобно для расположения столбцов из разных таблиц вместе бок о бок. Но оно не подходит, если вас интересует результирующее множество, которое содержит набор строк из нескольких таблиц, следующих одна за другой, или несколько наборов строк одной таблицы. Для таких операций удобно использовать объединение – UNION. Объединение UNION позволяет запустить несколько предложений SELECT и «вертикально» склеить их результаты. Вы получаете вывод в одном результирующем множестве вместо того, чтобы запускать несколько запросов и получать несколько результирующих множеств.

Операция UNION появилась в версии MySQL 4.0. В этом разделе показано, как ее использовать, и описано несколько способов решения задач в старых версиях MySQL.

Предположим, что у вас есть две таблицы со списками потенциальных и фактических клиентов (prospect и customer) и третья (vendor), перечисляющая ваших поставщиков. Вы хотите создать единый список рассылки, объединив имена и адреса из всех трех таблиц. UNION обеспечивает решение такой задачи. Предположим, что таблицы имеют такое содержимое:

mysql> SELECT * FROM prospect;

+--------+--------+----------------------------+
| fname  | lname  | addr                              |
+--------+--------+----------------------------+
| Peter    | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith  | 916 Maple Dr.                |
+--------+---------+----------------------------+

mysql> SELECT * FROM customer;

+------------+--------------+--------------------------+
| last_name | first_name | address                      |
+------------+-------------+---------------------------+
| Peterson   | Grace         | 16055 Seminole Ave. |
| Smith        | Bernice       | 916 Maple Dr.              |
| Brown       | Walter        | 8602 1st St.                |
+------------+--------------+--------------------------+

mysql> SELECT * FROM vendor;

+---------------------+--------------------------+
| company              | street                          |
+---------------------+--------------------------+
| ReddyParts, Inc. | 38 Industrial Blvd.       |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+--------------------+---------------------------+

Столбцы таблиц похожи, но не идентичны: prospect и customer используют разные имена для столбцов имени и фамилии, а в таблице vendor всего один столбец для названия компании. Все это не имеет никакого значения для UNION; единственное, в чем необходимо убедиться, – изо всех таблиц должно выбираться одинаковое количество столбцов и в одинаковом порядке. Следующий запрос показывает, как выбирать имена и адреса из трех таблиц одновременно:

mysql> SELECT fname, lname, addr FROM prospect
-> UNION
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;

+-------------------+------------+---------------------------+
| fname                 | lname      | addr                              |
+-------------------+------------+---------------------------+
| Peter                  | Jones      | 482 Rush St., Apt. 402 |
| Bernice                | Smith      | 916 Maple Dr.                |
| Grace                  | Peterson | 16055 Seminole Ave.    |
| Walter                 | Brown     | 8602 1st St.                  |
| ReddyParts, Inc. |                | 38 Industrial Blvd.        |
| Parts-to-go, Ltd. |                | 213B Commerce Park.  |
+--------------------+-----------+----------------------------+

Имена и типы в результирующем множестве определяются именами и типами столбцов, извлеченных первым предложением SELECT. Обратите внимание, что по умолчанию UNION удаляет повторения; Bernice Smith присутствует и в таблице prospect, и в таблице customer, но результирующее множество содержит только одну такую запись. Если вы хотите выбрать все записи, включая повторения, укажите после первого ключевого слова UNION ключевое слово ALL:

mysql> SELECT fname, lname, addr FROM prospect
-> UNION ALL
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;

+--------------------+----------+-----------------------------+
| fname                 | lname     | addr                               |
+--------------------+----------+-----------------------------+
| Peter                   | Jones     | 482 Rush St., Apt. 402 |
| Bernice               | Smith       | 916 Maple Dr.                |
| Grace                  | Peterson | 16055 Seminole Ave.    |
| Bernice                | Smith      | 916 Maple Dr.                |
| Walter                 | Brown     | 8602 1st St.                  |
| ReddyParts, Inc. |               | 38 Industrial Blvd.         |
| Parts-to-go, Ltd. |                | 213B Commerce Park.  |
+--------------------+-----------+---------------------------+

Поскольку необходимо выбирать одинаковое количество столбцов из всех таблиц, предложение SELECT для таблицы vendor (в которой всего один столбец для названия компании) извлекает фиктивный (пустой) столбец названия. Можно было бы обеспечить одинаковое количество столбцов и другим способом: объединив столбцы имени и фамилии таблиц prospect и customer в один столбец:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
-> UNION
-> SELECT CONCAT(last_name,', ',first_name), address FROM customer
-> UNION
-> SELECT company, street FROM vendor;

+--------------------+----------------------------+
| name                   | addr                              |
+--------------------+----------------------------+
| Jones, Peter       | 482 Rush St., Apt. 402 |
| Smith, Bernice     | 916 Maple Dr.                |
| Peterson, Grace  | 16055 Seminole Ave.    |
| Brown, Walter     | 8602 1st St.                  |
| ReddyParts, Inc. | 38 Industrial Blvd.         |
| Parts-to-go, Ltd. | 213B Commerce Park.    |
+--------------------+-----------------------------+

Для того чтобы упорядочить все результирующее множество, добавьте инструкцию ORDER BY после последнего предложения SELECT. Если вы в инструкции ORDER BY ссылаетесь на столбцы по имени, то это должны быть имена из первого предложения SELECT, так как именно они используются в результирующем множестве. Например, для сортировки по полю name выполните следующее:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
-> UNION
-> SELECT CONCAT(last_name,', ',first_name), address FROM customer
-> UNION
-> SELECT company, street FROM vendor
-> ORDER BY name;

+--------------------+----------------------------+
| name                   | addr                               |
+--------------------+----------------------------+
| Brown, Walter     | 8602 1st St.                   |
| Jones, Peter        | 482 Rush St., Apt. 402 |
| Parts-to-go, Ltd. | 213B Commerce Park.    |
| Peterson, Grace  | 16055 Seminole Ave.     |
| ReddyParts, Inc. | 38 Industrial Blvd.         |
| Smith, Bernice      | 916 Maple Dr.                 |
+--------------------+----------------------------+

В MySQL есть возможность сортировки результатов отдельных предложений SELECT внутри UNION. Для этого заключим указанное предложение SELECT (включая его инструкцию ORDER BY) в скобки:

mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr
-> FROM prospect ORDER BY 1)
-> UNION
-> (SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer ORDER BY 1)
-> UNION
-> (SELECT company, street FROM vendor ORDER BY 1);

+--------------------+----------------------------+
| name                   | addr                              |
+--------------------+----------------------------+
| Jones, Peter       | 482 Rush St., Apt. 402 |
| Smith, Bernice     | 916 Maple Dr.                |
| Brown, Walter     | 8602 1st St.                  |
| Peterson, Grace  | 16055 Seminole Ave.    |
| Parts-to-go, Ltd. | 213B Commerce Park.   |
| ReddyParts, Inc. | 38 Industrial Blvd.         |
+--------------------+-----------------------------+

Похожий синтаксис можно использовать и для инструкции LIMIT. То есть вы можете ограничить все результирующее множество, используя инструкцию LIMIT в самом конце предложения, а можете применять ее к отдельным запросам SELECT, заключая их в скобки. В некоторых случаях разумно исполь зовать ORDER BY в сочетании с LIMIT. Предположим, вы хотите выбрать счастливого призера какой-то рекламной кампании. Чтобы выбрать победителя случайным образом из всего результирующего множества для трех таблиц, сделайте следующее:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
-> UNION
-> SELECT CONCAT(last_name,', ',first_name), address FROM customer
-> UNION
-> SELECT company, street FROM vendor
-> ORDER BY RAND() LIMIT 1;

+-------------------+--------------------------+
| name                  | addr                           |
+-------------------+--------------------------+
| Peterson, Grace | 16055 Seminole Ave. |
+-------------------+--------------------------+

Чтобы выбрать победителя в каждой таблице, сделайте так:

mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr
-> FROM prospect ORDER BY RAND() LIMIT 1)
-> UNION
-> (SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer ORDER BY RAND() LIMIT 1)
-> UNION
-> (SELECT company, street-> FROM vendor ORDER BY RAND() LIMIT 1);

+---------------------+----------------------+
| name                    | addr                      |
+---------------------+----------------------+
| Smith, Bernice      | 916 Maple Dr.        |
| ReddyParts, Inc. | 38 Industrial Blvd. |
+---------------------+----------------------+

Если вас удивит результат (почему выбрано не три строки?), вспомните о присутствии Bernice в двух таблицах и о том, что UNION удаляет повторения.

Если случилось так, что и первый и второй запросы SELECT выбрали Bernice, одна из строк результата будет удалена. (Если в трех таблицах нет повторяющихся строк, запрос всегда будет возвращать три строки.) Естественно, вы можете обеспечить вывод трех записей, используя UNION ALL или запуская предложения SELECT по отдельности.

Если у вас более ранняя версия MySQL, чем 4.0, вы не можете использовать UNION. Но можете получить те же результаты, создав временную таблицу, сохраняя результаты нескольких запросов SELECT в этой таблице и выбирая ее содержимое. В MySQL 3.23 можно создать временную таблицу (CREATE TABLE ... SELECT для первого предложения SELECT), а затем последовательно извлекать в нее другие результирующие множества:

mysql> CREATE TABLE tmp SELECT CONCAT(lname,', ',fname) AS name, addr
-> FROM prospect;
mysql> INSERT INTO tmp (name, addr)
-> SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer;
mysql> INSERT INTO tmp (name, addr)
-> SELECT company, street FROM vendor;

Если ваша версия MySQL старше, чем 3.23, сначала создайте таблицу, затем выберите в нее каждую запись:

mysql> CREATE TABLE tmp (name CHAR(40), addr CHAR(40));
mysql> INSERT INTO tmp (name, addr)
-> SELECT CONCAT(lname,', ',fname), addr
-> FROM prospect;
mysql> INSERT INTO tmp (name, addr)
-> SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer;
mysql> INSERT INTO tmp (name, addr)
-> SELECT company, street FROM vendor;

После вставки отдельных результатов во временную таблицу выберите ее содержимое:

mysql> SELECT * FROM tmp;

+--------------------+----------------------------+
| name                   | addr                              |
+--------------------+----------------------------+
| Jones, Peter       | 482 Rush St., Apt. 402 |
| Smith, Bernice     | 916 Maple Dr.                |
| Peterson, Grace  | 16055 Seminole Ave.    |
| Smith, Bernice     | 916 Maple Dr.                |
| Brown, Walter     | 8602 1st St.                  |
| ReddyParts, Inc. | 38 Industrial Blvd.        |
| Parts-to-go, Ltd.  | 213B Commerce Park. |
+---------------------+---------------------------+

Обратите внимание на то, что результат больше похож на UNION ALL, а не на UNION, так как повторения не удалены. Чтобы удалить из вывода дубликаты, создадим таблицу с уникальным индексом для столбцов name и addr:

mysql> CREATE TABLE tmp (name CHAR(40), addr CHAR(40), UNIQUE (name, addr));
mysql> INSERT INTO ...
...
mysql> SELECT * FROM tmp;

+---------------------+----------------------------+
| name                    | addr                              |
+---------------------+----------------------------+
| Brown, Walter     | 8602 1st St.                   |
| Jones, Peter        | 482 Rush St., Apt. 402 |
| Parts-to-go, Ltd. | 213B Commerce Park.   |
| Peterson, Grace  | 16055 Seminole Ave.    |
| ReddyParts, Inc. | 38 Industrial Blvd.        |
| Smith, Bernice      | 916 Maple Dr.                |
+---------------------+----------------------------+

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

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

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