SQLГлава 10. Вставка одного запроса внутрь другогоВ конце Главы 9 мы говорили, что запросы могут управлять другими запросами. В этой главе вы узнаете, как это делается (большей частью) путём помещения запроса внутрь предиката другого запроса и использования вывода внутреннего запроса в верном или неверном условии предиката.
Вы сможете выяснить, какие виды операторов могут использовать подзапросы, и посмотреть, как подзапросы работают со средствами SQL, такими как
Вы узнаете, как использовать подзапросы с предложением Как работает подзапрос?
С помощью SQL вы можете вкладывать запросы друга в друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса, определяющего, верно оно или нет. Например, предположим, что мы знаем имя продавца: SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE sname = 'Motika');
Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения
Единственной найденной строкой, естественно, будет =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT snum | | FROM Salespeople | | WHERE sname = 'Motika'); | |=================================================| | onum amt odate cnum snum | | ----- ------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | ================================================= Рисунок 10.1 Использование подзапроса Основной запрос затем выполняется как обычно с вышеупомянутыми результатами. Разумеется, подзапрос должен выбрать один, и только один, столбец, а тип данных этого столбца должен совпадать с тем значением, с которым он будет сравниваться в предикате.
Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена (в данном случае Значения, которые подзапрос может выводитьСкорее всего, было бы удобнее, чтобы наш подзапрос в предыдущем примере возвращал одно, и только одно, значение.
Имея условие При использовании подзапросов в предикатах, основанных на реляционных операциях (уравнениях или неравенствах, как объяснено в Главе 4), вы должны убедиться, что использовали подзапрос, который будет выдавать одну, и только одну, строку вывода. Если вы используете подзапрос, который не выводит никаких значений вообще, команда не потерпит неудачи, но основной запрос не выведет никаких значений. Подзапросы, которые не производят никакого вывода (или нулевой вывод), вынуждают рассматривать предикат ни как верный, ни как неверный, а как неизвестный. Однако неизвестный предикат имеет тот же самый эффект, что и неверный: никакие строки не выбираются основным запросом (смотри в Главе 5 подробную информацию о неизвестном предикате). Вот пример плохой стратегии: SELECT * FROM Orders WHERE snum = (SELECT snum FROM Salespeople WHERE city = 'Barcelona');
Поскольку мы имеем только одного продавца в DISTINCT с подзапросами
В некоторых случаях вы можете использовать Вот способ сделать это (вывод показан на Рисунке 10.2): SELECT * FROM Orders WHERE snum = (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum = | | (SELECT DISTINCT snum | | FROM Orders | | Where cnum = 2001); | | =============================================== | | onum amt odate cnum snum | | ----- --------- --------- ------ ------- | | 3003 767.19 10/03/1990 2001 1001 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================= Рисунок 10.2 Использование DISTINCT для получения одного значения из подзапроса
Подзапрос установил, что значение поля Должен быть и альтернативный подход, чтобы ссылаться к таблице Заказчиков, а не к таблице Заказов в подзапросе. Так как поле cnum это первичный ключ таблицы Заказчиков, запрос, выбирающий его, должен выдать только одно значение. Это рационально, только если вы как пользователь имеете доступ к таблице Заказов, но не к таблице Заказчиков. В этом случае вы можете использовать решение, которое мы показали выше. (SQL имеет механизмы, которые определяют, кто имеет привилегии на выполнение действий в определённой таблице. Это будет объясняться в Главе 22.) Пожалуйста, учтите, что методика, используемая в предшествующем примере, применима, только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных (РБД), она является исключением из правил. Предикаты с подзапросами являются необратимымиВы должны обратить внимание что предикаты, включающие подзапросы, используют выражение <скалярная форма> <оператор> <подзапрос>, а не <подзапрос> <оператор> <скалярное выражение> или <подзапрос> <оператор> <подзапрос>. Другими словами, вы не должны записывать предыдущий пример так: SELECT * FROM Orders WHERE (SELECT DISTINCT snum FROM Orders WHERE cnum = 2001) = snum; В строгой ANSI-реализации это приведет к неудаче, хотя некоторые программы и позволяют делать такие вещи. ANSI также предохраняет от появления в выводе подзапроса обоих значений при сравнении. Использование агрегатных функций в подзапросахТип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же - агрегатная функция.
Любой запрос, использующий одиночную функцию агрегата без предложения SELECT * FROM Orders WHERE amt > (SELECT AVG (amt) FROM Orders WHERE odate = 10/04/1990); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE amt > | | (SELECT AVG (amt) | | FROM Orders | | WHERE odate = 01/04/1990); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ----- | | 3002 1900.10 10/03/1990 2007 1004 | | 3005 2345.45 10/03/1990 2003 1002 | | 3006 1098.19 10/03/1990 2008 1007 | | 3009 1713.23 10/04/1990 2002 1003 | | 3008 4723.00 10/05/1990 2006 1001 | | 3010 1309.95 10/06/1990 2004 1002 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================= Рисунок 10.3 Выбор всех сумм со значением выше среднего на 10/04/1990
Средняя сумма приобретений на 4 октября — Например, следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне, SELECT AVG (comm) FROM Salespeople GROUP BY city HAVING city = 'London'; не может использоваться в подзапросе! Во всяком случае, это не лучший способ формировать запрос. Другим способом может быть SELECT AVG (comm) FROM Salespeople WHERE city = 'London'; Использование подзапросов, которые выдают много строк с помощью оператора IN
Вы можете использовать подзапросы, которые производят любое число строк, если вы применяете специальный оператор
Когда вы используете SELECT * FROM Orders WHERE snum IN (SELECT snum FROM Salespeople WHERE city = 'London'); =============== SQL Execution Log ============== | | | SELECT * | | FROM Orders | | WHERE snum IN | | (SELECT snum | | FROM Salespeople | | WHERE city = 'London'); | | =============================================== | | onum amt odate cnum snum | | ----- -------- ---------- ----- ------ | | 3003 767.19 10/03/1990 2001 1001 | | 3002 1900.10 10/03/1990 2007 1004 | | 3006 1098.19 10/03/1990 2008 1007 | | 3008 4723.00 10/05/1990 2006 1001 | | 3011 9891.88 10/06/1990 2006 1001 | ================================================= Рисунок 10.4 Использование подзапроса с IN В ситуации, подобной этой, подзапрос проще для понимания пользователем и проще для выполнения компьютером, чем если бы вы использовали объединение: SELECT onum, amt, odate, cnum, Orders.snum FROM Orders, Salespeople WHERE Orders.snum = Salespeople.snum AND Salespeople.city = 'London';
Хотя это и произведёт тот же самый вывод, что в примере с подзапросом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эффективнее извлекать из таблицы Продавцов значения поля Строго говоря, то, быстрее или нет работает вариант подзапроса, практически зависит от реализации — в какой программе вы это используете. Часть вашей программы, называемая оптимизатор, пытается найти наиболее эффективный способ выполнения ваших запросов. Хороший оптимизатор в любом случае преобразует вариант объединения в подзапрос, но нет достаточно простого способа, чтобы выяснить, выполнено это или нет. Лучше сохранить ваши запросы в памяти, нежели полагаться полностью на оптимизатор.
Конечно, вы можете также использовать оператор SELECT onum, amt, odate FROM Orders WHERE snum = (SELECT snum FROM Orders WHERE cnum = 2001); Вы можете устранить потребность в DISTINCT, используя IN вместо (=): SELECT onum, amt, odate FROM Orders WHERE snum IN (SELECT snum FROM Orders WHERE cnum = 2001);
Что случится, если есть ошибка и один из заказов был аккредитован различным продавцам? Версия, использующая IN является подходящим, если запрос может ограниченно производить одно или более значений, независимо от того, ожидаете вы их или нет. Предположим, мы хотим знать комиссионные всех продавцов, обслуживающих заказчиков в Лондоне: SELECT comm FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE city = 'London');
Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца =============== SQL Execution Log ============== | | | SELECT comm | | FROM Salespeople | | WHERE snum IN | | (SELECT snum | | FROM Customers | | WHERE city = 'London'); | | =============================================== | | comm | | ------- | | 0.12 | | | ================================================= Рисунок 10.5 Использование IN с подзапросом для вывода одного значения
Между прочим, префикс таблицы для поля Подзапросы выбирают одиночные столбцы
Смысл всех подзапросов, обсуждённых в этой главе, в том, что все они выбирают одиночный столбец. Это обязательно, поскольку полученный вывод сравнивается с одиночным значением. Подтверждением этому является то, что Использование выражений в подзапросах
Вы можете использовать выражение, основанное на столбце, а не просто сам столбец, в предложении SELECT * FROM Customers WHERE cnum = (SELECT snum + 1000 FROM Salespeople WHERE sname = Serres);
Он находит всех заказчиков, чьё значение поля cnum, равное 1000, выше поля =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE cnum = | | (SELECT snum + 1000 | | WHERE Salespeople | | WHERE sname = 'Serres' | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ----- | | 2002 Giovanni Rome 200 1003 | =============================================== Рисунок 10.6 Использование подзапроса с выражением Подзапросы в предложении HAVING
Вы можете также использовать подзапросы внутри предложения SELECT rating, COUNT (DISTINCT cnum) FROM Customers GROUP BY rating HAVING rating > (SELECT AVG (rating) FROM Customers WHERE city = 'San Jose'); =============== SQL Execution Log ============= | | | SELECT rating,count (DISTINCT cnum) | | FROM Customers | | GROUP BY rating | | HAVING rating > | | (SELECT AVG (rating)snum + 1000 | | FROM Custimers | | WHERE city = 'San Jose'); | |================================================ | | rating | | -------- -------- | | 200 2 | ================================================ Рисунок 10.7 Поиск в San Jose заказчиков с оценкой выше среднего
Эта команда подсчитывает заказчиков в РезюмеТеперь вы используете запросы в иерархической манере. Вы видели, что использование результата одного запроса для управления другим расширяет возможности, позволяя выполнить большее количество функций.
Вы теперь понимаете, как использовать подзапросы с реляционными операциями и со специальным оператором
В следующих главах мы будем рассматривать подзапросы. Сначала, в Главе 11, мы обсудим другой вид подзапроса, который выполняется отдельно для каждой строки таблицы, вызываемой во внешнем запросе. Затем, в Главах 12 и 13, мы представим вам несколько специальных операторов, которые функционируют на всех подзапросах, как это делает Работа с SQL
(См. ответы в Приложении A.) |
||