W Cat - SQL за 24 часа
SELECT EMP_ID
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS';
EMP_ID
442346889
313782439
220984332
443679012
4 строки выбраны.
Этот запрос будет использован в качестве подзапроса в следующем операторе UPDATE. Вот этот оператор UPDATE с подзапросом.
UPDATE EMPLOYEE_PAY_TBL
SET PAY_RATE = PAY_RATE * 1.1
WHERE EMP_ID IN (SELECT EMP_ID
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS');
4 строки обновлены.
Как и ожидалось, были обновлены данные четырех строк Здесь следует обратить внимание на то, что в отличие от примеров в предыдущих разделах в данном случае подзапрос возвращает несколько строк данных. Ввиду того, что ожидается получить от подзапроса несколько строк, используется ключевое слово IN, позволяющее сравнить данное значение со списком значений. При использовании для сравнения знака равенства было бы возвращено сообщение об ошибке.
Научитесь правильно выбирать операцию для обработки результатов подзапроса Например, знак равенства, используемый для сравнения выражения с одним значением, не годится для использования с подзапросом, возвращающим несколько строк данных
Подзапросы в операторе DELETE
Подзапросы можно использовать в операторе DELETE. Базовый синтаксис оператора следующий.
DELETE FROM имя_таблицы
[ WHERE ОПЕРАЦИЯ [ значение ]
(SELECT имя_столбца
FROM имя_таблицы
[ WHERE ])
В следующем примере из таблицы EMPLOYEE_PAY_TBL удаляется запись с информацией о служащем по имени BRANDON GLASS. Табельный номер этого служащего не известен, но можно создать подзапрос, который найдет этот номер в таблице EMPLOYEEJTBL по значениям столбцов с именами (FIRST_NAME) и фамилиями (LAST_NAME) служащих.
DELETE FROM EMPLOYEE_PAY_TBL WHERE EMP_ID = (SELECT EMP_ID
FROM EMPLOYEE_PAY_TBL
WHERE LAST_NAME a 'GLASS'
AND FIRST_NAME = 'BRAHDON');
1 строка обновлена.
He забывайте использовать в операторах UPDATE и DELETE ключевое слово WHERE. Если последнее не использовать, будут обновлены или удалены данные всех столбцов1 За подробностями обратитесь к уроку 5, "Манипуляция данными"
Подзапросы внутри подзапросов
Точно так же, как подзапрос можно вложить в главный запрос, подзапрос можно вложить и в подзапрос. В главном запросе подзапрос выполняется до выполнения главного, точно так же и в подзапросе вложенный в него подзапрос будет выполнен первым
По поводу имеющихся ограничений (если они есть вообще) на число вложений одних запросов в другие в рамках одного оператора обратитесь к документации по используемой вами реализации языка, поскольку такие ограничения для разных реализаций могут не совпадать
Базовый синтаксис для операторов, использующих вложенные подзапросы, должен быть следующим
SELECT имя_столбца [, имя_столбца ]
FROM таблица1 [, таблица2 ]
WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя__столбца
FPOM таблица
WHERE имя_столбца ОПЕРАЦИЯ (SELECT имя_столбца FROM таблица
[WHEREимя_столбца ОПЕРАЦИЯ значение]))
В следующем примере используются два подзапроса, вложенные один в другой Требуется выяснить, какие покупатели заказали товаров на сумму большую, чем сумма цен всех товаров
SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID IN (SELECT O.CUST_ID
FROM ORDERS_TBL O, PRODUCTS_TBL P WHERE 0 PROD_ID = P.PROD_ID
AND O.QTY * P.COST > (SELECT STJM(COST)
FROM PRODUCTS_TBL));
CUST_ID CUST_NAME
287 GAVINS PLACE
43 SCHYLERS NOVELTIES
2 строки выбраны.
В результате оказались выбранными 2 строки, удовлетворяющие условиям обоих подзапросов.
Вот как последовательно выполнялись запросы в данном операторе.
SELECT SUM(COST) FROM PRODUCTS_TBL));
SUM(COST)
138.08 1 строка выбрана.
SELECT O.CUST_ID
FROM ORDERS_TBL O, PRODUCTSJTBL P
WHERE О PROD_ID = P.PROD_ID
AND O.QTY * P.COST > 138.08;
CUST_ID
287
43
2 строки выбраны.
После подстановки в главный запрос результатов внутреннего подзапроса главный запрос принимает следующий вид
SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID IN (SELECT O.CUST_ID
FROM ORDERS_TBL O, PRODUCTS_TBL P WHERE О PROD_ID = P.PROD_ID
AND O.QTY * P.COST > 138.08);
А вот что получается после выполнения следующего подзапроса
SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID IN '287•,'43');
Поэтому в результате имеем
CUST_ID CUST_NAME
287 GAVINS PLACE
43 SCHYLERS NOVELTIES
2 строки выбраны.
При использовании в операторе нескольких подзапросов увеличивается время, необходимое для обработки запроса, и повышается вероятность ошибок из-за усложнения оператора
Связанные подзапросы
Связанные подзапросы допускаются во многих реализациях SQL. Концепция связанного подзапроса определяется стандартом ANSI SQL и поэтому рассматривается здесь. Связанный подзапрос - это подзапрос, зависящий от информации, предоставляемой главным запросом.
В следующем примере в подзапросе определение связи между таблицами CUSTOMER_TBL И ORDERSJTBL использует псевдоним таблицы CUSTOMERJTBL (С), определенный в главном запросе. Этот оператор возвращает имена всех покупателей, заказавших более 10 единиц товара.
SELECT C.CUST_NAME
FROM CUSTOMER_TBL С
WHERE 10 < (SELECT SUM(O.QTY)
FROM ORDERS_TBL О
WHERE O.CUST_ID =C.CUST_ID);
CUST_NAME
SCOTTYS MARKET
SCHYLERS NOVELTIES
MARYS GIFT SHOP
В случае связанного подзапроса ссылка на таблицу главного запроса должна быть определена до начала выполнения подзапроса.
В следующем операторе этот запрос немного модифицирован, чтобы получить список всех заказчиков с соответствующим количеством заказанных товаров и иметь возможность проверить результаты предыдущего примера.
SELECT C.CUST_NAME, SUM(O.QTY)
FROM CUSTOMER_TBL С,
ORDERS_TBL О GROUP BY CUST_NAME;
CUSTJMAME SUM(O.QTY)
GAVINS PLACE 10
LESLIE GLEASON 1
MARYS GIFT SHOP 100
SCHYLERS NOVELTIES 25
SCOTTYS MARKET 20
WENDY WOLF 2
6 строк выбраны.
Ключевое слово GROUP BY здесь требуется потому, что по отношению ко второму столбцу используется итоговая функция SUM. Это позволяет подсчитать суммы для каждого из заказчиков В предыдущем примере ключевое слово GROUP BY не требовалось, поскольку там функция зим использовалась для суммирования всех результатов запроса, выполняемого для каждого конкретного заказчика.
Резюме
Попросту говоря, подзапрос представляет собой запрос, выполняемый в рамках другого запроса для задания дополнительных условий на выводимые данные. Подзапрос можно использовать в выражениях ключевых слов WHERE и HAVING. Подзапросы обычно используют в других запросах (операторах DQL - языка запросов к данным), но подзапросы можно использовать и в операторах DML (языка манипуляций данными) таких, как INSERT, UPDATE и DELETE. Все основные правила использования операторов языка манипуляций данными применимы и при использовании в них подзапросов.
Синтаксис подзапросов практически не отличается от синтаксиса обычного запроса, имеются лишь небольшие ограничения. Одним из таких ограничений является запрет на использование в подзапросах ключевого слова ORDER BY, однако, вместо него можно использовать ORDER BY, чем достигается практически тот же эффект. Подзапросы используются для размещения в запросах условий, точные данные для которых не известны, тем самым расширяя возможности и гибкость SQL.
Вопросы и ответы
В примерах подзапросов обращает на себя внимание использование многочисленных отступов. Являются ли отступы необходимым элементом синтаксиса подзапроса?
Нет. Отступы используются исключительно для того, чтобы разбить оператор на части, чтобы его было легче читать и проще понять.
Имеются ли ограничения на число вложений подзапросов в запросы?
Ограничения на число уровней вложения подзапросов в запросы и число связываемых в запросе таблиц зависят от конкретной реализации SQL. В некоторых реализациях языка таких ограничений вообще нет, хотя использование слишком большого числа вложенных подзапросов может существенно замедлить выполнение соответствующего оператора. По большей части такие ограничения фактически определяются возможностями оборудования, скоростью процессора, объемами памяти и другими подобными факторами.
Отладка операторов с подзапросами кажется непростым делом, особенно если используются еще и вложенные подзапросы. Есть ли какие-либо рекомендации по поводу оптимизации процесса отладки запросов с подзапросами?
Лучше всего для отладки выделить из сложного запроса составляющие его запросы. Сначала следует проверить внутренний подзапрос самого низшего уровня и постепенно продвигаться по уровням до главного запроса (точно так же, как запрос обрабатывается базой данных). На каждом шагу после обработки выделенного из сложного оператора подзапроса можно подставить возвращенные этим подзапросом значения в исходный оператор, чтобы проверить правильность работы последнего. Чаще всего ошибки возникают из-за выражений, содержащих неправильное использование знаков операций для оценки результатов подзапроса, таких как =, IN, >, < и т. п.
