изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

Название	Она позволяет решать многошаговые задачи одним выражением
Дата	09.02.2018
Размер	1.6 Mb.
Формат файла
Имя файла	изучаем SQL.pdf
Тип	Документы #36127
страница	19 из 31

1 ... 15 16 17 18 19 20 21 22 ... 31

Подзапросы, возвращающие несколько строк
и один столбец
Если подзапрос возвращает более одной строки, его нельзя использо вать как одну из частей условия равенства, что и было продемонстри

172
Глава 9. Подзапросы ровано предыдущим примером. Однако есть четыре дополнительных оператора, позволяющие строить условия с подзапросами этих типов.
Оператор in
Нельзя приравнять одно значение набору значений, но можно прове рить наличие этого значения в наборе. Следующий пример, хотя и без подзапроса, показывает, как можно создать условие, использующее оператор in (в) для поиска значения в наборе значений:
mysql> SELECT branch_id, name, city
> FROM branch
> WHERE name IN ('Headquarters', 'Quincy Branch');
+
+
+
+
| branch_id | name | city |
+
+
+
+
| 1 | Headquarters | Waltham |
| 3 | Quincy Branch | Quincy |
+
+
+
+
2 rows in set (0.03 sec)
В левой части условия – выражение (столбец name), а в правой части –
набор строк. Оператор in проверяет, нет ли в столбце name одной из за данных строк. Если есть, условие выполнено, и строка добавляется в ре зультирующий набор. Такие же результаты можно получить и с помо щью двух условий равенства:
mysql> SELECT branch_id, name, city
> FROM branch
> WHERE name = 'Headquarters' OR name = 'Quincy Branch';
+
+
+
+
| branch_id | name | city |
+
+
+
+
| 1 | Headquarters | Waltham |
| 3 | Quincy Branch | Quincy |
+
+
+
+
2 rows in set (0.01 sec)
Для набора только из двух выражений такой подход кажется рацио нальным, но если в наборе десятки (или сотни, тысячи и т. д.) значе ний, очевидный выбор – одно условие с оператором in.
Даже иногда создавая вручную наборы строк, дат или чисел для ис пользования в одной из частей условия, вы все же предпочтете форми ровать набор при выполнении запроса посредством подзапроса, воз вращающего одну или более строк. Следующий запрос использует опе ратор in и подзапрос в правой части условия фильтрации для того, что бы выявить руководящий состав банка:
mysql> SELECT emp_id, fname, lname, title
> FROM employee
> WHERE emp_id IN (SELECT superior_emp_id
> FROM employee);

Несвязанные подзапросы
173
+
+
+
+
+
| emp_id | fname | lname | title |
+
+
+
+
+
| 1 | Michael | Smith | President |
| 3 | Robert | Tyler | Treasurer |
| 4 | Susan | Hawthorne | Operations Manager |
| 6 | Helen | Fleming | Head Teller |
| 10 | Paula | Roberts | Head Teller |
| 13 | John | Blake | Head Teller |
| 16 | Theresa | Markham | Head Teller |
+
+
+
+
+
7 rows in set (0.01 sec)
Подзапрос возвращает ID всех сотрудников, имеющих кого то в под чинении, а основной запрос извлекает для этих сотрудников четыре столбца таблицы employee. Вот результаты подзапроса:
mysql> SELECT superior_emp_id
> FROM employee;
+
+
| superior_emp_id |
+
+
| NULL |
| 1 |
| 1 |
| 3 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 6 |
| 6 |
| 6 |
| 10 |
| 10 |
| 13 |
| 13 |
| 16 |
| 16 |
+
+
18 rows in set (0.00 sec)
Как видите, ID некоторых сотрудников встречаются по нескольку раз,
поскольку у них более одного подчиненного. Это не оказывает нега тивного влияния на результаты основного запроса, потому что совер шенно неважно, сколько раз встречается ID сотрудника в результи рующем наборе подзапроса. Конечно, если вас беспокоят дублирую щие значения в возвращаемой подзапросом таблице, можно добавить в блок select подзапроса ключевое слово distinct, но это никак не отра зится на результирующем наборе основного запроса.

Несвязанные подзапросы
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 полезно увидеть несколько путей достижения одного результата.
Вот еще один пример, требующий применения подзапроса. Скажем,
от клиентов поступило несколько жалоб, связанных с неверными зна чениями в столбцах доступного остатка и отложенного остатка (pending balance) таблицы account. Задача – найти все счета, остатки на которых не соответствуют суммам по операциям для этого счета. Вот частичное решение проблемы:
SELECT 'ALERT! : Account #1 Has Incorrect Balance!'
FROM account
WHERE (avail_balance, pending_balance) <>
(SELECT SUM(),
SUM()
FROM transaction
WHERE account_id = 1)
AND account_id = 1;

