Поиск по сайту:

Как использовать объединения в 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 с локального компьютера:

  1. ssh sammy@your_server_ip

Затем откройте приглашение сервера MySQL, заменив sammy именем вашей учетной записи пользователя MySQL:

  1. mysql -u sammy -p

Создайте базу данных с именем bookstore:

  1. CREATE DATABASE bookstore;

Если база данных была создана успешно, вы получите такой вывод:

Output
Query OK, 1 row affected (0.01 sec)

Чтобы выбрать базу данных bookstore, выполните следующую инструкцию USE:

  1. USE bookstore;

Вы получите следующий вывод:

Output
Database changed

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

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

Первая таблица, book_purchases, будет содержать данные о купленных книгах и покупателях, совершивших покупки. Он будет содержать четыре столбца:

  • purchase_id: этот столбец содержит идентификатор покупки, представленный типом данных int. Этот столбец станет первичным ключом таблицы, а каждое значение станет уникальным идентификатором соответствующей строки.
  • customer_name: в этом столбце будет содержаться имя клиента, выраженное с использованием типа данных varchar, не более 30 символов.
  • book_title: в этом столбце будет содержаться название купленной книги, выраженное с использованием типа данных varchar, не более 200 символов.
  • date. При использовании типа данных date в этом столбце будет содержаться дата каждой покупки.

Создайте образец таблицы с помощью следующей команды:

  1. CREATE TABLE book_purchases (
  2. purchase_id int,
  3. customer_name varchar(30),
  4. book_title varchar(40),
  5. date date,
  6. PRIMARY KEY (purchase_id)
  7. );

Если выводятся следующие выходные данные, первая таблица создана:

Output
Query 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 в этом столбце будет содержаться дата окончания аренды.

Создайте вторую таблицу с помощью следующей команды:

  1. CREATE TABLE book_leases (
  2. lease_id int,
  3. customer_name varchar(30),
  4. book_title varchar(40),
  5. date_from date,
  6. date_to date,
  7. PRIMARY KEY (lease_id)
  8. );

Следующий вывод подтверждает создание второй таблицы:

Output
Query OK, 0 rows affected (0.00 sec)

После этого загрузите в таблицу Purchases образцы данных, выполнив следующую операцию INSERT INTO:

  1. INSERT INTO book_purchases
  2. VALUES
  3. (1, 'sammy', 'The Picture of Dorian Gray', '2022-10-01'),
  4. (2, 'sammy', 'Pride and Prejudice', '2022-10-04'),
  5. (3, 'sammy', 'The Time Machine', '2022-09-23'),
  6. (4, 'bill', 'Frankenstein', '2022-07-23'),
  7. (5, 'bill', 'The Adventures of Huckleberry Finn', '2022-10-01'),
  8. (6, 'walt', 'The Picture of Dorian Gray', '2022-04-15'),
  9. (7, 'walt', 'Frankenstein', '2022-10-13'),
  10. (8, 'walt', 'Pride and Prejudice', '2022-10-19');

Операция INSERT INTO добавит восемь покупок с указанными значениями в таблицу book_purchases. Следующий вывод показывает, что все восемь строк были добавлены:

Output
Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0

Затем вставьте несколько примеров данных в таблицу book_leases:

  1. INSERT INTO book_leases
  2. VALUES
  3. (1, 'sammy', 'Frankenstein', '2022-09-14', '2022-11-14'),
  4. (2, 'sammy', 'Pride and Prejudice', '2022-10-01', '2022-12-31'),
  5. (3, 'sammy', 'The Adventures of Huckleberry Finn', '2022-10-01', '2022-12-01'),
  6. (4, 'bill', 'The Picture of Dorian Gray', '2022-09-03', '2022-09-18'),
  7. (5, 'bill', 'Crime and Punishment', '2022-09-27', '2022-12-05'),
  8. (6, 'kim', 'The Picture of Dorian Gray', '2022-10-01', '2022-11-15'),
  9. (7, 'kim', 'Pride and Prejudice', '2022-09-08', '2022-11-17'),
  10. (8, 'kim', 'The Time Machine', '2022-09-04', '2022-10-23');

Вы получите следующий вывод, который подтверждает, что образцы данных были добавлены:

Output
Query 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:

  1. SELECT column1, column2 FROM table1
  2. UNION
  3. SELECT column1, column2 FROM table2;

Этот фрагмент SQL начинается с оператора SELECT, который возвращает два столбца из table1, за которым следует оператор UNION и второй SELECT. заявление. Второй запрос SELECT также возвращает два столбца, но из table2. Ключевое слово UNION сообщает базе данных, что следует взять предыдущий и последующий запросы, выполнить их по отдельности, а затем объединить их наборы результатов в один. Весь фрагмент кода, включая оба запроса SELECT и ключевое слово UNION между ними, представляет собой один оператор SQL. Из-за этого первый запрос SELECT не заканчивается точкой с запятой, которая появляется только после всей инструкции.

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

  1. SELECT customer_name FROM book_purchases
  2. UNION
  3. SELECT customer_name FROM book_leases;

Вот набор результатов этого запроса:

Output
+---------------+ | customer_name | +---------------+ | sammy | | bill | | walt | | kim | +---------------+ 4 rows in set (0.000 sec)

Эти выходные данные показывают, что Сэмми, Билл, Уолт и Ким либо покупали, либо брали книги в аренду в какой-то момент времени. Чтобы понять, как был сгенерирован этот набор результатов, попробуйте выполнить две инструкции SELECT по отдельности: один раз для покупок и один раз для аренды.

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

  1. SELECT customer_name FROM book_purchases;

Следующий вывод будет напечатан на экране:

Output
+---------------+ | customer_name | +---------------+ | sammy | | sammy | | sammy | | bill | | bill | | walt | | walt | | walt | +---------------+ 8 rows in set (0.000 sec)

Сэмми, Билл и Уолт купили книги, а Ким нет.

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

  1. 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, возвращающим один столбец, и другим оператором, возвращающим два столбца:

  1. SELECT purchase_id, customer_name FROM book_purchases
  2. UNION
  3. SELECT customer_name FROM book_leases;

Сервер базы данных ответит ошибкой:

Output
The used SELECT statements have a different number of columns

Выполнение операций UNION над наборами результатов с разным количеством столбцов невозможно.

UNION с несоответствующим порядком столбцов

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

  1. SELECT customer_name, book_title FROM book_purchases
  2. UNION
  3. 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.

Предположим, вы хотите узнать, какие книги Сэмми читает с помощью вашего книжного магазина, путем покупки или аренды. Запустите следующий запрос:

  1. SELECT book_title FROM book_purchases
  2. WHERE customer_name = 'Sammy'
  3. UNION
  4. SELECT book_title FROM book_leases
  5. 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. Упорядочивание выполняется по окончательным объединенным результатам, а не по отдельным запросам.

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

  1. SELECT book_title FROM book_purchases
  2. WHERE customer_name = 'Sammy'
  3. UNION
  4. SELECT book_title FROM book_leases
  5. WHERE customer_name = 'Sammy'
  6. 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 года. Чтобы получить эти названия, вы можете следовать примеру, аналогичному предыдущему:

  1. SELECT book_title FROM book_purchases
  2. WHERE date = '2022-10-01'
  3. UNION
  4. SELECT book_title FROM book_leases
  5. WHERE date_from = '2022-10-01'
  6. 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:

  1. SELECT book_title FROM book_purchases
  2. WHERE date = '2022-10-01'
  3. UNION ALL
  4. SELECT book_title FROM book_leases
  5. WHERE date_from = '2022-10-01'
  6. 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».