SQLГлава 11. Соотнесённые подзапросы
В этой главе мы рассмотрим тип подзапроса, о котором мы не говорили в Главе 10, — соотнесённый подзапрос. Вы узнаете, как использовать соотнесённые подзапросы в предложениях Как сформировать соотнесённый подзапрос?Когда вы используете подзапросы в SQL, вы можете обратиться к внутреннему запросу таблицы в предложении внешнего запроса FROM, сформировав соотнесённый подзапрос. Когда вы делаете это, подзапрос выполняется неоднократно, по одному разу для каждой строки таблицы основного запроса. Соотнесённый подзапрос — одно из большого количества тонких понятий в SQL. Если вы сумеете овладеть им, вы найдёте, что он очень мощен, поскольку может выполнять сложные функции с помощью очень лаконичных указаний. Например, вот способ найти всех заказчиков в Заказах на 3-е октября (вывод показан на Рисунке 11.1): SELECT * FROM Customers outer WHERE 10/03/1990 IN (SELECT odate FROM Orders inner WHERE outer.cnum = inner.cnum); Как работает соотнесённый подзапрос?
В вышеупомянутом примере, "внутренний" ( =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE 10/03/1990 IN | | (SELECT odate | | FROM Orders inner | | WHERE outer.cnum = inner.cnum); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2001 Hoffman London 100 1001 | | 2003 Liu San Jose 200 1002 | | 2008 Cisneros San Jose 300 1007 | | 2007 Pereira Rome 100 1004 | =============================================== Рисунок 11.1 Использование соотнесённого подзапроса Следовательно, процедура оценки, выполняемой соотнесённым подзапросом:
В вышеупомянутом примере SQL осуществляет следующую процедуру:
Как видите, вычисления, которые SQL выполняет с помощью этих простых инструкций, довольно сложны. Конечно, вы могли бы решить ту же самую проблему, используя объединение следующего вида (вывод для этого запроса показан на Рисунке 11.2): SELECT * FROM Customers first, Orders second WHERE first.cnum = second.cnum AND second.odate = 10/03/1990;
Обратите внимание, что =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers first, Orders second | | WHERE first.cnum = second.cnum | | (SELECT COUNT (*) | | FROM Customers | | WHERE snum = main.snum; | | ============================================= | | cnum cname | | ----- -------- | | 1001 Peel | | 1002 Serres | =============================================== Рисунок 11.2 Использование объединения вместо соотнесенного подзапроса Предположим, что мы хотим видеть имена и номера всех продавцов, которые имеют более одного заказчика. Следующий запрос выполнит это для вас (вывод показан на Рисунке 11.3): SELECT snum, sname FROM Salespeople main WHERE 1 < ( SELECT COUNT (*) FROM Customers WHERE snum = main.snum );
Обратите внимание, что предложение Псевдонимы также часто запрашиваются, чтобы дать возможность ссылаться к той же самой таблице во внутреннем и внешнем запросе без какой-либо неоднозначности. =============== SQL Execution Log ============ | | | SELECT snum sname | | FROM Salespeople main | | WHERE 1 < | | AND second.odate = 10/03/1990; | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2001 Hoffman London 100 1001 | | 2003 Liu San Jose 200 1002 | | 2008 Cisneros San Jose 300 1007 | | 2007 Pereira Rome 100 1004 | =============================================== Рисунок 11.3 Нахождение продавцов с несколькими заказчиками Использование соотнесенных подзапросов для поиска ошибок
Иногда полезно выполнять запросы, которые разработаны специально так, чтобы находить ошибки. Это всегда возможно при появлении дефектной информации, которую можно ввести в вашу БД, и, если она введена, бывает трудно её выявить. Следующий запрос не должен производить никакого вывода. Он просматривает таблицу Заказов, чтобы увидеть, совпадают ли поля SELECT * FROM Orders main WHERE NOT snum = (SELECT snum FROM Customers WHERE cnum = main.cnum); При использовании механизма ссылочной целостности (обсуждаемого в Главе 19), вы можете быть гарантированы от некоторых ошибок такого вида. Этот механизм не всегда доступен, хотя его использование желательно во всех случаях, причем поиск ошибки запроса, описанный выше, может быть ещё полезнее. Сравнение таблицы с собойВы можете также использовать соотнесённый подзапрос, основанный на той же самой таблице, что и основной запрос. Это даст вам возможность извлечь сложные формы произведённой информации. Например, мы можем найти все заказы со значениями сумм приобретений выше среднего для их заказчиков (вывод показан на Рисунке 11.4): SELECT * FROM Orders outer WHERE amt > (SELECT AVG amt FROM Orders inter WHERE inner.cnum = outer.cnum); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders outer | | WHERE amt > | | (SELECT AVG (amt) | | FROM Orders inner | | WHERE inner.cnum = outer.cnum | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3006 1098.19 10/03/1990 2008 1007 | | 3010 1309.00 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================= Рисунок 11.4 Соотнесение таблицы с собой Конечно, в нашей маленькой типовой таблице, где большинство заказчиков имеют только один заказ, большинство значений являются одновременно средними и, следовательно, не выбираются. Давайте введём команду другим способом (вывод показан на Рисунке 11.5): SELECT * FROM Orders outer WHERE amt > = (SELECT AVG (amt) FROM Orders inner WHERE inner.cnum = outer.cnum); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders outer | | WHERE amt > = | | (SELECT AVG (amt) | | FROM Orders inner | | WHERE inner.cnum = outer.cnum); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 5160.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================= Рисунок 11.5 Выбираются заказы, которые >= средней сумме приобретений их заказчиков. Различие, конечно, в том, что реляционная операция основного предиката включает значения, которые равняются среднему (что обычно означает, что они — единственные заказы данных заказчиков). Соотнесённые подзапросы в предложении HAVING
Предложение SELECT odate, SUM(amt) FROM Orders a GROUP BY odate HAVING SUM(amt) > ( SELECT 2000.00 + MAX(amt) FROM Orders b WHERE a.odate = b.odate );
Подзапрос вычисляет значение Соотнесённые подзапросы и объединенияКак вы и могли предположить, соотнесённые подзапросы по своей природе близки объединениям — они оба включают сверку каждой строки одной таблицы с каждой строкой другой (или псевдонимом) таблицы. Вы найдёте, что большинство операций, которые могут выполняться с одним из них, будут также работать и с другим.
Однако в прикладной программе между ними имеется различие, такое как вышеупомянутая потребность в использовании Как правило форма запроса, которая кажется наиболее интуитивной, будет, вероятно, лучшей в использовании, но при этом хорошо бы знать обе техники для тех ситуаций, когда та или иная могут не работать. Резюме
Вы можете поздравить себя с овладением большого блока понятий SQL — соотнесённого подзапроса. Вы видели, как соотнесённый подзапрос связан с объединением, а также — как его можно использовать с агрегатными функциями и в предложении
Следующий шаг — описание некоторых специальных операторов SQL. Они берут подзапросы как аргументы, как это делает Работа с SQL
(См. ответы в Приложении A.) |
||