Связанные подзапросы
179
Как видите, здесь нет выражений, суммирующих операции для вычис ления доступного и отложенного остатков, но обещаю, что в главе 11,
когда мы научимся создавать выражения case, все будет доработано.
Но даже в таком виде запрос достаточно полон, чтобы увидеть, что подзапрос генерирует две суммы из таблицы transaction, которые по том сравниваются со столбцами avail_balance и pending_balance табли цы account. И подзапрос, и основной запрос включают условие фильт рации account_id = 1. Таким образом, запрос в его теперешней форме будет проверять только по одному счету за раз. В следующем разделе мы научимся создавать более общую форму запроса, которая будет проверять все счета за одно выполнение.
Связанные подзапросы
Все приведенные до сих пор запросы не зависели от своих содержащих выражений, т. е. могли выполняться самостоятельно и представлять свои результаты для проверки. Связанный подзапрос (correlated sub
query
), напротив, зависит от содержащего выражения, из которого он ссылается на один или более столбцов. В отличие от несвязанного под запроса, который выполняется непосредственно перед выполнением содержащего выражения, связанный подзапрос выполняется по разу для каждой строки кандидата (это строки, которые предположитель но могут быть включены в окончательные результаты). Например,
следующий запрос использует связанный подзапрос для подсчета ко личества счетов у каждого клиента. Затем основной запрос выбирает тех клиентов, у которых ровно по два счета:
mysql> SELECT c.cust_id, c.cust_type_cd, c.city
> FROM customer c
> WHERE 2 = (SELECT COUNT(*)
> FROM account a
> WHERE a.cust_id = c.cust_id);
+
+
+
+
| cust_id | cust_type_cd | city |
+
+
+
+
| 2 | I | Woburn |
| 3 | I | Quincy |
| 6 | I | Waltham |
| 8 | I | Salem |
| 10 | B | Salem |
+
+
+
+
5 rows in set (0.01 sec)
Ссылка на c.cust_id в самом конце подзапроса – это то, что делает этот подзапрос связанным. Чтобы подзапрос мог выполняться, основной запрос должен поставлять значения для c.cust_id. В данном случае ос новной запрос извлекает из таблицы customer все 13 строк и выполняет по одному подзапросу для всех клиентов, передавая в него соответст

180
Глава 9. Подзапросы вующий ID клиента при каждом выполнении. Если подзапрос возвра щает значение 2, условие фильтрации выполняется и строка добавля ется в результирующий набор.
Помимо условий равенства связанные подзапросы можно применять в условиях других типов, таких как условие вхождения в диапазон,
проиллюстрированное ниже:
mysql> SELECT c.cust_id, c.cust_type_cd, c.city
> FROM customer c
> WHERE (SELECT SUM(a.avail_balance)
> FROM account a
> WHERE a.cust_id = c.cust_id)
> BETWEEN 5000 AND 10000;
+
+
+
+
| cust_id | cust_type_cd | city |
+
+
+
+
| 4 | I | Waltham |
| 7 | I | Wilmington |
| 11 | B | Wilmington |
+
+
+
+
3 rows in set (0.02 sec)
Этот вариант приведенного ранее запроса находит всех клиентов, чей общий доступный остаток по всем счетам находится в диапазоне от
5000 до 10 000 долларов. И снова связанный подзапрос выполняется
13 раз (по разу для каждой строки), и каждое выполнение подзапроса возвращает общий остаток по счетам данного клиента.
В конце предыдущего раздела было продемонстрировано, как прове рять доступный и отложенный остатки счета по транзакциям, зареги стрированным по данному счету, и я обещал показать, как изменить пример для обработки всех счетов за одно выполнение. Вот тот пример:
SELECT 'ALERT! : Account #1 Has Incorrect Balance!'
FROM account
WHERE (avail_balance, pending_balance) <>
(SELECT SUM(),
SUM()
FROM transaction
WHERE account_id = 1)
AND account_id = 1;
При использовании связанного подзапроса вместо несвязанного основ ной запрос может выполняться всего лишь один раз, а подзапрос будет выполняться для каждого счета. Вот обновленная версия:
SELECT CONCAT('ALERT! : Account #', a.account_id,
' Has Incorrect Balance!')
FROM account a
WHERE (a.avail_balance, a.pending_balance) <>
(SELECT SUM(),
SUM()

Связанные подзапросы

1 ... 15 16 17 18 19 20 21 22 ... 31