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

SQL

Глава 14. Использование предложения UNION

В предшествующих главах мы обсуждали различные способы вложения запросов друг в друга. Имеется другой способ объединения нескольких запросов — формирование их в объединение. В этой главе вы научитесь использовать предложение UNION в SQL. UNION отличается от подзапросов тем, что в нём ни один из двух (или более) запросов не управляется другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их объединяется.

Объединение нескольких запросов в один

Вы можете поместить несколько запросов вместе и объединить их вывод, используя предложение UNION. Предложение UNION объединяет вывод двух или более SQL-запросов в единый набор строк и столбцов. Например, чтобы получить всех продавцов и заказчиков, размещённых в Лондоне, и вывести их как единое целое, вы могли бы ввести:

SELECT snum, sname
   FROM Salespeople
   WHERE city = 'London'
UNION
SELECT cnum, cname
   FROM Customers
   WHERE city = 'London';

и получить вывод, показанный в Рисунке 14.1.

Как видите, столбцы, выбранные двумя командами, выведены так, как если бы это была одна команда. Заголовки столбца исключены, потому что ни один из столбцов, выведённых объединением, не был извлечён непосредственно из одной таблицы. Следовательно, все эти столбцы вывода не имеют никаких имён (смотрите Главу 7, в которой обсуждается вывод столбцов).

Кроме того, обратите внимание, что только последний запрос заканчивается точкой с запятой.

Отсутствие точки с запятой дает понять SQL, что имеется ещё один или более запросов.

                
   ===============  SQL Execution Log ============
  |                                               |
  | SELECT snum, sname                            |
  | FROM  Salespeople                             |
  | WHERE city = 'London'                         |
  | UNION                                         |
  | SELECT cnum, cname                            |
  | FROM Customers                                |
  | WHERE city = 'London';                        |
  | ============================================= |
  |                                               |
  |  -----    --------                            |
  |   1001    Peel                                |
  |   1004    Motika                              |
  |   2001    Hoffman                             |
  |   2006    Climens                             |
  |                                               |
   ===============================================

   Рисунок 14.1 Формирование объединения из двух запросов

Когла вы можете делать объединение запросов?

Когда два (или более) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое количество столбцов и в том же порядке и каждый должен иметь тип, совместимый с каждым. Значение совместимости типов меняется. ANSI следит за этим очень строго, и поэтому числовые поля должны иметь одинаковый числовой тип и размер, хотя некоторые имена, используемые ANSI для этих типов, являются синонимами. (Смотрите в Приложении B подробности о числовых типах ANSI.) Кроме того, символьные поля должны иметь одинаковое количество символов (значение предназначенного числа — не обязательно такое же, как используемое число).

Хорошо, что некоторые SQL-программы обладают большей гибкостью, чем это определяется ANSI. Типы, не определённые ANSI, такие как DATA и BINARY, обычно должны совпадать с другими столбцами такого же нестандартного типа.

Длина строки также может стать проблемой. Большинство программ разрешают поля переменной длины, но они не обязательно будут использоваться с UNION. С другой стороны, некоторые программы (и ANSI тоже) требуют, чтобы символьные поля были точно одинаковой длины. В этих вопросах вы должны проконсультироваться с документацией вашей собственной программы.

Другое ограничение на совместимость: пустые значения (NULL) запрещены в любом столбце объединения, причем эти значения необходимо запретить и для всех соответствующих столбцов в других запросах объединения. Пустые значения (NULL) запрещены ограничением NOT NULL, которое будет обсуждаться в Главе 18. Кроме того, вы не можете использовать UNION в подзапросах, а также не можете использовать агрегатные функции в предложении SELECT-запроса в объединении. (Большинство программ пренебрегают этими ограничениями.)

UNION и устранение дубликатов

