SQLГлава 14. Использование предложения UNION
В предшествующих главах мы обсуждали различные способы вложения запросов друг в друга. Имеется другой способ объединения нескольких запросов — формирование их в объединение. В этой главе вы научитесь использовать предложение Объединение нескольких запросов в один
Вы можете поместить несколько запросов вместе и объединить их вывод, используя предложение 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, такие как
Длина строки также может стать проблемой. Большинство программ разрешают поля переменной длины, но они не обязательно будут использоваться с
Другое ограничение на совместимость: пустые значения ( UNION и устранение дубликатов
Оператор 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. В строку =============== 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
До сих пор мы не оговаривали, что данные нескольких запросов будут выводиться в каком-то особом порядке. Мы просто показывали вывод сначала из одного запроса, а затем из другого. Конечно, вы не можете полагаться на вывод, приходящий в произвольном порядке. Мы как раз сделаем так, чтобы этот способ выполнения примеров был более простым. Вы можете использовать предложение 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
Пока Внешнее объединениеОперация, которая бывает часто полезна, это объединение двух запросов, в котором второй запрос выбирает строки, исключённые первым. Наиболее часто вы будете делать это, чтобы не исключать строки, которые не удовлетворили предикату при объединении таблиц. Это называется внешним объединением. Для рассмотрения внешних соединений добавим к таблице 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; Обойти это ограничение позволяет объединение двух запросов следующим образом:
Например: 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 Внешнее объединение с встоенными флагами
Строка 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); Резюме
Теперь вы знаете, как использовать предложение Этим заканчиваются наши главы о запросах. Вы теперь имеете довольно полное представление о поиске данных в SQL. Следующий шаг - изучение того, как значения вводятся в таблицы и как таблицы создаются "с нуля". Как вы увидите, запросы иногда используются внутри других типов команд так же хорошо, как и сами по себе. Работа с SQL
(См. ответы в Приложении A.) |
||