alan_beaulieu-learning_sql-ru (865932), страница 30
Текст из файла (страница 30)
е. после завершения выполнения выражения сервер высво бождает всю память, отведенную под результаты подзапроса).Предыдущие главы уже содержали несколько примеров подзапросов.Для начала приведем простой пример:mysql> SELECT account_id, product_cd, cust_id, avail_balance> FROM account> WHERE account_id = (SELECT MAX(account_id) FROM account);+++++Типы подзапросов169| account_id | product_cd | cust_id | avail_balance |+++++|24 | SBL|13 |50000.00 |+++++1 row in set (0.65 sec)В этом примере подзапрос возвращает максимальное значение столбцаaccount_id таблицы account. Затем содержащее выражение возвращаетданные по этому счету.
Если возникают какие нибудь вопросы по по воду того, что делает подзапрос, можно выполнить его отдельно (безскобок) и посмотреть, что он возвращает. Вот подзапрос из предыду щего примера:mysql> SELECT MAX(account_id) FROM account;++| MAX(account_id) |++|24 |++1 row in set (0.00 sec)Итак, подзапрос возвращает одну строку и один столбец. Это позволя ет использовать его как одно из выражений в условии равенства (еслибы подзапрос возвращал две или более строк, он мог бы сравниватьсяс чем то, но не мог бы быть равным чему то; более подробно об этомпозже). В этом случае можно взять значение, возвращаемое подзапро сом, и подставить его в правую часть условия фильтрации в основномзапросе:mysql> SELECT account_id, product_cd, cust_id, avail_balance> FROM account a> WHERE account_id = 24;+++++| account_id | product_cd | cust_id | avail_balance |+++++|24 | SBL|13 |50000.00 |+++++1 row in set (0.02 sec)Здесь удобно использовать подзапрос, потому что он позволяет извле кать информацию о счете с наибольшим порядковым номером однимзапросом.
В противном случае пришлось бы с помощью одного запросаполучать максимальный account_id и затем писать второй запрос длявыбора необходимых данных из таблицы account. Как вы увидите, под запросы полезны и во многих других ситуациях и могут стать однимиз самых мощных инструментов в вашем наборе SQL инструментов.Типы подзапросовНаряду с различиями, отмеченными ранее относительно типа возвра щаемой подзапросом таблицы (одна строка/один столбец, одна стро 170Глава 9.
Подзапросыка/несколько столбцов или несколько столбцов), есть и другой показа тель, по которому можно дифференцировать подзапросы. Некоторыеподзапросы полностью самодостаточны (называются несвязаннымиподзапросами (noncorrelated subqueries)), тогда как другие ссылаютсяна столбцы содержащего выражения (называются связанными подзапросами (correlated subqueries)). В нескольких следующих разделахрассмотрены эти два типа подзапросов и приведены разные операто ры, позволяющие взаимодействовать с ними.Несвязанные подзапросыПриведенный ранее в этой главе пример является несвязанным подза просом.
Он может выполняться самостоятельно и не использует ниче го из содержащего выражения. Большинство подзапросов являютсянесвязанными. Только выражения update или delete часто используютсвязанные подзапросы (более подробно об этом позже). Упомянутыйпример не только является несвязанным, но и возвращает таблицу, со стоящую всего из одной строки и одного столбца. Такой тип подзапро са называется скалярным подзапросом (scalar subquery), и его можнопомещать в любую часть условия, использующего обычные операторы(=, <>, <, >, <=, >=). Следующие примеры показывают применение ска лярного подзапроса в условии неравенства:mysql> SELECT account_id, product_cd, cust_id, avail_balance> FROM account> WHERE open_emp_id <> (SELECT e.emp_id> FROM employee e INNER JOIN branch b>ON e.assigned_branch_id = b.branch_id> WHERE e.title = 'Head Teller' AND b.city = 'Woburn');+++++| account_id | product_cd | cust_id | avail_balance |+++++|6 | CHK|3 |1057.75 ||7 | MM|3 |2212.50 ||8 | CHK|4 |534.12 ||9 | SAV|4 |767.77 ||10 | MM|4 |5487.09 ||11 | CHK|5 |2237.97 ||12 | CHK|6 |122.37 ||13 | CD|6 |10000.00 ||15 | CHK|8 |3487.19 ||16 | SAV|8 |387.99 ||17 | CHK|9 |125.67 ||18 | MM|9 |9345.55 ||19 | CD|9 |1500.00 ||20 | CHK|10 |23575.12 ||21 | BUS|10 |0.00 ||23 | CHK|12 |38552.05 ||24 | SBL|13 |50000.00 |Несвязанные подзапросы171+++++17 rows in set (0.00 sec)Этот запрос возвращает данные по всем счетам, которые были откры ты операционистом отделения Woburn, который не является старшим(подзапрос написан в предположении, что в отделении только одинстарший операционист).
Подзапрос в этом примере немного сложнее,чем в предыдущем, – он соединяет две таблицы и включает два усло вия фильтрации. Подзапросы могут быть простыми или сложными на столько, насколько требуется. Они могут использовать любые из всехдоступных блоков запроса (select, from, where, group by, having, order by).Если при использовании в условии равенства подзапрос возвращаетболее одной строки, будет сформирована ошибка. Например, если пре дыдущий запрос изменить так, чтобы по подзапросу возвращались всеоперационисты отделения Woburn, а не только старший, будет полу чена следующая ошибка:mysql> SELECT account_id, product_cd, cust_id, avail_balance> FROM account> WHERE open_emp_id <> (SELECT e.emp_id> FROM employee e INNER JOIN branch b>ON e.assigned_branch_id = b.branch_id> WHERE e.title = 'Teller' AND b.city = 'Woburn');ERROR 1242 (21000): Subquery returns more than 1 rowЕсли выполнить только подзапрос, результаты будут такими:mysql> SELECT e.emp_id> FROM employee e INNER JOIN branch b> ON e.assigned_branch_id = b.branch_id> WHERE e.title = 'Teller' AND b.city = 'Woburn';++| emp_id |++|11 ||12 |++2 rows in set (0.02 sec)Причина сбоя основного запроса в том, что выражение (open_emp_id) неможет быть приравнено набору выражений (emp_id 11 и 12).
Другимисловами, единичный элемент не может приравниваться множеству.В следующем разделе вы увидите, как решить эту проблему с помо щью другого оператора.Подзапросы, возвращающие несколько строки один столбецЕсли подзапрос возвращает более одной строки, его нельзя использо вать как одну из частей условия равенства, что и было продемонстри 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, но это никак не отра зится на результирующем наборе основного запроса.174Глава 9.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