Оператор UNION автоматически исключает повторяющиеся строки из выходных данных, тогда как в одиночных запросах для этого необходимо использовать DISTINCT. Например, в следующем запросе (см. рис. 14.2) комбинация значений 1001 и London присутствует дважды, поскольку мы не давали указания SQL исключать дублирование:

                 
SELECT snum, city
   FROM Customers;
   
   ===============  SQL Execution Log ============
  |                                               |
  | SELECT snum, city                             |
  | FROM Customers;                               |
  | ============================================= |
  |  snum     city                                |
  |  -----    --------                            |
  |   1001    London                              |
  |   1003    Rome                                |
  |   1002    San Jose                            |
  |   1002    Berlin                              |
  |   1001    London                              |
  |   1004    Rome                                |
  |   1007    San Jose                            |
   ===============================================

   Рисунок 14.2 Одиночный запрос с дублированным выводом

Но если объединить этот запрос с аналогичным к таблице Salespeople, используя UNION, то избыточная информация будет отсутствовать. На рис. 14.3 показан результат следующего запроса:

             
SELECT snum, city
   FROM Customers
UNION
SELECT snum, city
   FROM Salespeople;

   ===============  SQL Execution Log ============
  |                                               |
  | SELECT snum, city                             |
  | FROM Customers;                               |
  | ============================================= |
  |  snum     city                                |
  |  -----    --------                            |
  |   1001    London                              |
  |   1003    Rome                                |
  |   1002    San Jose                            |
  |   1002    Berlin                              |
  |   1004    Rome                                |
  |   1007    San Jose                            |
  |   1003    New York                            |
  |   1004    London                              |
  |   1007    Barcelona                           |
   ===============================================

   Рисунок 14.3 UNION устраняет вывод дубликатов

Можно отменить автоматическое исключение дублирования, указав UNION ALL вместо UNION:

             
SELECT snum, city
   FROM Customers
UNION ALL
SELECT snum, city
   FROM Salespeople;

Использование строк и выражений с UNION

В главе 7 были показаны способы размещения констант среди выходных столбцов запроса. Это допустимо и при использовании UNION, но константы и выражения должны удовлетворять требованиям UNION-совместимости, описанным выше. Такая возможность полезна, например, для добавления комментариев, показывающих, какой запрос вывел данную строку. Разрешается также объединять выходные столбцы с выражениями (при условии их UNION-совместимости).

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

SELECT a.snum, sname, onum, 'Highest on', odate
   FROM Salespeople a, Orders b
   WHERE a.snum = b.snum
   AND b.amt =
      (SELECT MAX(amt)
          FROM Orders c
          WHERE c.odate = b.odate)
UNION
SELECT a.snum, sname, onum, 'Lowest  on', odate
   FROM Salespeople a, Orders b
   WHERE a.snum = b.snum
   AND b.amt =
      (SELECT  MIN(amt)
          FROM Orders c
          WHERE c.odate = b.odate);

Результат работы этой команды показан на рис. 14.4. В строку 'Lowest  on' пришлось добавить лишний пробел, чтобы она совпадала по длине со строкой 'Highest on'. В большинстве реализаций выравнивание по длине не требуется, но оно было частью стандарта SQL89, поэтому не исключено, что вам придется делать это. Обратите также внимание, что максимальный и минимальный заказы от 5 октября оказались у продавца Peel (фактически, это единственный заказ в тот день). Поскольку в двух запросах вставляется разный текст, строки не считаются идентичными.

                
   ===============  SQL Execution Log ============
  |                                               |
  | AND b.amt =                                   | 
  | (SELECT min (amt)                             |
  | FROM Orders c                                 |
  | WHERE c.odate = b.odate);                     |
  | ============================================= |
  |                                               |
  | -----  ------- ------  ---------- ----------- |
  |  1001  Peel     3008   Highest on  10/05/1990 |
  |  1001  Peel     3008   Lowest  on  10/05/1990 |
  |  1001  Peel     3011   Highest on  10/06/1990 |
  |  1002  Serres   3005   Highest on  10/03/1990 |
  |  1002  Serres   3007   Lowest  on  10/04/1990 |
  |  1002  Serres   3010   Lowest  on  10/06/1990 |
  |  1003  Axelrod  3009   Highest on  10/04/1990 |
  |  1007  Rifkin   3001   Lowest  on  10/03/1990 |
   ===============================================

   Рисунок 14.4 Вывод максимального и минимального
                заказов с поясняющим текстом

