Как использовать объединения в SQL
Автор выбрал программу Write for DOnations.
Введение
Многие базы данных распределяют информацию по разным таблицам в зависимости от их значения и контекста. Часто при извлечении информации о данных, хранящихся в базе данных, вам может понадобиться обратиться к нескольким таблицам одновременно.
системы реляционных баз данных. Операция UNION
берет результаты двух запросов с соответствующими столбцами и объединяет их в один.
В этом руководстве вы будете использовать операции UNION
для одновременного извлечения данных из более чем одной таблицы и последующего объединения результатов. Вы также будете комбинировать оператор UNION
с фильтрацией, чтобы упорядочить результаты.
Предпосылки
Чтобы следовать этому руководству, вам понадобится компьютер с системой управления реляционными базами данных (RDBMS) на основе SQL. Инструкции и примеры в этом руководстве были проверены с использованием следующей среды:
- Сервер под управлением Ubuntu 20.04, пользователь без полномочий root с правами администратора и брандмауэр, настроенный с помощью UFW, как описано в нашем руководстве по первоначальной настройке сервера для Ubuntu 20.04.
- MySQL установлен и защищен на сервере, как описано в шаге 3.
- Основные знания о выполнении запросов
SELECT
для выбора данных из базы данных, как описано в нашем руководстве «Выбор строк из таблиц в SQL».
Примечание. Обратите внимание, что многие СУБД используют собственные уникальные реализации SQL. Хотя команды, описанные в этом руководстве, будут работать на большинстве СУБД и являются частью стандартного синтаксиса SQL, точный синтаксис или вывод могут отличаться, если вы тестируете их в системе, отличной от MySQL.
Вам также понадобится база данных с несколькими таблицами, загруженными образцами данных, чтобы вы могли попрактиковаться в использовании операций UNION. Мы рекомендуем вам ознакомиться со следующим разделом «Подключение к MySQL и настройка образца базы данных» для получения подробной информации о подключении к серверу MySQL и создании образца базы данных, используемого в примерах в этом руководстве.
Подключение к MySQL и настройка образца базы данных
В этом разделе вы подключитесь к серверу MySQL и создадите образец базы данных, чтобы следовать примерам в этом руководстве.
Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локального компьютера:
- ssh sammy@your_server_ip
Затем откройте приглашение сервера MySQL, заменив sammy
именем вашей учетной записи пользователя MySQL:
- mysql -u sammy -p
Создайте базу данных с именем bookstore
:
- CREATE DATABASE bookstore;
Если база данных была создана успешно, вы получите такой вывод:
OutputQuery OK, 1 row affected (0.01 sec)
Чтобы выбрать базу данных bookstore
, выполните следующую инструкцию USE
:
- USE bookstore;
Вы получите следующий вывод:
OutputDatabase changed
После выбора базы данных вы можете создать в ней образцы таблиц. Для целей этого руководства вы будете использовать воображаемый книжный магазин, который предлагает как покупку книг, так и аренду. Обе службы управляются отдельно; таким образом, данные о покупках и аренде хранятся в отдельных таблицах.
Примечание. Схема базы данных для этого примера упрощена для образовательных целей. В реальных сценариях структуры таблиц были бы более сложными и требовали понимания реляционных баз данных.
Первая таблица, book_purchases
, будет содержать данные о купленных книгах и покупателях, совершивших покупки. Он будет содержать четыре столбца:
purchase_id
: этот столбец содержит идентификатор покупки, представленный типом данныхint
. Этот столбец станет первичным ключом таблицы, а каждое значение станет уникальным идентификатором соответствующей строки.customer_name
: в этом столбце будет содержаться имя клиента, выраженное с использованием типа данныхvarchar
, не более 30 символов.book_title
: в этом столбце будет содержаться название купленной книги, выраженное с использованием типа данныхvarchar
, не более 200 символов.date
. При использовании типа данныхdate
в этом столбце будет содержаться дата каждой покупки.
Создайте образец таблицы с помощью следующей команды:
- CREATE TABLE book_purchases (
- purchase_id int,
- customer_name varchar(30),
- book_title varchar(40),
- date date,
- PRIMARY KEY (purchase_id)
- );
Если выводятся следующие выходные данные, первая таблица создана:
OutputQuery OK, 0 rows affected (0.00 sec)
Вторая таблица будет называться book_leases
и будет хранить информацию о заимствованных книгах. Он структурирован аналогично предыдущему, но аренда характеризуется двумя отдельными датами: датой аренды и продолжительностью аренды. Чтобы представить это, таблица аренды будет содержать пять столбцов:
lease_id
: этот столбец содержит идентификатор аренды, представленный типом данныхint
. Этот столбец станет первичным ключом таблицы, а каждое значение станет уникальным идентификатором соответствующей строки.customer_name
: в этом столбце будет содержаться имя клиента, выраженное с использованием типа данныхvarchar
, не более 30 символов.book_title
: в этом столбце будет содержаться название заимствованной книги, выраженное с использованием типа данныхvarchar
, не более 200 символов.date_from
: при использовании типа данныхdate
в этом столбце будет содержаться дата начала аренды.date_to
: при использовании типа данныхdate
в этом столбце будет содержаться дата окончания аренды.
Создайте вторую таблицу с помощью следующей команды:
- CREATE TABLE book_leases (
- lease_id int,
- customer_name varchar(30),
- book_title varchar(40),
- date_from date,
- date_to date,
- PRIMARY KEY (lease_id)
- );
Следующий вывод подтверждает создание второй таблицы:
OutputQuery OK, 0 rows affected (0.00 sec)
После этого загрузите в таблицу Purchases образцы данных, выполнив следующую операцию INSERT INTO
:
- INSERT INTO book_purchases
- VALUES
- (1, 'sammy', 'The Picture of Dorian Gray', '2022-10-01'),
- (2, 'sammy', 'Pride and Prejudice', '2022-10-04'),
- (3, 'sammy', 'The Time Machine', '2022-09-23'),
- (4, 'bill', 'Frankenstein', '2022-07-23'),
- (5, 'bill', 'The Adventures of Huckleberry Finn', '2022-10-01'),
- (6, 'walt', 'The Picture of Dorian Gray', '2022-04-15'),
- (7, 'walt', 'Frankenstein', '2022-10-13'),
- (8, 'walt', 'Pride and Prejudice', '2022-10-19');
Операция INSERT INTO
добавит восемь покупок с указанными значениями в таблицу book_purchases
. Следующий вывод показывает, что все восемь строк были добавлены:
OutputQuery OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
Затем вставьте несколько примеров данных в таблицу book_leases
:
- INSERT INTO book_leases
- VALUES
- (1, 'sammy', 'Frankenstein', '2022-09-14', '2022-11-14'),
- (2, 'sammy', 'Pride and Prejudice', '2022-10-01', '2022-12-31'),
- (3, 'sammy', 'The Adventures of Huckleberry Finn', '2022-10-01', '2022-12-01'),
- (4, 'bill', 'The Picture of Dorian Gray', '2022-09-03', '2022-09-18'),
- (5, 'bill', 'Crime and Punishment', '2022-09-27', '2022-12-05'),
- (6, 'kim', 'The Picture of Dorian Gray', '2022-10-01', '2022-11-15'),
- (7, 'kim', 'Pride and Prejudice', '2022-09-08', '2022-11-17'),
- (8, 'kim', 'The Time Machine', '2022-09-04', '2022-10-23');
Вы получите следующий вывод, который подтверждает, что образцы данных были добавлены:
OutputQuery OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
Аренда и покупка относятся к похожим клиентам и названиям книг, что будет полезно для демонстрации поведения оператора UNION
.
После этого вы готовы следовать остальной части руководства и начать использовать операции UNION
в SQL.
Понимание синтаксиса оператора UNION
Оператор UNION
в SQL указывает базе данных объединить два отдельных набора результатов, полученных с помощью отдельных запросов SELECT
, в один набор результатов, содержащий строки, возвращенные из обоих запросов.
Примечание. Базы данных не ограничивают сложность запросов SELECT
, используемых с UNION
. Запросы на получение данных могут включать подзапросы. Часто UNION
используется для объединения результатов сложных операторов. В учебных целях в примерах в этом руководстве будут использоваться запросы SELECT
, чтобы сосредоточиться на том, как ведет себя оператор UNION
.
В следующем примере показан общий синтаксис инструкции SQL, включающей оператор UNION
:
- SELECT column1, column2 FROM table1
- UNION
- SELECT column1, column2 FROM table2;
Этот фрагмент SQL начинается с оператора SELECT
, который возвращает два столбца из table1
, за которым следует оператор UNION
и второй SELECT
. заявление. Второй запрос SELECT
также возвращает два столбца, но из table2
. Ключевое слово UNION
сообщает базе данных, что следует взять предыдущий и последующий запросы, выполнить их по отдельности, а затем объединить их наборы результатов в один. Весь фрагмент кода, включая оба запроса SELECT
и ключевое слово UNION
между ними, представляет собой один оператор SQL. Из-за этого первый запрос SELECT
не заканчивается точкой с запятой, которая появляется только после всей инструкции.
В качестве примера предположим, что вы хотите составить список всех клиентов, которые купили или взяли книгу напрокат. Записи о покупках хранятся в таблице book_purchases
, а записи об аренде — в таблице book_leases
. Запустите следующий запрос:
- SELECT customer_name FROM book_purchases
- UNION
- SELECT customer_name FROM book_leases;
Вот набор результатов этого запроса:
Output+---------------+
| customer_name |
+---------------+
| sammy |
| bill |
| walt |
| kim |
+---------------+
4 rows in set (0.000 sec)
Эти выходные данные показывают, что Сэмми, Билл, Уолт и Ким либо покупали, либо брали книги в аренду в какой-то момент времени. Чтобы понять, как был сгенерирован этот набор результатов, попробуйте выполнить две инструкции SELECT
по отдельности: один раз для покупок и один раз для аренды.
Выполните следующий запрос, чтобы вернуть клиентов, купивших книги:
- SELECT customer_name FROM book_purchases;
Следующий вывод будет напечатан на экране:
Output+---------------+
| customer_name |
+---------------+
| sammy |
| sammy |
| sammy |
| bill |
| bill |
| walt |
| walt |
| walt |
+---------------+
8 rows in set (0.000 sec)
Сэмми, Билл и Уолт купили книги, а Ким нет.
Затем запустите запрос, чтобы вернуть клиентов, которые брали книги напрокат:
- SELECT customer_name FROM book_leases;
Следующий вывод будет напечатан на экране:
Output+---------------+
| customer_name |
+---------------+
| sammy |
| sammy |
| sammy |
| bill |
| bill |
| kim |
| kim |
| kim |
+---------------+
8 rows in set (0.000 sec)
В таблице аренды упоминаются Сэмми, Билл и Ким, но Уолт никогда не берет книги. Объединив два ответа, вы получите данные как по аренде, так и по покупкам.
Важное различие между использованием UNION
и выполнением двух запросов по отдельности заключается в том, что UNION
помимо объединения результатов удаляет повторяющиеся значения: ни одно из имен клиентов не повторяется в результате.
Чтобы использовать UNION
для правильного объединения результатов двух отдельных запросов, оба запроса должны возвращать результаты в одном и том же формате. Некоторые несоответствия приведут к ошибкам ядра базы данных, в то время как другие дадут результаты, не соответствующие цели запроса.
Рассмотрим два следующих примера:
UNION с несовпадающим количеством столбцов
Попробуйте выполнить UNION
между оператором SELECT
, возвращающим один столбец, и другим оператором, возвращающим два столбца:
- SELECT purchase_id, customer_name FROM book_purchases
- UNION
- SELECT customer_name FROM book_leases;
Сервер базы данных ответит ошибкой:
OutputThe used SELECT statements have a different number of columns
Выполнение операций UNION
над наборами результатов с разным количеством столбцов невозможно.
UNION с несоответствующим порядком столбцов
Попробуйте выполнить UNION
между двумя операторами SELECT
, возвращающими одинаковые значения, но в другом порядке:
- SELECT customer_name, book_title FROM book_purchases
- UNION
- SELECT book_title, customer_name FROM book_leases;
Сервер базы данных не вернет ошибку, но результирующий набор будет неправильным:
Output+------------------------------------+------------------------------------+
| customer_name | book_title |
+------------------------------------+------------------------------------+
| sammy | The Picture of Dorian Gray |
| sammy | Pride and Prejudice |
| sammy | The Time Machine |
| bill | Frankenstein |
| bill | The Adventures of Huckleberry Finn |
| walt | The Picture of Dorian Gray |
| walt | Frankenstein |
| walt | Pride and Prejudice |
| Frankenstein | sammy |
| Pride and Prejudice | sammy |
| The Adventures of Huckleberry Finn | sammy |
| The Picture of Dorian Gray | bill |
| Crime and Punishment | bill |
| The Picture of Dorian Gray | kim |
| Pride and Prejudice | kim |
| The Time Machine | kim |
+------------------------------------+------------------------------------+
16 rows in set (0.000 sec)
В этом примере операция UNION
объединяет первый столбец первого запроса с первым столбцом второго запроса и делает то же самое для второго столбца, смешивая вместе имена клиентов и названия книг.
Использование предложений WHERE и упорядочение вместе с UNION
В предыдущем примере вы объединили наборы результатов, представляющие все строки в двух соответствующих таблицах. Часто вам нужно будет отфильтровать строки перед объединением результатов. Операторы SELECT
, объединенные с оператором UNION
, могут использовать для этого предложение WHERE
.
Предположим, вы хотите узнать, какие книги Сэмми читает с помощью вашего книжного магазина, путем покупки или аренды. Запустите следующий запрос:
- SELECT book_title FROM book_purchases
- WHERE customer_name = 'Sammy'
- UNION
- SELECT book_title FROM book_leases
- WHERE customer_name = 'Sammy';
Оба запроса SELECT
включают предложение WHERE
, отфильтровывая строки из двух отдельных таблиц, чтобы включить только покупки и аренду, выполненные Сэмми
. Набор результатов для этого запроса будет напечатан следующим образом:
Output+------------------------------------+
| book_title |
+------------------------------------+
| The Picture of Dorian Gray |
| Pride and Prejudice |
| The Time Machine |
| Frankenstein |
| The Adventures of Huckleberry Finn |
+------------------------------------+
5 rows in set (0.000 sec)
Еще раз, UNION
гарантирует, что в списке результатов не будет дубликатов. Вы можете использовать предложения WHERE
, чтобы ограничить, какие строки возвращаются в обоих запросах SELECT
или только в одном из них. Кроме того, предложение WHERE
может ссылаться на разные столбцы и условия в обоих операторах.
Результаты, возвращаемые операцией UNION
, не следуют какому-либо определенному порядку. Чтобы изменить это, вы можете использовать предложение ORDER BY
. Упорядочивание выполняется по окончательным объединенным результатам, а не по отдельным запросам.
Чтобы отсортировать названия книг в алфавитном порядке после получения списка всех книг, купленных или взятых напрокат Сэмми, выполните следующий запрос:
- SELECT book_title FROM book_purchases
- WHERE customer_name = 'Sammy'
- UNION
- SELECT book_title FROM book_leases
- WHERE customer_name = 'Sammy'
- ORDER BY book_title;
Следующий вывод будет напечатан на экране:
Output+------------------------------------+
| book_title |
+------------------------------------+
| Frankenstein |
| Pride and Prejudice |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray |
| The Time Machine |
+------------------------------------+
5 rows in set (0.001 sec)
На этот раз результаты возвращаются в порядке, основанном на столбце book_title
, содержащем объединенные результаты обоих запросов SELECT
.
Использование UNION ALL для сохранения дубликатов
Как показано в предыдущих примерах, оператор UNION
автоматически удаляет повторяющиеся строки из результатов. Однако иногда такое поведение не соответствует ожиданиям или намерениям добиться с помощью запроса. Например, предположим, что вас интересуют книги, которые были куплены или взяты в аренду 1 октября 2022 года. Чтобы получить эти названия, вы можете следовать примеру, аналогичному предыдущему:
- SELECT book_title FROM book_purchases
- WHERE date = '2022-10-01'
- UNION
- SELECT book_title FROM book_leases
- WHERE date_from = '2022-10-01'
- ORDER BY book_title;
Вы получите следующие результаты:
Output+------------------------------------+
| book_title |
+------------------------------------+
| Pride and Prejudice |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray |
+------------------------------------+
3 rows in set (0.001 sec)
Возвращаемые названия книг верны, но результаты не сообщат вам, были ли эти книги только куплены, только взяты в аренду или и то, и другое. В тех случаях, когда некоторые книги были куплены и взяты в аренду, их названия будут отображаться как в таблице book_purchases
, так и в таблице book_leases
. Однако в результате удаления повторяющихся строк с помощью UNION
эта информация теряется в результате.
К счастью, в SQL есть способ изменить это поведение и сохранить повторяющиеся строки. Вы можете использовать оператор UNION ALL
для объединения результатов двух запросов без удаления повторяющихся строк. UNION ALL
работает аналогично UNION
, но в случаях, когда одни и те же значения встречаются несколько раз, все они будут присутствовать в результате.
Запустите тот же запрос, но измените UNION
на UNION ALL
:
- SELECT book_title FROM book_purchases
- WHERE date = '2022-10-01'
- UNION ALL
- SELECT book_title FROM book_leases
- WHERE date_from = '2022-10-01'
- ORDER BY book_title;
На этот раз полученный список будет длиннее:
Output+------------------------------------+
| book_title |
+------------------------------------+
| Pride and Prejudice |
| The Adventures of Huckleberry Finn |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray |
| The Picture of Dorian Gray |
+------------------------------------+
5 rows in set (0.000 sec)
Две книги — Приключения Гекльберри Финна
и Портрет Дориана Грея
— появляются в наборе результатов два раза. Это означает, что эти заголовки появились в обеих таблицах book_purchases
и book_leases
. Для дубликатов записей вы можете предположить, что они были и арендованы, и куплены в тот же день.
В зависимости от того, хотите ли вы удалить дубликаты или оставить их, вы можете выбирать между операторами UNION
и UNION ALL
, которые взаимозаменяемы.
Примечание. Выполнение UNION ALL
выполняется быстрее, чем выполнение UNION
, поскольку базе данных не нужно проверять набор результатов на наличие дубликатов. Если вы объединяете результаты двух запросов SELECT
, которые, как вы знаете, не будут содержать повторяющихся строк, использование UNION ALL
может заметно повысить производительность на больших наборах данных.
Заключение
Следуя этому руководству, вы получили данные из нескольких таблиц с помощью операций UNION
и UNION ALL
. Вы также использовали предложения WHERE
для фильтрации результатов и предложения ORDER BY
для их упорядочения. Наконец, вы узнали о возможных ошибках и неожиданном поведении, если операторы SELECT
выдают разные форматы данных.
Хотя приведенные здесь команды должны работать с большинством реляционных баз данных, имейте в виду, что каждая база данных SQL использует свою собственную уникальную реализацию языка. (Более подробно об этих различиях см. в нашем руководстве SQLite, MySQL и PostgreSQL: сравнение систем управления реляционными базами данных.) Вам следует обратиться к официальной документации вашей СУБД для получения более полного описания каждой команды и полного набора ее параметров.
Если вы хотите узнать больше о различных концепциях языка SQL и работе с ним, мы рекомендуем вам ознакомиться с другими руководствами из серии «Как использовать SQL».