Запрос в запросе sql

Здравствуйте, уважаемые читатели! В этой статье мы поговорим о том, что такое вложенные запросы в SQL. Традиционно, рассмотрим несколько примеров с той базой данных, которую создавали в первых статьях.

Введение

Итак, само название говорит о том, что запрос во что-то вложен. Так вот, вложенный запрос в SQL означает, что запрос select выполняется в еще одном запросе select — на самом деле вложенность может быть и многоуровневой, то есть select в select в select и т.д.

Такие запросы обычно используются для получения данных из двух и более таблиц. Они нужны чтобы данные из разных таблиц можно было соотнести и по зависимости осуществить выборку. У вложенных запросов есть и недостаток — зачастую слишком долгое время работы занимает запрос, потому что идет большая нагрузка на сервер. Тем не менее, саму конструкцию необходимо знать и использовать при возможности.

Структура ранее созданных таблиц

Прежде чем перейдем к простому примеру, напомним структуру наших таблиц, с которыми будем работать:

— Таблица Salespeole (продавцы):

snum	sname	city	comm
1	Колованов	Москва	10
2	Петров	Тверь	25
3	Плотников	Москва	22
4	Кучеров	Санкт-Петербург	28
5	Малкин	Санкт-Петербург	18
6	Шипачев	Челябинск	30
7	Мозякин	Одинцово	25
8	Проворов	Москва	25

— Таблица Customers (покупатели):

сnum	сname	city	rating	snum
1	Деснов	Москва	90	6
2	Краснов	Москва	95	7
3	Кириллов	Тверь	96	3
4	Ермолаев	Обнинск	98	3
5	Колесников	Серпухов	98	5
6	Пушкин	Челябинск	90	4
7	Лермонтов	Одинцово	85	1
8	Белый	Москва	89	3
9	Чудинов	Москва	96	2
10	Лосев	Одинцово	93	8

— Таблица Orders (заказы)

onum	amt	odate	cnum	snum
1001	128	2016-01-01	9	4
1002	1800	2016-04-10	10	7
1003	348	2017-04-08	2	1
1004	500	2016-06-07	3	3
1005	499	2017-12-04	5	4
1006	320	2016-03-03	5	4
1007	80	2017-09-02	7	1
1008	780	2016-03-07	1	3
1009	560	2017-10-07	3	7
1010	900	2016-01-08	6	8

Основы вложенных запросов в SQL

Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов.

Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу Salespeople, определили бы snum продавца Колыванова — он равен 1. И выполнили бы запрос SQL с помощью условия WHERE. Вот пример такого SQL запроса:

Очевидно, какой будет вывод:

amt	odate
348	2017-04-08
80	2017-09-02

Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его snum. А теперь посмотрим на вложенный запрос:

В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы salespeople, а затем, в таблице orders определяем по этому идентификатору нужные нам значения. Таким образом работают вложенные запросы SQL.

Рассмотрим еще один пример:
Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.

Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN. Его следует использовать в том случае, если вложенный подзапрос SQL возвращает несколько значений. То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы salespeople в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.

Получился такой результат:

snum	sname
3	Плотников
4	Кучеров
7	Мозякин
8	Проворов

Вложенные запросы SQL с несколькими параметрами

Те примеры, которые мы уже рассмотрели, сравнивали в условии WHERE одно поле. Это конечно хорошо, но стоит отметить, что в SQL предусмотрена возможность сравнения сразу нескольких полей, то есть можно использовать вложенный запрос с несколькими параметрами.

Вывести пары покупателей и продавцов, которые осуществили сделку между собой в 2017 году.

Запрос чем то похож на предыдущий, только теперь мы добавляем еще одно поле для сравнения. Итоговый запрос SQL будет выглядеть таким образом:

Покупатель	Продавец
Краснов	Колованов
Колесников	Кучеров
Лермонтов	Колованов
Кириллов	Мозякин

В этом примере мы сравниваем сразу два поля одновременно по идентификаторам. То есть из таблицы orders берутся те строки, которые удовлетворяют условию по 2017 году, затем вместо идентификаторов подставляются значение имен покупателей и продавцов.

На самом деле, такой запрос SQL используется крайне редко, обычно используют оператор INNER JOIN, о котором будет сказано в следующей статье.