Использование UNION с ORDER BY

До сих пор мы не оговаривали, что данные нескольких запросов будут выводиться в каком-то особом порядке. Мы просто показывали вывод сначала из одного запроса, а затем из другого. Конечно, вы не можете полагаться на вывод, приходящий в произвольном порядке. Мы как раз сделаем так, чтобы этот способ выполнения примеров был более простым. Вы можете использовать предложение ORDER BY, чтобы упорядочить вывод из объединения, точно так же, как это делается в индивидуальных запросах. Давайте пересмотрим наш последний пример, чтобы упорядочить имена с помощью их порядковых номеров. Это может внести противоречие, такое как повторение имени Peel в последней команде, как вы сможете увидеть из вывода показанного в Рисунке 14.5.

SELECT a.snum, sname, onum, 'Highest on', odate
   FROM Salespeople a, Orders b
   WHERE a.snum = b.snum
   AND b.amt =
      (SELECT MAX(amt)
          FROM Orders c
          WHERE c.odate = b.odate)
UNION
SELECT a.snum, sname, onum, 'Lowest  on', odate
   FROM Salespeople a, Orders b
   WHEREa.snum = b.snu 
   AND b.amt =
      (SELECT MIN(amt)
          FROM Orders c
          WHERE c.odate = b.odate)
ORDER BY 3;

   ===============  SQL Execution Log ============
  |                                               |
  | (SELECT min (amt)                             |
  | FROM Orders c                                 |
  | WHERE c.odate = b.odate)                      |
  | ORDER BY 3;                                   |
  | ============================================= |
  |                                               |
  | -----  ------- ------  ---------- ----------- |
  |  1007  Rifkin   3001   Lowest  on  10/03/1990 |
  |  1002  Serres   3005   Highest on  10/03/1990 |
  |  1002  Serres   3007   Lowest  on  10/04/1990 |
  |  1001  Peel     3008   Highest on  10/05/1990 |
  |  1001  Peel     3008   Lowest  on  10/05/1990 |
  |  1003  Axelrod  3009   Highest on  10/04/1990 |
  |  1002  Serres   3010   Lowest  on  10/06/1990 |
  |  1001  Peel     3011   Highest on  10/06/1990 |
   ===============================================

   Рисунок 14.5 Формирование объединения с использованием 
                ORDER BY

Пока ORDER BY используется по умолчанию, мы не должны его указывать. Мы можем упорядочить наш вывод с помощью нескольких полей, одно внутри другого, и указать ASC или DESC для каждого, точно так же, как мы делали это для одиночных запросов. Заметьте, что номер 3 в предложении ORDER BY указывает, какой столбец из предложения SELECT будет упорядочен. Так как столбцы объединения это столбцы вывода, они не имеют имён, и, следовательно, должны определяться по номеру. Этот номер указывает их место среди других столбцов вывода. (Смотрите Главу 7, обсуждающую столбцы вывода.)

Внешнее объединение

Операция, которая бывает часто полезна, это объединение двух запросов, в котором второй запрос выбирает строки, исключённые первым. Наиболее часто вы будете делать это, чтобы не исключать строки, которые не удовлетворили предикату при объединении таблиц. Это называется внешним объединением.

Для рассмотрения внешних соединений добавим к таблице Salespeople еще одну строку:

Snum   Sname   City      Comm
-----------------------------
1020   Wang    Bangkok   0.11

Для этого продавца нет заказов в таблице Orders. При просмотре списка продавцов вместе с их текущими заказами вы не увидете в нем Wang:

SELECT s.snum, sname, onum
   FROM Salespeople s, Orders o
   WHERE s.snum = o.snum;

Обойти это ограничение позволяет объединение двух запросов следующим образом:

  • Один запрос ищет все заказы для тех продавцов, которые их имеют.
  • Другой запрос ищет всех продавцов, не имеющих заказов, и выводит сведения об этих продавцах с NULL-значением в столбце onum.

Например:

SELECT s.snum, sname, onum
   FROM Salespeople s, Orders o
   WHERE s.snum = o.snum
