alan_beaulieu-learning_sql-ru (865932), страница 31
Текст из файла (страница 31)
ПодзапросыМожно проверять не только наличие значения в наборе значений, нои его отсутствие. Делается это с помощью оператора not in (нет в). Вотдругой вариант предыдущего запроса с оператором not in вместо in:mysql> SELECT emp_id, fname, lname, title> FROM employee> WHERE emp_id NOT IN (SELECT superior_emp_id> FROM employee> WHERE superior_emp_id IS NOT NULL);+++++| emp_id | fname| lname| title|+++++|2 | Susan| Barker | Vice President ||5 | John| Gooding | Loan Manager ||7 | Chris| Tucker | Teller||8 | Sarah| Parker | Teller||9 | Jane| Grossman | Teller||11 | Thomas | Ziegler | Teller||12 | Samantha | Jameson | Teller||14 | Cindy| Mason| Teller||15 | Frank| Portman | Teller||17 | Beth| Fowler | Teller||18 | Rick| Tulman | Teller|+++++11 rows in set (0.00 sec)Этот запрос находит всех сотрудников, которые никем не руководят.Здесь потребовалось добавить в подзапрос условие фильтрации, чтобыгарантировать отсутствие значений null в возвращаемой подзапросомтаблице.
В следующем разделе объясняется, почему в данном случаепонадобился этот фильтр.Оператор allОператор in используется для поиска выражения в наборе выражений,а оператор all (все) позволяет проводить сравнение одиночного значе ния с каждым значением набора. Для построения такого условия, по мимо оператора all, понадобится один из операторов сравнения (=, <>,<, > и т. д.).
Например, следующий запрос находит всех сотрудников,ID которых не равен ни одному из ID руководителей:mysql> SELECT emp_id, fname, lname, title> FROM employee> WHERE emp_id <> ALL (SELECT superior_emp_id> FROM employee> WHERE superior_emp_id IS NOT NULL);+++++| emp_id | fname| lname| title|+++++|2 | Susan| Barker | Vice President ||5 | John| Gooding | Loan Manager |Несвязанные подзапросы175|7 | Chris| Tucker | Teller||8 | Sarah| Parker | Teller||9 | Jane| Grossman | Teller||11 | Thomas | Ziegler | Teller||12 | Samantha | Jameson | Teller||14 | Cindy| Mason| Teller||15 | Frank| Portman | Teller||17 | Beth| Fowler | Teller||18 | Rick| Tulman | Teller|+++++11 rows in set (0.05 sec)И опять подзапрос возвращает набор ID сотрудников, имеющих подчи ненных.
Основной запрос возвращает данные для всех сотрудников,ID которых не равны ни одному возвращенному подзапросом ID. Ина че говоря, запрос находит всех сотрудников «неруководителей». Еслиэтот подход кажется вам несколько топорным, вы не одиноки; многиепредпочли бы построить запрос по другому, обойдясь без оператораall. Например, результаты этого запроса аналогичны последнему при меру с оператором not in из предыдущего раздела. Дело вкуса, но, ду маю, что этим многим версия с not in просто кажется более понятной.Сравнивать значения с набором значений с помощью операто ров not in или <> all нужно аккуратно, убедившись, что в наборенет значения null. Сервер приравнивает значение из левой час ти выражения к каждому члену набора, и любая попытка при равнять значение к null дает в результате unknown.
Таким обра зом, следующий запрос возвратит пустой набор:mysql> SELECT emp_id, fname, lname, title> FROM employee> WHERE emp_id NOT IN (1, 2, NULL);Empty set (0.00 sec)Бывают случаи, когда оператор all чуть более естественен. Следую щий пример использует all для поиска счетов, доступный остаток ко торых меньше, чем на любом из счетов Фрэнка Такера (Frank Tucker):mysql> SELECT account_id, cust_id, product_cd, avail_balance> FROM account> WHERE avail_balance < ALL (SELECT a.avail_balance> FROM account a INNER JOIN individual i>ON a.cust_id = i.cust_id> WHERE i.fname = 'Frank' AND i.lname = 'Tucker');+++++| account_id | cust_id | product_cd | avail_balance |+++++|2 |1 | SAV|500.00 ||5 |2 | SAV|200.00 ||8 |4 | CHK|534.12 ||9 |4 | SAV|767.77 ||12 |6 | CHK|122.37 |176Глава 9.
Подзапросы|16 |8 | SAV|387.99 ||17 |9 | CHK|125.67 ||21 |10 | BUS|0.00 |+++++8 rows in set (0.01 sec)Вот таблица, возвращенная подзапросом. Она включает доступные ос татки всех счетов Фрэнка:mysql> SELECT a.avail_balance> FROM account a INNER JOIN individual i> ON a.cust_id = i.cust_id> WHERE i.fname = 'Frank' AND i.lname = 'Tucker';++| avail_balance |++|1057.75 ||2212.50 |++2 rows in set (0.01 sec)У Фрэнка два счета, минимальный остаток – 1057,75 долларов. Основ ной запрос находит все счета, остаток на которых меньше, чем на лю бом из счетов Фрэнка. Таким образом, результирующий набор вклю чает все счета, остаток на которых меньше 1057,75 долларов.Оператор anyКак и оператор all, оператор any (любой) обеспечивает возможностьсравнивать значение с элементами набора значений.
Однако, в отли чие от all, условие, использующее оператор any, истинно (true), еслиесть хотя бы одно совпадение, тогда как при использовании оператораall требуется, чтобы условие выполнялось для всех элементов набора.Например, требуется найти все счета, доступный остаток которыхбольше, чем на любом из счетов Фрэнка Такера:mysql> SELECT account_id, cust_id, product_cd, avail_balance> FROM account> WHERE avail_balance > ANY (SELECT a.avail_balance> FROM account a INNER JOIN individual i>ON a.cust_id = i.cust_id> WHERE i.fname = 'Frank' AND i.lname = 'Tucker');+++++| account_id | cust_id | product_cd | avail_balance |+++++|3 |1 | CD|3000.00 ||4 |2 | CHK|2258.02 ||7 |3 | MM|2212.50 ||10 |4 | MM|5487.09 ||11 |5 | CHK|2237.97 ||13 |6 | CD|10000.00 ||14 |7 | CD|5000.00 |Несвязанные подзапросы177|15 |8 | CHK|3487.19 ||18 |9 | MM|9345.55 ||19 |9 | CD|1500.00 ||20 |10 | CHK|23575.12 ||22 |11 | BUS|9345.55 ||23 |12 | CHK|38552.05 ||24 |13 | SBL|50000.00 |+++++14 rows in set (0.01 sec)У Фрэнка два счета с остатками 1057,75 и 2212,50 долларов.
Чтобы ос таток был больше, чем на любом из этих двух счетов, на счете должнобыть, по крайней мере, 1057,75 долларов.Операторы = any и in эквивалентны, хотя многие предпочитаютоператор in.Подзапросы, возвращающие несколько столбцовДо сих пор все примеры подзапросов в данной главе возвращали одинстолбец и одну или более строк. Однако в определенных ситуацияхможно использовать подзапросы, возвращающие два или более столб цов. Чтобы лучше разобраться в подзапросах, возвращающих несколь ко столбцов, полезно сначала взглянуть на пример использования не скольких подзапросов, возвращающих один столбец:mysql> SELECT account_id, product_cd, cust_id> FROM account> WHERE open_branch_id = (SELECT branch_id> FROM branch> WHERE name = 'Woburn Branch')> AND open_emp_id IN (SELECT emp_id> FROM employee> WHERE title = 'Teller' OR title = 'Head Teller');++++| account_id | product_cd | cust_id |++++|1 | CHK|1 ||2 | SAV|1 ||3 | CD|1 ||4 | CHK|2 ||5 | SAV|2 ||14 | CD|7 ||22 | BUS|11 |++++7 rows in set (0.00 sec)Чтобы выявить ID отделения Woburn и ID всех банковских операцио нистов, этот запрос использует два подзапроса.
Затем содержащий за прос использует эту информацию для выбора всех текущих счетов, от 178Глава 9. Подзапросыкрытых старшим операционистом в отделении Woburn. Но в таблицеemployee есть информация об отделении, в котором числится каждый со трудник, поэтому те же результаты можно получить путем сравнениястолбцов account.open_branch_id и account.open_emp_id с единственнымподзапросом к таблицам employee и branch. Для этого в условии фильтра ции следует указать в круглых скобках имена обоих столбцов таблицыaccount в том же порядке, в каком они возвращаются подзапросом:mysql> SELECT account_id, product_cd, cust_id> FROM account> WHERE (open_branch_id, open_emp_id) IN> (SELECT b.branch_id, e.emp_id> FROM branch b INNER JOIN employee e>ON b.branch_id = e.assigned_branch_id> WHERE b.name = 'Woburn Branch'>AND (e.title = 'Teller' OR e.title = 'Head Teller'));++++| account_id | product_cd | cust_id |++++|1 | CHK|1 ||2 | SAV|1 ||3 | CD|1 ||4 | CHK|2 ||5 | SAV|2 ||14 | CD|7 ||22 | BUS|11 |++++7 rows in set (0.00 sec)Эта версия запроса делает то же самое, что и предыдущий пример, нос помощью всего одного подзапроса, который возвращает два столбца,а не двух подзапросов, возвращающих по одному столбцу.Конечно, можно было бы переписать предыдущий пример, просто со единив три таблицы, без этой возни с подзапросами.
Но при изученииSQL полезно увидеть несколько путей достижения одного результата.Вот еще один пример, требующий применения подзапроса. Скажем,от клиентов поступило несколько жалоб, связанных с неверными зна чениями в столбцах доступного остатка и отложенного остатка (pendingbalance) таблицы account. Задача – найти все счета, остатки на которыхне соответствуют суммам по операциям для этого счета.
Вот частичноерешение проблемы:SELECT 'ALERT! : Account #1 Has Incorrect Balance!'FROM accountWHERE (avail_balance, pending_balance) <>(SELECT SUM(<expression to generate available balance>),SUM(<expression to generate pending balance>)FROM transactionWHERE account_id = 1)AND account_id = 1;Связанные подзапросы179Как видите, здесь нет выражений, суммирующих операции для вычис ления доступного и отложенного остатков, но обещаю, что в главе 11,когда мы научимся создавать выражения case, все будет доработано.Но даже в таком виде запрос достаточно полон, чтобы увидеть, чтоподзапрос генерирует две суммы из таблицы transaction, которые по том сравниваются со столбцами avail_balance и pending_balance табли цы account.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