Дополнительно скажем о конструкциях, которые использовались в этом запросе. Оператор as нужен для того, чтобы при выводе SQL показывал не имена полей, а то, что мы зададим. И после оператора FROM за именами таблиц стоят сокращения, которые потом используются — это псевдонимы. Псевдонимы можно называть любыми именами, в этом запросе они используются для явного определения поля, так как мы несколько раз обращаемся к одному и тому же полю, только из разных таблиц.

Примеры на вложенные запросы SQL

1.Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателя с фамилией Краснов. Предположим, что вы не знаете номера этого покупателя, указываемого в поле cnum.

2. Напишите запрос, который вывел бы имена и рейтинг всех покупателей, которые имеют Заказы, сумма которых выше средней.

3. Напишите запрос, который бы выбрал общую сумму всех приобретений в Заказах для каждого продавца, у которого эта общая сумма больше, чем сумма наибольшего Заказа в таблице.

4. Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателей проживающих в Москве.

5. Используя подзапрос определить дату заказа, имеющего максимальное значение суммы приобретений (вывести даты и суммы приобретений).

6. Определить покупателей, совершивших сделки с максимальной суммой приобретений.

Заключение

На этом сегодня все, мы познакомились с вложенными запросам в SQL. Очевидно, что это достаточно удобный и понятный способ получения данных из таблиц, но не всегда рационален с точки зрения скорости и нагрузки на сервер. Основные примеры, которые мы разобрали, действительно встречаются на практике языка SQL.

Что такое подзапрос в SQL?

• SQL подзапрос — это запрос, вложенный в другой запрос;
• Подзапрос может использоваться:

o В инструкции SELECT ;
o В инструкции FROM ;
o В условии WHERE .

• Подзапрос может быть вложен в инструкции SELECT , INSERT , UPDATE или DELETE , а также в другой подзапрос;
• Подзапрос обычно добавляется в условие WHERE оператора SQL SELECT ;
• Можно использовать операторы сравнения, такие как >, ANY или ALL ;
• Подзапрос также называется внутренним запросом. Оператор, содержащий подзапрос, также называется внешним;
• Внутренний запрос выполняется перед родительским запросом, чтобы результаты его работы могли быть переданы внешнему.

Подзапрос можно использовать в инструкциях SELECT , INSERT , DELETE или UPDATE для выполнения следующих задач:

• Сравнения выражения с результатом запроса;
• Определения того, включено ли выражение в результаты запроса;
• Проверки того, выбирает ли запрос любые строки.

• Подзапрос SQL ( внутренний запрос ) выполняется перед выполнением основного запроса ( внешнего запроса );
• Основной запрос использует результат выполнения подзапроса.

Примеры подзапросов SQL

В этом разделе мы рассмотрим, как использовать подзапросы. У нас есть следующие две таблицы: ‘ student ‘ и ‘ marks ‘ с общим полем ‘ StudentID ‘:

Теперь нужно составить запрос, определяющий всех студентов, которые получают лучшие отметки, чем студент со StudentID — « V002 ». Но мы не знаем отметок студента « V002 ».

Поэтому нужно составить два SQL подзапроса в Select . Один запрос возвращает отметки ( хранятся в поле « Total_marks » ) для « V002 », а второй запрос выбирает учеников, которые получают лучшие оценки, чем результат первого запроса.

Результатом запроса будет 80 .

Используя результат этого запроса, мы написали еще один запрос, чтобы определить учеников, которые получают оценки лучше, чем 80 .

Два приведенных запроса определяют студентов, которые получают лучше оценки, чем студент StudentID « V002 » ( Abhay ).

Можно объединить эти два запроса, вложив один запрос в другой. Подзапрос — это запрос внутри круглых скобок. Рассмотрим подзапроса в SQL пример :

Графическое представление подзапроса SQL :

Подзапросы: общие правила

Ниже приведен синтаксис подзапроса :

Подзапросы: рекомендации по использованию

Ниже приведен ряд рекомендаций, которым нужно следовать при использовании SQL подзапросов:

• Подзапрос должен быть заключен в круглые скобки;
• Подзапрос должен указываться в правой части оператора сравнения;
• Подзапросы не могут обрабатывать свои результаты, поэтому в подзапрос не может быть добавлено условие ORDER BY ;
• Используйте однострочные операторы с однострочными подзапросами;
• Если подзапрос возвращает во внешний запрос значение null , внешний запрос не будет возвращать никакие строки при использовании операторов сравнения в условии WHERE .

