SQL — Урок 5. Вложенные запросы
В прошлом уроке мы столкнулись с одним неудобством. Когда мы хотели узнать, кто создал тему «велосипеды», и делали соответствующий запрос:
Вместо имени автора, мы получали его идентификатор. Это и понятно, ведь мы делали запрос к одной таблице — Темы, а имена авторов тем хранятся в другой таблице — Пользователи. Поэтому, узнав идентификатор автора темы, нам надо сделать еще один запрос — к таблице Пользователи, чтобы узнать его имя:
В SQL предусмотрена возможность объединять такие запросы в один путем превращения одного из них в подзапрос (вложенный запрос). Итак, чтобы узнать, кто создал тему «велосипеды», мы сделаем следующий запрос:
То есть, после ключевого слова WHERE, в условие мы записываем еще один запрос. MySQL сначала обрабатывает подзапрос, возвращает id_author=2, и это значение передается в предложение WHERE внешнего запроса.
В одном запросе может быть несколько подзапросов, синтаксис у такого запроса следующий:
SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN (SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN (SELECT имя_столбца FROM имя_таблицы WHERE условие) ) ;
Обратите внимание, что подзапросы могут выбирать только один столбец, значения которого они будут возвращать внешнему запросу. Попытка выбрать несколько столбцов приведет к ошибке.
Давайте для закрепления составим еще один запрос, узнаем, какие сообщения на форуме оставлял автор темы «велосипеды»:
Теперь усложним задачу, узнаем, в каких темах оставлял сообщения автор темы «велосипеды»:
-
Сначала MySQL выполнит самый глубокий запрос:
SELECT id_author FROM topics WHERE topic_name=’велосипеды’
SELECT id_topic FROM posts WHERE id_author IN (2);
SELECT topic_name FROM topics WHERE id_topic IN (4,1);
SELECT name FROM users WHERE id_user IN
(SELECT id_author FROM topics WHERE topic_name=’велосипеды’);
SELECT name FROM users WHERE id_user =
(SELECT id_author FROM topics WHERE topic_name=’велосипеды’);
Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!
Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.
admin@site-do.ru
site-do.ru © 2009-2019 Копировать материалы сайта запрещено!
За нарушение авторских прав предусмотрена уголовная ответственность!
Телефон редакции +7(499)346-89-56
127051, г.Москва, Цветной бульвар, д. 11, стр. 6, офис 405.
Подзапросы в SQL (вложенные запросы SQL)
o В инструкции SELECT ;
o В инструкции FROM ;
o В условии WHERE .
- Подзапрос может быть вложен в инструкции SELECT , INSERT , UPDATE или DELETE , а также в другой подзапрос;
- Подзапрос обычно добавляется в условие WHERE оператора SQL SELECT ;
- Можно использовать операторы сравнения, такие как >,
- Подзапрос также называется внутренним запросом. Оператор, содержащий подзапрос, также называется внешним;
- Внутренний запрос выполняется перед родительским запросом, чтобы результаты его работы могли быть переданы внешнему.
Подзапрос можно использовать в инструкциях SELECT , INSERT , DELETE или UPDATE для выполнения следующих задач:
- Сравнения выражения с результатом запроса;
- Определения того, включено ли выражение в результаты запроса;
- Проверки того, выбирает ли запрос любые строки.
- Подзапрос SQL ( внутренний запрос ) выполняется перед выполнением основного запроса ( внешнего запроса );
- Основной запрос использует результат выполнения подзапроса.
Подзапросы SQL-примеры
В этом разделе мы рассмотрим, как использовать подзапросы. У нас есть следующие две таблицы: ‘ student ‘ и ‘ marks ‘ с общим полем ‘ StudentID ‘:
Теперь нужно составить запрос, определяющий всех студентов, которые получают лучшие отметки, чем студент со StudentID — « V002 ». Но мы не знаем отметок студента « V002 ».
Поэтому нужно составить два SQL подзапроса в Select . Один запрос возвращает отметки ( хранятся в поле « Total_marks » ) для « V002 », а второй запрос выбирает учеников, которые получают лучшие оценки, чем результат первого запроса.
SELECT * FROM `marks` WHERE studentid = 'V002';
Результатом запроса будет 80 .
Используя результат этого запроса, мы написали еще один запрос, чтобы определить учеников, которые получают оценки лучше, чем 80 .
SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks >80;
Два приведенных запроса определяют студентов, которые получают лучше оценки, чем студент StudentID « V002 » ( Abhay ).
Можно объединить эти два запроса, вложив один запрос в другой. Подзапрос — это запрос внутри круглых скобок. Рассмотрим подзапроса в SQL пример :
SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks > (SELECT total_marks FROM marks WHERE studentid = 'V002');
Графическое представление подзапроса SQL :
Подзапросы в SQL (вложенные запросы SQL): общие правила
Ниже приведен синтаксис подзапроса :
(SELECT [DISTINCT] аргументы_подзапроса_для_отбора FROM < имя_таблицы | имя_представления >. [WHERE условия_поиска] [GROUP BY выражение_объединения [,выражение_объединения] . ] [HAVING условия_поиска])
Подзапросы SQL (вложенные запросы SQL): рекомендации по использованию
Ниже приведен ряд рекомендаций, которым нужно следовать при использовании SQL подзапросов:
- Подзапрос должен быть заключен в круглые скобки;
- Подзапрос должен указываться в правой части оператора сравнения;
- Подзапросы не могут обрабатывать свои результаты, поэтому в подзапрос не может быть добавлено условие ORDER BY ;
- Используйте однострочные операторы с однострочными подзапросами;
- Если подзапрос возвращает во внешний запрос значение null , внешний запрос не будет возвращать никакие строки при использовании операторов сравнения в условии WHERE .
Подзапросы SQL (вложенные запросы SQL) — основные типы
- Однострочный подзапрос : возвращает ноль или одну строку;
- Многострочный подзапрос : возвращает одну или несколько строк;
- Многостолбцовый подзапрос : возвращает один или несколько столбцов;
- Коррелированные подзапросы : указывают один или несколько столбцов во внешней инструкции SQL . Такой подзапрос называется коррелированным, поскольку он связан с внешней инструкцией SQL ;
- Вложенные подзапросы : подзапросы помещенные в другой подзапрос.
Также можно использовать подзапрос внутри инструкций INSERT , UPDATE и DELETE .
Подзапросы SQL с инструкцией INSERT
Инструкция INSERT может использоваться с подзапросами SQL .
INSERT INTO имя_таблицы [ (столбец1 [, столбец2 ]) ] SELECT [ *|столбец1 [, столбец2 ] FROM таблица1 [, таблица2 ] [ WHERE VALUE OPERATOR ];
Если мы хотим вставить заказы из таблицы ‘ orders ‘, для которых в таблице « neworder » значение advance_amount составляет 2000 или 1500 , можно использовать следующий код SQL :
Пример таблицы: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION ---------- ---------- -------------- --------- --------------- --------------- ----------------- 200114 3500 2000 15-AUG-08 C00002 A008 200122 2500 400 16-SEP-08 C00003 A004 200118 500 100 20-JUL-08 C00023 A006 200119 4000 700 16-SEP-08 C00007 A010 200121 1500 600 23-SEP-08 C00008 A004 200130 2500 400 30-JUL-08 C00025 A011 200134 4200 1800 25-SEP-08 C00004 A005 200108 4000 600 15-FEB-08 C00008 A004 200103 1500 700 15-MAY-08 C00021 A005 200105 2500 500 18-JUL-08 C00025 A011 200109 3500 800 30-JUL-08 C00011 A010 200101 3000 1000 15-JUL-08 C00001 A008 200111 1000 300 10-JUL-08 C00020 A008 200104 1500 500 13-MAR-08 C00006 A004 200106 2500 700 20-APR-08 C00005 A002 200125 2000 600 10-OCT-08 C00018 A005 200117 800 200 20-OCT-08 C00014 A001 200123 500 100 16-SEP-08 C00022 A002 200120 500 100 20-JUL-08 C00009 A002 200116 500 100 13-JUL-08 C00010 A009 200124 500 100 20-JUN-08 C00017 A007 200126 500 100 24-JUN-08 C00022 A002 200129 2500 500 20-JUL-08 C00024 A006 200127 2500 400 20-JUL-08 C00015 A003 200128 3500 1500 20-JUL-08 C00009 A002 200135 2000 800 16-SEP-08 C00007 A010 200131 900 150 26-AUG-08 C00012 A012 200133 1200 400 29-JUN-08 C00009 A002 200100 1000 600 08-JAN-08 C00015 A003 200110 3000 500 15-APR-08 C00019 A010 200107 4500 900 30-AUG-08 C00007 A010 200112 2000 400 30-MAY-08 C00016 A007 200113 4000 600 10-JUN-08 C00022 A002 200102 2000 300 25-MAY-08 C00012 A012
INSERT INTO neworder SELECT * FROM orders WHERE advance_amount in(2000,1500);
Подзапросы SQL с инструкцией UPDATE
В инструкции UPDATE можно установить новое значение столбца, равное результату, возвращаемому однострочным подзапросом. Ниже приводится синтаксис и пример UPDATE с подзапросом SQL .
UPDATE таблица SET имя_столбца = новое_значение [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
Если мы хотим изменить параметры ord_date в таблице ‘ neworder ‘ с ‘ 15 -JAN-10 ‘, для которых разница между ord_amount и advance_amount меньше минимальной ord_amount в таблице ‘ orders ‘,то можно использовать следующий код SQL :
Пример таблицы: neworder ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION ---------- ---------- -------------- --------- --------------- --------------- ----------------- 200114 3500 2000 15-AUG-08 C00002 A008 200122 2500 400 16-SEP-08 C00003 A004 200118 500 100 20-JUL-08 C00023 A006 200119 4000 700 16-SEP-08 C00007 A010 200121 1500 600 23-SEP-08 C00008 A004 200130 2500 400 30-JUL-08 C00025 A011 200134 4200 1800 25-SEP-08 C00004 A005 200108 4000 600 15-FEB-08 C00008 A004 200103 1500 700 15-MAY-08 C00021 A005 200105 2500 500 18-JUL-08 C00025 A011 200109 3500 800 30-JUL-08 C00011 A010 200101 3000 1000 15-JUL-08 C00001 A008 200111 1000 300 10-JUL-08 C00020 A008 200104 1500 500 13-MAR-08 C00006 A004 200106 2500 700 20-APR-08 C00005 A002 200125 2000 600 10-OCT-08 C00018 A005 200117 800 200 20-OCT-08 C00014 A001 200123 500 100 16-SEP-08 C00022 A002 200120 500 100 20-JUL-08 C00009 A002 200116 500 100 13-JUL-08 C00010 A009 200124 500 100 20-JUN-08 C00017 A007 200126 500 100 24-JUN-08 C00022 A002 200129 2500 500 20-JUL-08 C00024 A006 200127 2500 400 20-JUL-08 C00015 A003 200128 3500 1500 20-JUL-08 C00009 A002 200135 2000 800 16-SEP-08 C00007 A010 200131 900 150 26-AUG-08 C00012 A012 200133 1200 400 29-JUN-08 C00009 A002 200100 1000 600 08-JAN-08 C00015 A003 200110 3000 500 15-APR-08 C00019 A010 200107 4500 900 30-AUG-08 C00007 A010 200112 2000 400 30-MAY-08 C00016 A007 200113 4000 600 10-JUN-08 C00022 A002 200102 2000 300 25-MAY-08 C00012 A012
UPDATE neworder SET ord_date='15-JAN-10' WHERE ord_amount-advance_amount< (SELECT MIN(ord_amount) FROM orders);
Подзапросы SQL с инструкцией DELETE
Ниже приводится синтаксис и пример использования SQL подзапросов с инструкцией DELETE .
DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
Если нужно удалить заказы из таблицы « neworder », для которых advance_amount меньше максимального значения advance_amount из таблицы « orders », можно использовать следующий код SQL :
Пример таблицы: neworder
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION ---------- ---------- -------------- --------- --------------- --------------- ----------------- 200114 3500 2000 15-AUG-08 C00002 A008 200122 2500 400 16-SEP-08 C00003 A004 200118 500 100 20-JUL-08 C00023 A006 200119 4000 700 16-SEP-08 C00007 A010 200121 1500 600 23-SEP-08 C00008 A004 200130 2500 400 30-JUL-08 C00025 A011 200134 4200 1800 25-SEP-08 C00004 A005 200108 4000 600 15-FEB-08 C00008 A004 200103 1500 700 15-MAY-08 C00021 A005 200105 2500 500 18-JUL-08 C00025 A011 200109 3500 800 30-JUL-08 C00011 A010 200101 3000 1000 15-JUL-08 C00001 A008 200111 1000 300 10-JUL-08 C00020 A008 200104 1500 500 13-MAR-08 C00006 A004 200106 2500 700 20-APR-08 C00005 A002 200125 2000 600 10-OCT-08 C00018 A005 200117 800 200 20-OCT-08 C00014 A001 200123 500 100 16-SEP-08 C00022 A002 200120 500 100 20-JUL-08 C00009 A002 200116 500 100 13-JUL-08 C00010 A009 200124 500 100 20-JUN-08 C00017 A007 200126 500 100 24-JUN-08 C00022 A002 200129 2500 500 20-JUL-08 C00024 A006 200127 2500 400 20-JUL-08 C00015 A003 200128 3500 1500 20-JUL-08 C00009 A002 200135 2000 800 16-SEP-08 C00007 A010 200131 900 150 26-AUG-08 C00012 A012 200133 1200 400 29-JUN-08 C00009 A002 200100 1000 600 08-JAN-08 C00015 A003 200110 3000 500 15-APR-08 C00019 A010 200107 4500 900 30-AUG-08 C00007 A010 200112 2000 400 30-MAY-08 C00016 A007 200113 4000 600 10-JUN-08 C00022 A002 200102 2000 300 25-MAY-08 C00012 A012
DELETE FROM neworder WHERE advance_amount< (SELECT MAX(advance_amount) FROM orders);
Как сделать вложенный запрос sql
Подзапросы являются одним из самых мощных инструментов �� в SQL, который можно использовать в любых видах запросов. В ближайших уроках мы познакомимся с основными типами подзапросов и рассмотрим примеры как их можно использовать.
Подзапрос — это запрос, использующийся в другом SQL запросе. Подзапрос всегда заключён в круглые скобки и обычно выполняется перед основным запросом.
Как и любой другой SQL запрос, подзапрос возвращает результирующий набор, который может быть одним из следующих:
- одна строка и один столбец;
- нескольких строк с одним столбцом;
- нескольких строк с несколькими столбцами.
В зависимости от типа результирующего набора подзапроса определяются операторы, которые могут использоваться в основном запросе.
Получим список всех бронирований самого дорогого на данный момент жилого помещения:
MySQLSELECT * FROM Reservations WHERE Reservations.room_id = ( SELECT id FROM Rooms ORDER BY price DESC LIMIT 1 )В данном случае запрос на получение самого дорого жилого помещения выполняется в качестве подзапроса, а затем результат результирующего набора применяется в основном запросе.
MySQLSELECT id FROM Rooms ORDER BY price DESC LIMIT 1Вложенные запросы SQL
Здравствуйте, уважаемые читатели! В этой статье мы поговорим о том, что такое вложенные запросы в SQL. Традиционно, рассмотрим несколько примеров с той базой данных, которую создавали в первых статьях.
Введение
Итак, само название говорит о том, что запрос во что-то вложен. Так вот, вложенный запрос в SQL означает, что запрос select выполняется в еще одном запросе select — на самом деле вложенность может быть и многоуровневой, то есть select в select в select и т.д. Такие запросы обычно используются для получения данных из двух и более таблиц. Они нужны чтобы данные из разных таблиц можно было соотнести и по зависимости осуществить выборку. У вложенных запросов есть и недостаток — зачастую слишком долгое время работы занимает запрос, потому что идет большая нагрузка на сервер. Тем не менее, саму конструкцию необходимо знать и использовать при возможности.
Структура ранее созданных таблиц
Основы вложенных запросов в SQL
Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов.
Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу Salespeople, определили бы snum продавца Колыванова — он равен 1. И выполнили бы запрос SQL с помощью условия WHERE. Вот пример такого SQL запроса:
SELECT amt, odate FROM orders WHERE snum = 1Очевидно, какой будет вывод:
amt odate 348 2017-04-08 80 2017-09-02 Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его snum. А теперь посмотрим на вложенный запрос:
SELECT amt, odate FROM orders where snum = (SELECT snum FROM salespeople WHERE sname = 'Колованов')В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы salespeople, а затем, в таблице orders определяем по этому идентификатору нужные нам значения. Таким образом работают вложенные запросы SQL.
Рассмотрим еще один пример:
Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.SELECT snum, sname FROM salespeople where snum IN (SELECT snum FROM orders WHERE YEAR(odate) = 2016)Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN. Его следует использовать в том случае, если вложенный подзапрос SQL возвращает несколько значений. То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы salespeople в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.
Получился такой результат:
snum sname 3 Плотников 4 Кучеров 7 Мозякин 8 Проворов Вложенные запросы SQL с несколькими параметрами
Те примеры, которые мы уже рассмотрели, сравнивали в условии WHERE одно поле. Это конечно хорошо, но стоит отметить, что в SQL предусмотрена возможность сравнения сразу нескольких полей, то есть можно использовать вложенный запрос с несколькими параметрами.
Вывести пары покупателей и продавцов, которые осуществили сделку между собой в 2017 году.
Запрос чем то похож на предыдущий, только теперь мы добавляем еще одно поле для сравнения. Итоговый запрос SQL будет выглядеть таким образом:
SELECT cname as 'Покупатель', sname as 'Продавец' FROM customers cus, salespeople sal where (cus.cnum, sal.snum) IN (SELECT cnum, snum FROM orders WHERE YEAR(odate) = 2017)
Покупатель Продавец Краснов Колованов Колесников Кучеров Лермонтов Колованов Кириллов Мозякин В этом примере мы сравниваем сразу два поля одновременно по идентификаторам. То есть из таблицы orders берутся те строки, которые удовлетворяют условию по 2017 году, затем вместо идентификаторов подставляются значение имен покупателей и продавцов.
На самом деле, такой запрос SQL используется крайне редко, обычно используют оператор INNER JOIN, о котором будет сказано в следующей статье.
Дополнительно скажем о конструкциях, которые использовались в этом запросе. Оператор as нужен для того, чтобы при выводе SQL показывал не имена полей, а то, что мы зададим. И после оператора FROM за именами таблиц стоят сокращения, которые потом используются — это псевдонимы. Псевдонимы можно называть любыми именами, в этом запросе они используются для явного определения поля, так как мы несколько раз обращаемся к одному и тому же полю, только из разных таблиц.
Примеры на вложенные запросы SQL
1.Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателя с фамилией Краснов. Предположим, что вы не знаете номера этого покупателя, указываемого в поле cnum.
SELECT * FROM orders where cnum = (SELECT cnum FROM customers WHERE cname = 'Краснов')2. Напишите запрос, который вывел бы имена и рейтинг всех покупателей, которые имеют Заказы, сумма которых выше средней.
SELECT cname, rating FROM customers where cnum IN (SELECT cnum FROM orders WHERE amt > (SELECT AVG(amt) from orders))3. Напишите запрос, который бы выбрал общую сумму всех приобретений в Заказах для каждого продавца, у которого эта общая сумма больше, чем сумма наибольшего Заказа в таблице.
SELECT snum, SUM(AMT) FROM orders GROUP BY snum HAVING SUM(amt) > (SELECT MAX(amt) FROM orders)4. Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателей проживающих в Москве.
SELECT * FROM orders where cnum IN (SELECT cnum FROM customers WHERE city = 'Москва')5. Используя подзапрос определить дату заказа, имеющего максимальное значение суммы приобретений (вывести даты и суммы приобретений).
SELECT amt, odate FROM orders WHERE AMT = (SELECT MAX(AMT) FROM orders)6. Определить покупателей, совершивших сделки с максимальной суммой приобретений.
SELECT cname FROM customers WHERE cnum IN (SELECT cnum FROM orders WHERE amt = (SELECT MAX(amt) FROM orders))Заключение
На этом сегодня все, мы познакомились с вложенными запросам в SQL. Очевидно, что это достаточно удобный и понятный способ получения данных из таблиц, но не всегда рационален с точки зрения скорости и нагрузки на сервер. Основные примеры, которые мы разобрали, действительно встречаются на практике языка SQL.
Поделиться ссылкой:
Предыдущая запись