UNION
SELECT snum, sname, NULL
   FROM Salespeople s
   WHERE NOT EXISTS
      (SELECT *
	      FROM Orders o
		  WHERE s.snum = o.snum)
ORDER BY 1;

Разрешается вставлять в UNION текстовые комментарии для идентификации запроса, который вывел данную строку. Использование этой техники во внешних соединениях и подобных им конструкциях позволяет применять предикаты для классификации, а не для исключения выходных данных.

Ранее уже приводился пример поиска продавцов с покупателями, находящимися в тех же городах. Теперь предположим, что требуется список всех продавцов с указанием, кто из них имеет, а кто не имеет покупателей в своем городе. Эту задачу решает следующий запрос (см. рис. 14.6):

     
SELECT Salespeople.snum, sname, cname, comm
   FROM Salespeople, Customers
   WHERE Salespeople.city = Customers.city
UNION
SELECT snum, sname, ' NO MATCH ', comm
   FROM Salespeople
   WHERE NOT city = ANY
	  (SELECT city
		  FROM Customers)
ORDER BY 2 DESC;

   ===============  SQL Execution Log ============
  |                                               |
  | FROM Salespeople                              |
  | WHERE NOT city = ANY                          |
  | (SELECT city                                  |
  | FROM Customers)                               |
  | ORDER BY 2 DESC;                              |
  | ============================================= |
  |                                               |
  | -----  -------  ---------     ------------    |
  |  1002  Serres   Cisneros           0.1300     |
  |  1002  Serres   Liu                0.1300     |
  |  1007  Rifkin   NO MATCH           0.1500     |
  |  1001  Peel     Clemens            0.1200     |
  |  1001  Peel     Hoffman            0.1200     |
  |  1004  Motika   Clemens            0.1100     |
  |  1004  Motika   Hoffman            0.1100     |
  |  1003  Axelrod  NO MATCH           0.1000     |
   ===============================================

   Рисунок 14.6 Внешнее объединение с встоенными флагами

Строка ' NO MATCH ' была дополнена пробелами, чтобы получить совпадение поля cname по длине (не во всех реализациях SQL). Второй запрос выбирает даже те строки, которые исключил первый. Вы можете также добавить комментарий или выражение в ваш запрос в виде дополнительного поля. Вот запрос, который добавляет строки к выбранным полям и указывает, имеет ли данный продавец покупателей в своем городе:

  
SELECT a.snum, sname, a.city, 'MATCHED '
   FROM Salespeople a, Customers b
   WHERE a.city = b.city
UNION
SELECT snum, sname, city, 'NO MATCH'
   FROM Salespeople 
   WHERE city NOT IN
	  (SELECT city
	      FROM Customers)
ORDER BY 2 DESC;

Рисунок 14.7 показывает вывод этого запроса.

	
   ===============  SQL Execution Log ============
  |                                               |
  | FROM Salespeople                              |
  | WHERE NOT city = ANY                          |
  | (SELECT city                                  |
  | FROM Customers)                               |
  | ORDER BY 2 DESC;                              |
  | ============================================= |
  |                                               |
  | -----  -------   ------------  ---------      |
  |  1002  Serres     San Jose     MATCHED        |
  |  1007  Rifkin     Barselona    NO MATCH       |
  |  1001  Peel       London       MATCHED        |
  |  1004  Motika     London       MATCHED        |
  |  1003  Axelrod    New York     NO MATCH       |
   ===============================================

   Рисунок 14.7 Внешнее объединение с полем комментария

Все предыдущие внешние соединения, реализованные с помощью UNION, являются левыми, а не полными внешними соединениями. Выполним полное внешнее соединение таблиц Salespeople и Customers по столбцу city. Оно будет содержать:

  • Всех продавцов, которые имеют как минимум одного покупателя, находящегося в том же городе
  • Всех покупателей, которые имеют как минимум одного продавца, находящегося в том же городе
  • Всех продавцов, не имеющих таких покупателей
  • Всех покупателей, не имеющих таких продавцов

Результат запроса показан на рис. 14.8. Обратите внимание на скобки, их назначение объясняется ниже.