Типы подзапросов

• Однострочный подзапрос : возвращает ноль или одну строку;
• Многострочный подзапрос : возвращает одну или несколько строк;
• Многостолбцовый подзапрос : возвращает один или несколько столбцов;
• Коррелированные подзапросы : указывают один или несколько столбцов во внешней инструкции SQL . Такой подзапрос называется коррелированным, поскольку он связан с внешней инструкцией SQL ;
• Вложенные подзапросы : подзапросы помещенные в другой подзапрос.

Также можно использовать подзапрос внутри инструкций INSERT , UPDATE и DELETE .

Подзапросы с инструкцией INSERT

Инструкция INSERT может использоваться с подзапросами SQL .

Если мы хотим вставить заказы из таблицы ‘ orders ‘, для которых в таблице « neworder » значение advance_amount составляет 2000 или 5000 , можно использовать следующий код 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

Подзапросы с инструкцией UPDATE

В инструкции UPDATE можно установить новое значение столбца, равное результату, возвращаемому однострочным подзапросом. Ниже приводится синтаксис и пример UPDATE с подзапросом SQL .

Если мы хотим изменить параметры 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

Подзапросы с инструкцией DELETE

Ниже приводится синтаксис и пример использования SQL подзапросов с инструкцией DELETE .

Если нужно удалить заказы из таблицы « 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

Данная публикация представляет собой перевод статьи « SQL Subqueries » , подготовленной дружной командой проекта Интернет-технологии.ру

С помощью SQL вы можете вкладывать запросы внутрь друга друга. Обычно, внутренний запрос генерирует значение которое проверяется в предикате внешнего запроса, определяющего верно оно или нет. Например, предположим что мы знаем им продавца: Motika, но не знаем значение его пол snum, и хотим извлечь все порядки из таблицы Порядков. Имеется один способ чтобы сделать это( вывод показывается в Рисунке 10.1 ): Чтобы оценить внешний( основной ) запрос, SQL сначала должен оценить внутренний запрос ( или подзапрос ) внутри предложения WHERE. Он делает это так как и должен делать запрос имеющий единственную цель – отыскать через таблицу Продавцов все строки, где поле sname равно значению Motika, и затем извлечь значения пол snum этих строк. Единственной найденной строкой естественно будет snum = 1004. Однако SQL, не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предиката прочитал что Рисунок 10.1: Использование подзапроса

Основной запрос затем выполняется как обычно с вышеупомянутыми результатами. Конечно же, подзапрос должен выбрать один и только один столбец, а тип данных этого столбца должен совпадать с тем значением с которым он будет сравниваться в предикате. Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена( в этом случае, snum ), но это необязательно.

Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать WHERE snum = 1004 и выполнять далее с подзапросом в целом, но это было бы не так универсально. Это будет продолжать работать даже если номер Motika изменился, а, с помощью простого изменения имени в подзапросе, вы можете использовать его для чего угодно.

ЗНАЧЕНИЯ, КОТОРЫЕ МОГУТ ВЫДАВАТЬ ПОДЗАПРОСЫ

Скорее всего было бы удобнее, чтобы наш подзапрос в предыдущем примере возвращал одно и только одно значение.

Имея выбранным поле snum " WHERE city = "London" вместо "WHERE sname = ‘Motika", можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки верности или неверности, и команда выдаст ошибку.

При использовании подзапросов в предикатах основанных на реляционных операторах ( уравнениях или неравенствах, как объяснено в Главе 4 ), вы должны убедиться что использовали подзапрос который будет выдавать одну и только одну строку вывода. Если вы используете подзапрос который не выводит никаких значений вообще, команда не потерпит неудачи; но основной запрос не выведет никаких значений. Подзапросы которые не производят никакого вывода (или нулевой вывод) вынуждают рассматривать предикат ни как верный ни как неверный, а как неизвестный. Однако, неизвестный предикат имеет тот же самый эффект что и неверный: никакие строки не выбираются основным запросом ( смотри Главу 5 для подробной информации о неизвестном предикате ).

Это плоха стратеги, чтобы делать что-нибудь подобное следующему: Поскольку мы имеем только одного продавца в Barcelona – Rifkin, то подзапрос будет выбирать одиночное значение snum и следовательно будет принят. Но это – только в данном случае. Большинство SQL баз данных имеют многочисленных пользователей, и если другой пользователь добавит нового продавца из Barcelona в таблицу, подзапрос выберет два значения, и ваша команда потерпит неудачу.

