alan_beaulieu-learning_sql-ru (865932), страница 21
Текст из файла (страница 21)
Как видно по результатам, четыре строки из таблицы business включены дважды (ID клиентов 10, 11, 12 и 13).Поскольку вряд ли вы когда нибудь дважды включите один и тот жезапрос в составной запрос, вот другой пример составного запроса, покоторому возвращаются дублирующие данные:mysql> SELECT emp_id> FROM employee> WHERE assigned_branch_id = 2> AND (title = 'Teller' OR title = 'Head Teller')> UNION ALL> SELECT DISTINCT open_emp_id> FROM account> WHERE open_branch_id = 2;++| emp_id |++|10 ||11 ||12 ||10 |++4 rows in set (0.01 sec)Первый запрос составного выражения выбирает всех операционистовотделения Woburn, а второй возвращает другое множество – операцио нистов, открывавших счета в отделении Woburn.
Из четырех строк ре Операторы работы с множествами115зультирующего набора одна дублируется (ID сотрудника – 10). Еслибы потребовалось исключить дублирующие строки из составной таб лицы, вместо оператора union all надо было бы использовать операторunion:mysql> SELECT emp_id> FROM employee> WHERE assigned_branch_id = 2> AND (title = 'Teller' OR title = 'Head Teller')> UNION> SELECT DISTINCT open_emp_id> FROM account> WHERE open_branch_id = 2;++| emp_id |++|10 ||11 ||12 |++3 rows in set (0.01 sec)Для данной версии запроса с применением оператора union all в резуль тирующий набор включаются только три разные строки, а не четыре(три разные, одна дублирующаяся).Оператор intersectСпецификация SQL ANSI включает оператор intersect (пересечение),предназначенный для выполнения пересечений.
К сожалению, MySQLверсии 4.1 не реализует оператор intersect. Oracle (но не SQL Server) по зволяет использовать intersect. Однако поскольку для всех примеровданной книги используется MySQL, результирующие наборы для при меров запросов в данном разделе являются вымышленными и не могутбыть получены в MySQL до версии 5.0 включительно. Здесь не показа но приглашение MySQL (mysql>), потому что эти выражения не выпол няются сервером MySQL.Если два запроса составного запроса возвращают неперекрывающиесятаблицы, пересечением будет пустое множество. Рассмотрим следую щий запрос:SELECT emp_id, fname, lnameFROM employeeINTERSECTSELECT cust_id, fname, lnameFROM individual;Empty set (0.04 sec)Первый запрос возвращает ID и имя каждого сотрудника, а второй – IDи имя каждого клиента.
Это абсолютно неперекрывающиеся множест ва, поэтому пересечение двух этих множеств и дает пустое множество.116Глава 6. Работа с множествамиВторой шаг – выявить два запроса, действительно имеющих пере крывающиеся данные, и затем применить оператор intersect. Для это го используем тот же запрос, что и для демонстрации разницы междуunion и union all, только на этот раз возьмем оператор intersect:SELECT emp_idFROM employeeWHERE assigned_branch_id = 2AND (title = 'Teller' OR title = 'Head Teller')INTERSECTSELECT DISTINCT open_emp_idFROM accountWHERE open_branch_id = 2;++| emp_id |++|10 |++1 row in set (0.01 sec)Пересечение этих двух запросов дает сотрудника с ID равным 10, чтоявляется единственным значением, имеющимся в результирующихнаборах обоих запросов.Наряду с оператором intersect, удаляющим все дублирующие строкиобласти перекрытия, спецификация SQL ANSI предлагает оператор intersect all, не удаляющий дубликаты.
Единственный сервер БД, в на стоящее время реализующий оператор intersect all, – DB2 UniversalServer компании IBM.Оператор exceptСпецификация SQL ANSI включает оператор except (разность), пред назначенный для выполнения операции разности. Опять же, к сожа лению, MySQL версии 4.1 не реализует оператор except, поэтому в дан ном разделе действуют те же соглашения, что и в предыдущем.При работе с Oracle Database вам понадобится использовать опе ратор minus (минус), не совместимый со спецификацией ANSI.Операция except возвращает первую таблицу за вычетом всех перекры тий со второй таблицей.
Вот пример из предыдущего раздела, но с опе ратором except вместо intersect:SELECT emp_idFROM employeeWHERE assigned_branch_id = 2AND (title = 'Teller' OR title = 'Head Teller')EXCEPTSELECT DISTINCT open_emp_idFROM accountОператоры работы с множествами117WHERE open_branch_id = 2;++| emp_id |++|11 ||12 |++2 rows in set (0.01 sec)В этом варианте запроса результирующий набор включает три строкииз результирующего набора первого запроса минус сотрудник с ID,равным 10, который присутствует в результирующих наборах обоихзапросов. В спецификации SQL ANSI также описан оператор except all,но опять же он реализован только в DB2 Universal Server IBM.В операторе except all есть небольшая хитрость. Вот пример, показы вающий, как обрабатываются дублирующие данные.
Скажем, есть двамножества данных, имеющих следующий вид:Множество A++| emp_id |++|10 ||11 ||12 ||10 ||10 |++Множество B++| emp_id |++|10 ||10 |++В результате операции A except B получаем следующее:++| emp_id |++|11 ||12 |++Если изменить операцию и применить A except all B, увидим следующее:++| emp_id |++|10 ||11 |118Глава 6. Работа с множествами|12 |++Следовательно, разница между этими двумя операциями в том, что except удаляет все экземпляры дублирующихся данных из множества А,тогда как except all удаляет из множества А только один экземпляр дуб ликата данных для каждого экземпляра дубликата данных множества В.Правила операций с множествамиВ следующих разделах обозначены некоторые правила, которых необ ходимо придерживаться при работе с составными запросами.Результаты сортирующего составного запросаЕсли требуется сортировать результаты составного запроса, после по следнего входящего в него запроса можно добавить блок order by.
В бло ке order by указываются имена столбцов из первого запроса составногозапроса. До сих пор в каждом примере главы имена столбцов в обоихзапросах составного запроса совпадали, но так делать не обязательно,что и показывает следующий пример:mysql> SELECT emp_id, assigned_branch_id> FROM employee> WHERE title = 'Teller'> UNION> SELECT open_emp_id, open_branch_id> FROM account> WHERE product_cd = 'SAV'> ORDER BY emp_id;+++| emp_id | assigned_branch_id |+++|1 |1 ||7 |1 ||8 |1 ||9 |1 ||10 |2 ||11 |2 ||12 |2 ||14 |3 ||15 |3 ||16 |4 ||17 |4 ||18 |4 |+++12 rows in set (0.04 sec)В этом примере в двух запросах заданы разные имена столбцов. Еслив блоке order by указать имя столбца из второго запроса, будет получе на следующая ошибка:Правила операций с множествами119mysql> SELECT emp_id, assigned_branch_id> FROM employee> WHERE title = 'Teller'> UNION> SELECT open_emp_id, open_branch_id> FROM account> WHERE product_cd = 'SAV'> ORDER BY open_emp_id;ERROR 1054 (42S22): Unknown column 'open_emp_id' in 'order clause'Чтобы избежать этой проблемы, рекомендуется в обоих запросах да вать столбцам одинаковые псевдонимы.Старшинство операций с множествамиЕсли в составном запросе больше двух запросов, использующих разныеоператоры работы с множествами, то для обеспечения желаемых ре зультатов следует продумать порядок расположения этих запросов в со ставном выражении.
Рассмотрим следующее составное выражение изтрех запросов:mysql> SELECT cust_id> FROM account> WHERE product_cd IN ('SAV', 'MM')> UNION ALL> SELECT a.cust_id> FROM account a INNER JOIN branch b> ON a.open_branch_id = b.branch_id> WHERE b.name = 'Woburn Branch'> UNION> SELECT cust_id> FROM account> WHERE avail_balance BETWEEN 500 AND 2500;++| cust_id |++|1 ||2 ||3 ||4 ||8 ||9 ||7 ||11 ||5 |++9 rows in set (0.00 sec)Этот составной запрос включает три запроса, возвращающих набор не уникальных ID клиентов.
Первые два запроса разделены операторомunion all, а второй и третий – оператором union. Может показаться, чторасположение операторов union и union all не играет роли, но на самом120Глава 6. Работа с множествамиделе разница есть. Вот тот же составной запрос, в котором эти операто ры поменялись местами:mysql> SELECT cust_id> FROM account> WHERE product_cd IN ('SAV', 'MM')> UNION> SELECT a.cust_id> FROM account a INNER JOIN branch b> ON a.open_branch_id = b.branch_id> WHERE b.name = 'Woburn Branch'> UNION ALL> SELECT cust_id> FROM account> WHERE avail_balance BETWEEN 500 AND 2500;++| cust_id |++|1 ||2 ||3 ||4 ||8 ||9 ||7 ||11 ||1 ||1 ||2 ||3 ||3 ||4 ||4 ||5 ||9 |++17 rows in set (0.00 sec)При виде результатов становится очевидным, что порядок разных опе раторов работы с множествами в составном запросе действительноимеет значение.
В общем, составные запросы из трех или больше за просов оцениваются в порядке сверху вниз, но с учетом следующих по яснений:• По спецификации SQL ANSI из всех операторов работы с множест вами первым выполняется оператор intersect.• Порядок сочетания запросов можно задавать с помощью скобок.Но поскольку в MySQL еще не реализованы ни оператор intersect, нискобки в составных запросах, для получения нужного результата при дется аккуратно расставлять запросы, образующие составной запрос.При использовании другого сервера БД, для переопределения порядкаУпражнения121обработки составных запросов по умолчанию (сверху вниз) запросы,расположенные рядом, можно заключить в скобки:(SELECT cust_idFROM accountWHERE product_cd IN ('SAV', 'MM')UNION ALLSELECT a.cust_idFROM account a INNER JOIN branch bON a.open_branch_id = b.branch_idWHERE b.name = 'Woburn Branch')INTERSECT(SELECT cust_idFROM accountWHERE avail_balance BETWEEN 500 AND 2500EXCEPTSELECT cust_idFROM accountWHERE product_cd = 'CD'AND avail_balance < 1000);Для этого составного запроса первый и второй запросы комбинируют ся оператором union all, затем третий и четвертый запросы – операто ром except, и, наконец, для формирования окончательного результи рующего набора результаты этих двух операций комбинируются с по мощью оператора intersect.УпражненияСледующие упражнения призваны протестировать понимание опера ций с множествами.
Ответы на эти упражнения приведены в приложе нии С.6.1Имеются множество A = {L M N O P} и множество B = {P Q R S T}. Какие мно жества будут получены в результате следующих операций:••••A union BA union all BA intersect BA except B6.2Напишите составной запрос для выбора имен и фамилий всех клиен тов физических лиц, а также имен и фамилий всех сотрудников.6.3Отсортируйте результаты упражнения 6.2 по столбцу lname.Создание, преобразованиеи работа с даннымиКак говорилось в предисловии, цель данной книги – показать универ сальные методы SQL, применяемые на разных серверах БД.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