(SELECT snum, city, 'SALESPERSON - MATCH'
   FROM Salespeople
   WHERE city = ANY
      (SELECT city
          FROM Customers)
UNION
SELECT snum, city, 'SALESPERSON - NO MATCH'
   FROM Salespeople
   WHERE city NOT IN
      (SELECT city
          FROM Customers))
 
UNION
 
(SELECT cnum, city, 'CUSTOMER - MATCHED'
   FROM Customers
   WHERE city = ANY
      (SELECT city
          FROM Salespeople)
UNION
SELECT cnum, city, 'CUSTOMER - NO MATCH'
   FROM Customers
   WHERE city NOT IN

      (SELECT city
          FROM Salespeople))
 
ORDER BY 2 DESC;

   ===============  SQL Execution Log ===============
  |                                                  |
  | FROM  Salespeople)                               |
  | ORDER BY 2 DESC;                                 |
  |                                                  |
  | ================================================ |
  |                                                  |
  |  ----   --------     ------------------------    |
  |  2003   San Jose     CUSTOMER     -   MATCHED    |
  |  2008   San Jose     CUSTOMER     -   MATCHED    |
  |  2002   Rome         CUSTOMER     -   NO MATCH   |
  |  2007   Rome         CUSTOMER     -   NO MATCH   |
  |  1003   New York     SALESPERSON  -   MATCHED    |
  |  1003   New York     SALESPERSON  -   NO MATCH   |
  |  2001   London       CUSTOMER     -   MATCHED    |
  |  2006   London       CUSTOMER     -   MATCHED    |
  |  2004   Berlin       CUSTOMER     -   NO MATCH   |
  |  1007   Barcelona    SALESPERSON  -   MATCHED    |
  |  1007   Barcelona    SALESPERSON  -   NO MATCH   |
  |                                                  |
   ==================================================

   Рисунок 14.8 Полное внешнее объединение

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

 query X UNION query Y UNION query Z;

можно указать либо

 (query X UNION query Y)UNION query Z;

либо

 query X UNION (query Y UNION query Z);

Для исключения некоторых повторов при сохранении других можно комбинировать UNION и UNION ALL. Так, при наличии одинаковых строк оператор

 (query X UNION ALL query Y)UNION query Z;

не обязательно даст тот же результат, что и оператор

 query X UNION ALL(query Y UNION query Z);

Резюме

Теперь вы знаете, как использовать предложение UNION, которое даёт возможность объединять любое число запросов в единое тело вывода. Если вы имеете ряд подобных таблиц — таблиц, содержащих похожую информацию, но принадлежащих разным пользователям и охватывающих различные особенности, — возможно, что объединение сможет обеспечить простой способ для слияния и упорядочивания вывода. Аналогично внешние объединения дают вам новый способ использования условий не для исключения вывода, а для его маркировки или обработки его частей, когда встречается условие, отличающееся от того, которое не выполняется.

Этим заканчиваются наши главы о запросах. Вы теперь имеете довольно полное представление о поиске данных в SQL. Следующий шаг - изучение того, как значения вводятся в таблицы и как таблицы создаются "с нуля". Как вы увидите, запросы иногда используются внутри других типов команд так же хорошо, как и сами по себе.

Работа с SQL

  1. Создайте объединение из двух запросов, которое показало бы имена, города и оценки всех заказчиков. Те из них, которые имеют поле rating=200 и более, должны, кроме того, иметь слова — "Высокий рейтинг", а остальные должны иметь слова "Низкий рейтинг".
  2. Напишите команду, которая вывела бы имена и номера каждого продавца и каждого заказчика, которые имеют больше чем один текущий заказ. Результат представьте в алфавитном порядке.
  3. Сформируйте объединение из трех запросов. Первый выбирает поля snum всех продавцов в San Jose; второй, поля cnum всех заказчиков в San Jose; и третий — поля onum всех заказов на 3 октября. Сохраните дубликаты между последними двумя запросами, но устраните любую избыточность вывода между каждым из них и самым первым.
    (Примечание: в данных типовых таблицах не содержится никакой избыточности. Это только пример.)

(См. ответы в Приложении A.)