DISTINCT С ПОДЗАПРОСАМИ

Вы можете, в некоторых случаях, использовать DISTINCT чтобы вынудить подзапрос генерировать одиночное значение. Предположим что мы хотим найти все порядки кредитований для тех продавцов которые обслуживают Hoffmanа ( cnum = 2001 ).

Имеется один способ чтобы сделать это ( вывод показывается в Рисунке 10.2 ): Рисунок 10.2: Использование DISTINCT чтобы вынудить получение одного значения из подзапроса

Подзапрос установил что значение пол snum совпало с Hoffman – 1001, и затем основной запрос выделил все порядки с этим значением snum из таблицы Порядков( не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен к одному и только этому продавцу, мы знаем что каждая строка в таблице Порядков с данным значением cnum должна иметь такое же значение snum. Однако так как там может быть любое число таких строк, подзапрос мог бы вывести много ( хотя и идентичных ) значений snum для данного пол cnum. Аргумент DISTINCT предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных – хороша вещь для знающих об этом.

Альтернативный подход должен быть чтобы ссылаться к таблице Заказчиков а не к таблице Порядков в подзапросе. Так как поле cnum – это первичный ключ таблицы Заказчика, запрос выбирающий его должен произвести только одно значение. Это рационально только если вы как пользователь имеете доступ к таблице Порядков но не к таблице Заказчиков. В этом случае, вы можете использовать решение которое мы показали выше. ( SQL имеет механизмы которые определяют – кто имеет привилегии чтобы делать что-то в определенной таблице. Это будет объясняться в Главе 22.) Пожалуйста учтите, что методика используемая в предшествующем примере применима только когда вы знаете, что два различных пол в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных, она является исключением из правил.

ПРЕДИКАТЫ С ПОДЗАПРОСАМИ ЯВЛЯЮТСЯ НЕОБРАТИМЫМИ

ИСПОЛЬЗОВАНИЕ АГРЕГАТНЫХ ФУНКЦИЙ В ПОДЗАПРОСАХ

Один тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, – агрегатная функция.

Любой запрос использующий одиночную функцию агрегата без предложения GROUP BY будет выбирать одиночное значение для использования в основном предикате. Например, вы хотите увидеть все порядки имеющие сумму приобретений выше средней на 4-е Октября ( вывод показан на Рисунке 10.3 ): Рисунок 10.3: Выбор всех сумм со значением выше средней на 10/04/1990 Средняя сумма приобретений на 4 Октября – 1788.98 ( 1713.23 + 75.75) делится пополам, что в целом равняется = 894.49. Все строки со значением в поле amt выше этого – являются выбранными.

Имейте ввиду что сгруппированные агрегатные функции, которые являются агрегатными функциями определенными в терминах предложения GROUP BY, могут производить многочисленные значения. Они, следовательно, не позволительны в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Вы должны использовать одиночную агрегатную функцию с предложением WHERE что устранит нежелательные группы. Например, следующий запрос который должен найти сред- нее значение комиссионных продавца в Лондоне – не может использоваться в подзапросе! Во всяком случае это не лучший способ формировать запрос. Другим способом может быть –

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ КОТОРЫЕ ВЫДАЮТ МНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА IN

Вы можете использовать подзапросы которые производят любое число строк если вы используете специальный оператор IN ( операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами ). Как вы помните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в порядке, чтобы предикат был верным. Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой же подзапрос который не будет работать с реляционным оператором, и найти все атрибуты таблицы Порядков для продавца в Лондоне ( вывод показывается в Рисунке 10.4 ): Рисунок 10. 4: Использование подзапроса с IN

В ситуации подобно этой, подзапрос – более прост для пользователя чтобы понимать его и более прост для компьютера чтобы его выполнить, чем если бы Вы использовали объединение: Хотя это и произведет тот же самый вывод что и в примере с подзапросом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эффективнее извлекать из таблицы Продавцов значения пол snum где city = "London", и затем искать эти значения в таблице Порядков, как это делается в варианте с подзапросом. Внутренний запрос дает нам snums=1001 и snum=1004. Внешний запрос, затем, дает нам строки из таблицы Порядков где эти пол snum найдены. Строго говор, быстрее или нет работает вариант подзапроса, практически зависит от реализации – в какой программе вы это используете. Эта часть вашей программы называемой – оптимизатор, пытается найти наиболее эффективный способ выполнения ваших запросов.

Хороший оптимизатор во всяком случае преобразует вариант объединения в подзапрос, но нет достаточно простого способа для вас чтобы выяснить выполнено это или нет. Лучше сохранить ваши запросы в памяти чем полагаться полностью на оптимизатор.

Конечно вы можете также использовать оператор IN, даже когда вы уверены что подзапрос произведет одиночное значение. В любой ситуации где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов; если вы полагаете что подзапрос собирается произвести только одно значение, а он производит различные. Вы не сможете объяснить различи в выводе основного запроса. Например, рассмотрим команду, которая похожа на предыдущую: Вы можете устранить потребность в DISTINCT используя IN вместо (=), подобно этому: Что случится если есть ошибка и один из порядков был аккредитован к различным продавцам? Версия использующая IN будет давать вам все порядки для обоих продавцов. Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения сделанные на основе этого запроса не будут содержать ошибки. Вариант использующий ( = ) , просто потерпит неудачу.

Это, по крайней мере, позволило вам узнать что имеется такая проблема. Вы должны затем выполнять поиск неисправности, выполнив этот подза- прос отдельно и наблюдая значения которые он производит.

В принципе, если вы знаете что подзапрос должен( по логике) вывести только одно значение, вы должны использовать = . IN является подходящим, если запрос может ограниченно производить одно или более значений, независимо от того ожидаете вы их или нет. Предположим, мы хотим знать комиссионные всех продавцов обслуживаю- щих заказчиков в Лондоне: Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel ( snum = 1001 ), который имеет обоих заказчиков в Лондоне. Это – только для данного случая. Нет никакой причины чтобы некоторые заказчики в Лондоне не могли быть назначенными к кому-то еще. Следовательно, IN – это наиболее логична форма чтобы использовать ее в запросе. Рисунок 10.5 Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для пол city необязателен в предыду- щем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов. SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в предложении WHERE означает что имеется ссылка к Customer.city( поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает что это – правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже когда будем говорить об соотнесенных подзапросах. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.

ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫ

ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХ

Вы можете использовать выражение основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов или с IN. Например, следующий запрос использует реляционный оператор = ( вывод показывается в Рисунке 10.6 ): Он находит всех заказчиков чье значение пол cnum равное 1000, выше пол snum Serres. Мы предполагаем что столбец sname не имеет никаких двойных значений ( это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18 ); иначе Рисунок 10.6: Использование подзапроса с выражением

подзапрос может произвести многочисленные значения. Когда пол snum и сnum не имеют такого простого функционального значения как например первичный ключ , что не всегда хорошо, запрос типа вышеупомянутого невероятно полезен.

ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING

Вы можете также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции если они не производят многочисленных значений или использовать GROUP BY или HAVING. Следующий запрос является этому примером ( вывод показывается в Рисунке 10.7 ): Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San Jose

Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose. Так как имеются другие оценки отличные от 300, они должны быть выведены с числом номеров заказчиков которые имели эту оценку.

РЕЗЮМЕ

Теперь вы используете запросы в иерархической манере. Вы видели, что использование результата одного запроса для управления другим, расширяет возможности позволяющие выполнить большее количество функций. Вы теперь понимаете как использовать подзапросы с реляционными опера- торами также как и со специальным оператором IN, или в предложении WHERE или в предложении HAVING внешнего запроса.

В следующих главах, мы будем разрабатывать подзапросы. Сначала в Главе 11, мы обсудим другой вид подзапроса, который выполняется отдельно для каждой строки таблицы вызываемой во внешнем запросе. Затем, в Главе 12 и 13, мы представим вас нескольким специальным операто- рам которые функционируют на всех подзапросах, как это делает IN, за исключением когда эти операторы могут использоваться только в подзапросах.

РАБОТА С SQL

1. Напишите запрос, который бы использовал подзапрос для получения всех порядков для заказчика с именем Cisneros. Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum.

2. Напишите запрос который вывел бы имена и оценки всех заказчиков которые имеют усредненные порядки.

3. Напишите запрос который бы выбрал общую сумму всех приобретений в порядках для каждого продавца, у которого эта обща сумма больше чем сумма наибольшего порядка в таблице.