Помещение одного запроса внутрь другого


Содержание

Простые запросы

Для наспростым запросом будет запрос, который обращается только к одной таблице базы данных. Простые запросы помогут нам проиллюстриро­вать основную структуру SQL.

Простой запрос. Запрос, который обращается только к одной таблице базы данных.

Запрос: Кто работает штукатурами?

WHERE SKILL_TYPE = ‘Штукатур’

Результат:

Этот запрос иллюстрирует три наиболее часто встречающиеся фразы SQL: SELECT, FROM и WHERE. Хотя в нашем примере мы поместили их на разные строки, они все могут стоять в одной строке. Они также могут поме­щаться с разными отступами, а слова внутри фраз могут разделяться произ­вольным числом пробелов. Рассмотрим характеристики каждой фразы.

Select. Фраза SELECT перечисляет столбцы, которые должны войти в результирующую таблицу. Это всегда столбцы некоторой реляционной таб­лицы. В нашем примере результирующая таблица состоит из одного столбца (NAME), но в общем случае она может содержать несколько столбцов; она также может содержать вычисленные значения или константы. Мы приве­дем примеры каждого из этих вариантов. Если результирующая таблица должна содержать более одного столбца, то все нужные столбцы перечисля­ются после команды SELECT через запятую. Например, фраза SELECT WORKER_ID, NAME выдаст в результате таблицу, состоящую из столбцов WORKER_ID и NAME.

Фраза SELECT. Задает столбцы результирующей таблицы.

From. Фраза FROM задает одну или более таблиц, к которым обраща­ется запрос. Все столбцы, перечисленные во фразах SELECT и WHERE, должны существовать в одной из таблиц, перечисленных в команде FROM. В SQL2 эти таблицы могут быть напрямую определены в схеме как базовые таблицы или представления данных, или же они сами могут быть не имею­щими имен таблицами, полученными в результате запросов SQL. В послед­нем случае запрос явно приводится в команде FROM.

Фраза FROM. Задает существующие таблицы, к которым обращается запрос.

Where. Фраза WHERE содержит условие. на основании которого выби­раются строки таблицы (таблиц). В нашем примере условие состоит в том, что столбец SKILL_TYPE должен содержать константу ‘Штукатур’, заклю­ченную в апострофы, как это всегда делается с текстовыми константами в SQL. Фраза WHERE — наиболее изменчивая команда SQL; она может со­держать множество разнообразных условий. Большая часть нашего изложе­ния будет посвящена иллюстрации различных конструкций, разрешенных в команде WHERE.

Фраза WHERE.Задает условие, на основании которого выбираются строки из заданных таблиц.

Приведенный выше запрос SQL обрабатывается системой в следующем порядке: FROM, WHERE, SELECT. To есть строки таблицы, указанной в ко­манде FROM, помещаются в рабочую область для обработки. Затем к каждой строке последовательно применяется фраза WHERE. Все строки, не удовле­творяющие условию WHERE, исключаются из рассмотрения. Затем те строки, которые удовлетворяют условию WHERE, обрабатываются командой SELECT. В нашем примере из каждой такой строки выбирается NAME, и все выбранные значения выводятся в качестве результатов запроса.

Запрос: Привести все данные о зданиях офисов.

WHERE TYPE = ‘Офис’

BLDG IDАДРЕСTYPEQLTY LEVELSTATUS

312 Ул.Вязов, 123 Офис 2 2

210 Березовая ул. 1011 Офис З 1

111 Осиновая ул. 1213 Офис 4 1

Звездочка (*) в команде SELECT означает «строка целиком». Это удобное сокращение, которым мы будем часто пользоваться.

Запрос: Какова недельная зарплата каждого электрика?

SELECT NAME, ‘Недельная зарплата = ‘, 40 * HRLY_RATE

WHERE SKILL_TYPE = ‘Электрик’

Результат:

М.Фарадей Недельная зарплата = 500.00

Х.Колумб Недельная зарплата = 620.00

Этот запрос иллюстрирует употребление и символьных констант (в на­шем примере ‘Недельная зарплата = ‘), и вычислений в команде SELECT, Внутри команды SELECT можно производить вычисления, в которых ис­пользуются числовые столбцы и числовые константы, а также стандартные арифметические операторы (+, -, *, /), сгруппированные по мере необходи­мости с помощью скобок. Мы также включили новую команду ORDER BY, которая сортирует результат запроса в возрастающем алфавитно-числовом порядке по указанному столбцу. Если вы хотите упорядочивать результаты по убыванию, то к команде нужно добавить DESC. Фраза ORDER BY может сортировать результаты по нескольким столбцам, по одним — в порядке возрастания, по другим — в порядке убывания. Первым указывается столбец первичного ключа сортировки.

Символьная константа. Константа, состоящая из букв, цифр и «специальных» символов.

Запрос: У кого почасовая ставка от 10 до 12 долларов?

WHERE HRLY_RATE > = 10 AND HRLY_RATE (не равно), , =). Для создания составных условий или для отрицания условия могут использо­ваться булевы операции AND (И), OR (ИЛИ) и NOT (HE). Для группировки условий, как обычно в языках программирования, могут использоваться скобки.

Операторы сравнения =, <>, , =.

Булевы операцииAND (И), OR (ИЛИ) и NOT (HE).

Для формулировки этого запроса также можно былоиспользоватьоператор BETWEEN (между):

WHERE HRLY_RATE BETWEEN 10 AND 12

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

Запрос: Перечислить штукатуров, кровельщиков и электриков.

WHERE SKILL_TYPE IN (‘Штукатур’, ‘Кровельщик’, ‘Электрик’)

WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Электрик 1311

1412 К.Немо 13.75 Штукатур 1520

2920 Р.Гаррет 10.00 Кровельщик 2920

1520 Г.Риковер 11.75 Штукатур 1520

1311 Х.Колумб 15.50 Электрик 1311

Этот запрос поясняет использование оператора сравнения IN (В). Усло­вие WHERE считается истинным, если тип специальности строки располо­жен внутри множества, указанного в скобках, то есть если тип специаль­ности — штукатур, кровельщик или электрик. Мы еще встретимся с опера­тором IN в подзапросах.

Предположим, что мы не можем точно вспомнить написание специаль­ности: «электрик» или «электронщик» или еще как-то. Символы шаблона, которые замещают неопределенные строки символов, облегчают поиск не­точного написания в запросе.

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

Запрос: Перечислить работников, чей тип специальности начинается с «Элек».

WHERE SKILL_TYPE LIKE (‘Элек%’)

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Электрик 1311

1311 Х.Колумб 15.50 Электрик 1311

В SQL есть два символа шаблона: % (процент) и _ (подчеркивание). Подчеркивание замещает ровно один неопределенный символ. Процент за­мещает произвольное число символов, начиная с нуля. Когда используются символы шаблона, для сравнения символьных переменных с константами требуется оператор LIKE (как). Другие примеры:

NAME LIKE ‘__Колумб’

Условие в первом примере истинно, если NAME состоит из двух симво­лов, за которыми следует ‘Колумб’. В таблице WORKER все имена начина­ются с первого инициала и точки. Таким образом, при помощи этого усло­вия мы. найдем всех работников по фамилии «Колумб». Условие второго примера позволяет найти всех работников, чьи фамилии начинаются на бу­кву «К».

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

WHERE START _DATE BETWEEN CURRENT_DATE AND

CURRENT_DATE + INTERVAL ’14’ DAY

Результат: (Предположим, что текущая дата CURRENT DATE = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Этот запрос иллюстрирует употребление оператора BETWEEN (между) со значениями типа date (дата) и interval (промежуток). CURRENT_DATE — это функция, всегда возвращающая значение сегодняшней даты. Выражение

CURRENT_DATE + INTERVAL ’14’ DAY

прибавляет двухнедельный промежуток к текущей дате. Таким образом, ASSIGNMENT выбирается (в предположении, что сегодня 10.10) в том слу­чае, если в ней значение столбца START_DATE лежит между 10.10 и 24.10. Из этого видно, что мы можем прибавлять к полям дат величины типа interval. Более того, мы можем умножать значения промежутков на целые величины. Например, предположим, что мы хотим выяснить, какое число будет через определенное количество недель (обозначенное переменной NUM_WEEKS (ЧИСЛО НЕДЕЛЬ)). Мы можем это сделать так:

CURRENT_DATE + INTERVAL ‘7’ DAY * NUM_WEEKS

Задание 1. Выполните следующие запросы к базе данных «Перевозки».

1. Название клиента 680.

2. Какой пункт назначения груза № 3244?

3. Перечислить номера грузовиков, перевозивших грузы весом более 100 фунтов

4. Выдать все данные о грузах более 20 фунтов весом.

5. Создать алфавитный список клиентов с годовым доходом более 100000 долларов.

6. Какой ИД клиента у «Братьев Уилсон»?

7. Выдать названия и средний месячный доход клиентов, имеющих годовой доход свыше 10 миллионов долларов, но менее 50 миллионов долларов.

8. Выдать ИД клиентов, отправлявших грузы в Атланту, Сент-Луис или Балтимор.

9. Выдать названия клиентов, отправлявших грузы в города, названия которых начинаются на «С».

10. Выдать названия клиентов, отправлявших грузы в города, названия которых заканчиваются «-Сити».

11. Выдать названия клиентов, в названии которых третья буква — заглавная «М».

12. Выдать названия всех клиентов — торговых фирм.

2. Многотабличные запросы

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

Запрос: Каковы специальности рабочих, назначенных на здание 435?

Данные, необходимые для ответа, находятся в двух таблицах: WORKER и ASSIGNMENT. Для решения в SQL требуется перечислить обе таблицы в команде FROM и задать специальный тип условия WHERE:

FROM WORKER, ASSIGNMENT

Что здесь происходит? Мы должны рассмотреть два этапа обработки сис­темой данного запроса.

1. Как обычно, сначала обрабатывается фраза FROM. Однако в этом слу­чае, поскольку в команде указаны две таблицы, система создает декар­тово произведение строк этих таблиц. Это означает, что создается (логически) одна большая таблица, состоящая из столбцов обеих таб­лиц, в которой каждая строка одной таблицы спарена с каждой стро­кой другой таблицы. В нашем примере, поскольку в таблице WORKER пять столбцов, а в таблице ASSIGNMENT четыре столбца, в декартовом произведении, созданном командой FROM, будет девять столбцов. Общее число строк декартова произведения равно m * n, где m — число строк таблицы WORKER; а n — число строк таблицы ASSIGNMENT. По­скольку в таблице WORKER 7 строк, а в таблице ASSIGNMENT 19 строк, то декартово произведение будет содержать 7х19 или 133 строки. Если в команде FROM перечислено более двух таблиц, то создается декартово произве­дение всех таблиц, указанных в команде.

Декартово произведение. Результат объединения каждой строки одной таблицы с каждой строкой другой таблицы.

2. После создания гигантской реляционной таблицы система, как и пре­жде, применяет команду WHERE. Каждая строка таблицы, созданной командой FROM. проверяется на выполнение условия WHERE. Строки, не удовлетворяющие условию, исключаются из рассмотрения. Затем к оставшимся строкам применяется фраза SELECT.

Фраза WHERE в нашем запросе содержит два условия:

1. WORKER. WORKER_ >

Первое из этих условий — условие соединения. Обратите внимание, что поскольку обе таблицы WORKER и ASSIGNMENT содержат столбец с име­нем WORKER_ID, их декартово произведение будет содержать два столбца с таким именем. Для того чтобы различать их, мы помещаем перед именем столбца имя исходной таблицы, отделяя его точкой.

Первое условие означает, что в любой выбранной строке значение столбца WORKER_ >

Рис. 1. Соединение таблиц WORKER и ASSIGNMENT

Теперь мы покажем, как в SQL присоединить таблицу к ней самой.

Запрос: Перечислить работников, указав имена их менеджеров.

SELECT А.WORKER_NAME, B.WORKER_NAME

FROM WORKER A, WORKER В

Фраза FROM в этом примере создает две «копии» таблицы WORKER, давая им псевдонимы А и В. Псевдоним — это альтернативное имя, данное таблице. Затем копии А и В таблицы WORKER соединяются командой WHERE на основании условия равенства WORKER_ID в В и SUPV_ID в А. Таким образом, каждая строка из А присоединяется к строке В, содержащей информацию о менеджере строки А (рис.2).

Рис. 2. Соединение двух копий таблицы WORKER

Выбирая из каждой строки два имени работника, мы получим требуемый список:

К.Немо Г.Риковер Р.Гаррет Р.Гаррет

П.Мэйсон П.Мэйсон Г.Риковер Г.Риковер Х.Колумб Х.Колумб Дж.Барристер П.Мэйсон

Псевдоним. Альтернативное имя, данное таблице.

A.WORKER_NAME представляет работника, a B.WORKER_NAME пред­ставляет менеджера. Обратите внимание, что некоторые работники — сами себе менеджеры, что следует из выполненного в их строках равенства WORKER_ID — SUPV_ID.

В SQL можно за один раз связать более двух таблиц:

Запрос: Перечислить работников, назначенных на здания офисов.

FROM WORKER, ASSIGNMENT, BUILDING

NAME

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

Команды SQL этого запроса создают одну таблицу из трех реляционных таблиц базы данных. Первые две таблицы соединяются по WORKER_ID, после чего к полученной таблице присоединяется третья таблица по BLDG_ID. Условие

команды WHERE приводит к исключению всех строк, кроме строк, относящихся к офисным зданиям. Это соответствует требованиям запроса.

Задание 2. Выполните следующие запросы к базе данных «Перевозки».

1. Каковы названия клиентов, отправлявших грузы в Солт-Лейк-Сити?

2. В какие пункты назначения компании с годовым доходом менее од­ного миллиона долларов отправляли грузы?

3. Названия и население городов, получавших грузы весом более 100 фунтов.

4. Кто из клиентов с годовым доходом более 10 миллионов долларов от­правлял грузы весом менее 100 фунта?

5. Кто из клиентов с годовым доходом более 10 миллионов долларов от­правлял грузы весом менее 100 фунта или отправлял грузы в Балтимор?

6. Кто из водителей доставлял грузы для клиентов с годовым доходом более 20 миллионов долларов в города с населением свыше одного миллиона человек?

Подзапрос.Запрос внутри запроса

Подзапрос может помещаться в команду WHERE запроса, в результате чего возможности команды WHERE расширяются. Рассмотрим пример.

Запрос: Каковы специальности рабочих, назначенных на здание 435?

FROM WORKER WHERE WORKER_ID IN

Подзапрос в этом примере

Запрос, в котором содержится подзапрос, называется внешним запросом или главным запросом. Подзапрос приводит к созданию следующего множе­ства ИД (идентификаторов) работников:

Внешний запрос. Главный запрос, в котором содержатся все подзапросы.

Затем это множество ИД занимает место подзапроса во внешнем запросе. С этого момента выполняется внешний запрос, использующий множество, созданное подзапросом. Внешний запрос обрабатывает каждую строку таб­лицы WORKER в соответствии с условием WHERE. Если WORKER_ID строки лежит в (IN) множестве, созданном подзапросом, то SKILL_TYPE строки выбирается и выводится в результирующей таблице:

Очень важно, что фраза SELECT подзапроса содержит WORKER_ID и только WORKER_ID. В противном случае фраза WHERE внешнего запроса, означающая, что WORKER_ID лежит в множестве ИД работников, не имела бы смысла.

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

Некоррелированный подзапрос.Подзапрос, значение которого не зависит ни от какого внешнего запроса.

Приведем пример подзапроса внутри подзапроса.

Запрос: Перечислить работников, назначенных на здания офисов.

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

Вставка одного запроса внутрь другого

Название Вставка одного запроса внутрь другого
Дата конвертации 21.07.2013
Размер 144.8 Kb.
Тип Документы
1. /SQL doc Новый/Глава 1.doc
2. /SQL doc Новый/Глава 10.doc
3. /SQL doc Новый/Глава 11.doc
4. /SQL doc Новый/Глава 12.doc
5. /SQL doc Новый/Глава 13.doc
6. /SQL doc Новый/Глава 14.doc
7. /SQL doc Новый/Глава 15.doc
8. /SQL doc Новый/Глава 16.doc
9. /SQL doc Новый/Глава 17.doc
10. /SQL doc Новый/Глава 18.doc
11. /SQL doc Новый/Глава 19.doc
12. /SQL doc Новый/Глава 2.doc
13. /SQL doc Новый/Глава 20.doc
14. /SQL doc Новый/Глава 21.doc
15. /SQL doc Новый/Глава 22.doc
16. /SQL doc Новый/Глава 23.doc
17. /SQL doc Новый/Глава 24.doc
18. /SQL doc Новый/Глава 25.doc
19. /SQL doc Новый/Глава 3.doc
20. /SQL doc Новый/Глава 4.doc
21. /SQL doc Новый/Глава 5.doc
22. /SQL doc Новый/Глава 6.doc
23. /SQL doc Новый/Глава 7.doc
24. /SQL doc Новый/Глава 8.doc
25. /SQL doc Новый/Глава 9.doc
26. /SQL doc Новый/Оглавление книги Основы SQL.doc
27. /SQL doc Новый/Оглавление книги Основы SQL1.txt
28. /SQL doc Новый/Приложение A.doc
29. /SQL doc Новый/Приложение B.doc
30. /SQL doc Новый/Приложение C.oc.DOC
31. /SQL doc Новый/Приложение D.doc
32. /SQL doc Новый/Приложение E.doc
Что такое «реляционная база данных»?
Вставка одного запроса внутрь другого
Соотнесённые подзапросы

Глава 10. Вставка одного запроса внутрь другого

В конце Главы 9 мы говорили, что запросы могут управлять другими запросами. В

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

внутрь предиката другого запроса и использования вывода внутреннего запроса в

верном или неверном условии предиката.

Вы сможете выяснить, какие виды операторов могут использовать подзапросы, и

посмотреть, как подзапросы работают со средствами SQL, такими как DISTINCT, с

составными функциями и выводимыми выражениями.

Вы узнаете, как использовать подзапросы с предложением HAVING, и получите

некоторые наставления, как правильно использовать подзапросы.

Как работает подзапрос?

С помощью SQL вы можете вкладывать запросы друга в друга. Обычно внутренний

запрос генерирует значение, которое проверяется в предикате внешнего запроса,

определяющего, верно оно или нет. Например, предположим, что мы знаем имя

продавца: Motika, но не знаем значение его поля snum и хотим извлечь все заказы

из таблицы Заказов. Вот способ сделать это (вывод показан на Рис. 10.1 ):

WHERE sname = ‘Motika’);

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний

запрос (или подзапрос) внутри предложения WHERE. Он делает это так, как и должен

делать запрос, имеющий единственную цель — отыскать через таблицу Продавцов все

строки, где поле sname равно значению Motika, а затем извлечь значения поля snum

Единственной найденной строкой, естественно, будет snum = 1004. Однако SQL не

просто выдает это значение, а помещает его в предикат основного запроса вместо

самого подзапроса, так чтобы предикат прочитал, что


WHERE snum = 1004

| WHERE sname = ‘Motika’); |

| onum amt odate cnum snum |

| 3002 1900.10 10/03/1990 2007 1004 |

Рисунок 10.1 Использование подзапроса

Основной запрос затем выполняется как обычно с вышеупомянутыми результатами.

Разумеется, подзапрос должен выбрать один, и только один, столбец, а тип данных

этого столбца должен совпадать с тем значением, с которым он будет сравниваться

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

имена (в данном случае snum), но это не обязательно. Конечно, если бы мы уже

знали номер продавца Motika, мы могли бы просто напечатать WHERE snum = 1004 и

работать далее с подзапросом в целом, но это было бы не так универсально. Этот

же запрос будет продолжать работать, даже если номер Motika изменился, а с

помощью простого изменения имени в подзапросе вы можете использовать его для

Значения, которые подзапрос может выводить

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

возвращал одно, и только одно, значение.

Имея выбранное поле snum » WHERE city = «London» вместо «WHERE sname = ‘Motika»,

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

основного запроса невозможным оценку верности или неверности, и команда выдаст

При использовании подзапросов в предикатах, основанных на реляционных операциях

(уравнениях или неравенствах, как объяснено в Главе 4), вы должны убедиться, что

использовали подзапрос, который будет выдавать одну, и только одну, строку

вывода. Если вы используете подзапрос, который не выводит никаких значений

вообще, команда не потерпит неудачи, но основной запрос не выведет никаких

значений. Подзапросы, которые не производят никакого вывода (или нулевой вывод),

вынуждают рассматривать предикат ни как верный, ни как неверный, а как

неизвестный. Однако неизвестный предикат имеет тот же самый эффект, что и

неверный: никакие строки не выбираются основным запросом (смотри в Главе 5

подробную информацию о неизвестном предикате).

Вот пример плохой стратегии:

WHERE city = Barcelona);

Поскольку мы имеем только одного продавца в Barcelona — Rifkin, то подзапрос

будет выбирать одиночное значение snum, и, следовательно, будет принят. Но это

только в данном случае. Большинство БД SQL имеют многочисленных пользователей,

и, если другой пользователь добавит нового продавца из Barcelona в таблицу,

подзапрос выберет два значения, и ваша команда потерпит неудачу.

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

В некоторых случаях вы можете использовать DISTINCT чтобы вынудить подзапрос

генерировать одиночное значение. Предположим что мы хотим найти все порядки

кредитования для тех продавцов, которые обслуживают Hoffman’а (cnum = 2001).

Вот способ сделать это (вывод показан на Рисунке 10.2):

(SELECT DISTINCT snum

WHERE cnum = 2001);

| (SELECT DISTINCT snum |

| 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 для получения одного значения из подзапроса

Подзапрос установил, что значение поля snum совпало с Hoffman — 1001, а затем

основной запрос выделил все заказы с этим значением snum из таблицы Заказов (не

разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен

продавцу, мы знаем, что каждая строка в таблице Заказов с данным значением cnum

должна иметь такое же значение snum. Однако, поскольку там может быть любое

число таких строк, подзапрос мог бы вывести много (хотя и идентичных) значений

snum для данного поля cnum. Аргумент DISTINCT предотвращает это. Если наш

подзапрос возвратит более одного значения, это будет указывать на ошибку в наших

данных — хорошая вещь для знающих об этом.

Должен быть и альтернативный подход, чтобы ссылаться к таблице Заказчиков, а не

к таблице Заказов в подзапросе. Так как поле cnum это первичный ключ таблицы

Заказчиков, запрос, выбирающий его, должен выдать только одно значение. Это

рационально, только если вы как пользователь имеете доступ к таблице Заказов, но

не к таблице Заказчиков. В этом случае вы можете использовать решение, которое

мы показали выше. (SQL имеет механизмы, которые определяют, кто имеет привилегии

на выполнение действий в определённой таблице. Это будет объясняться в Главе

Пожалуйста, учтите, что методика, используемая в предшествующем примере,

применима, только когда вы знаете, что два различных поля в таблице должны

всегда совпадать, как в нашем случае. Эта ситуация не является типичной в

реляционных базах данных (РБД), она является исключением из правил.

Предикаты с подзапросами являются необратимыми

Вы должны обратить внимание что предикаты, включающие подзапросы, используют

Другими словами, вы не должны записывать предыдущий пример так:

WHERE (SELECT DISTINCT snum

WHERE cnum = 2001)

В строгой ANSI-реализации это приведет к неудаче, хотя некоторые программы и

позволяют делать такие вещи. ANSI также предохраняет от появления в выводе

подзапроса обоих значений при сравнении.

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

Тип функций, который автоматически может производить одиночное значение для

любого числа строк, конечно же — агрегатная функция.

Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY,

будет выбирать одиночное значение для использования в основном предикате.

Например, вы хотите увидеть все заказы, имеющие сумму выше средней на 4-е

октября (вывод показан на Рисунке 10.3):

WHERE odate = 10/04/1990);

| 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 октября — 1788.98 (1713.23 + 75.75) делится

пополам, что в целом равняется 894.49. Все строки со значением в поле amt выше

этого являются выбранными. Имейте в виду, что сгруппированные агрегатные

функции, которые являются агрегатными функциями, определёнными в терминах

предложения GROUP BY, могут производить многочисленные значения. Они,

следовательно, недопустимы в подзапросах такого характера. Даже если GROUP BY и

HAVING используются таким способом, что только одна группа выводится с помощью

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

агрегатную функцию с предложением WHERE, что устранит нежелательные группы.

Например, следующий запрос который должен найти среднее значение комиссионных

продавца в Лондоне,

SELECT AVG (comm)

HAVlNG city = «London»;

не может использоваться в подзапросе! Во всяком случае, это не лучший способ

Другим способом может быть

SELECT AVG (comm)

WHERE city = «London»;

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

Вы можете использовать подзапросы, которые производят любое число строк, если вы

применяете специальный оператор IN (операторы BETWEEN, LIKE и IS NULL не могут

использоваться с подзапросами). Как вы помните, IN определяет набор значений,

одно из которых должно совпадать с другим термином уравнения предиката в заказе,

чтобы предикат был верным.

Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода

подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой

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

атрибуты таблицы Заказов для продавца в Лондоне (вывод показан на Рисунке 10.4):

WHERE city = «LONDON»);

| 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

должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и

проверить их снова по составному предикату. Проще и эффективнее извлекать из

таблицы Продавцов значения поля snum, где city = «London», а затем искать эти

значения в таблице Заказов, как это делается в варианте с подзапросом.

Внутренний запрос даёт нам snums=1001 и snum=1004. Внешний запрос затем даёт нам

строки из таблицы Заказов, где эти поля snum найдены.

Строго говоря, то, быстрее или нет работает вариант подзапроса, практически

зависит от реализации — в какой программе вы это используете. Часть вашей

программы, называемая оптимизатор, пытается найти наиболее эффективный способ

выполнения ваших запросов. Хороший оптимизатор в любом случае преобразует

вариант объединения в подзапрос, но нет достаточно простого способа, чтобы

выяснить, выполнено это или нет. Лучше сохранить ваши запросы в памяти, нежели

полагаться полностью на оптимизатор.

Конечно, вы можете также использовать оператор IN, даже когда вы уверены, что

подзапрос произведет одиночное значение. В любой ситуации, где вы можете

использовать реляционный оператор сравнения (=), вы можете использовать IN. В

отличие от реляционных операторов, IN не может заставить команду потерпеть

неудачу, если больше чем одно значение выбрано подзапросом. Это может быть или

преимуществом или недостатком. Вы не увидите непосредственно вывода из

подзапросов, если вы полагаете, что подзапрос собирается произвести только одно

значение, а он производит несколько. Вы не сможете объяснить различия в выводе

основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:

SELECT onum, amt, odate


WHERE cnum = 2001);

Вы можете устранить потребность в DISTINCT, используя IN вместо (=):

SELECT onum, amt, odate

WHERE cnum = 2001);

Что случится, если есть ошибка и один из заказов был аккредитован различным

продавцам? Версия, использующая IN, будет выдавать вам все заказы для обоих

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

сгенерированные отчеты или решения, сделанные на основе этого запроса, не будут

содержать ошибки. Вариант, использующий (=), просто потерпит неудачу. Это, по

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

выполнять поиск неисправности, выполнив этот подзапрос отдельно и наблюдая

значения, которые он производит. В принципе, если вы знаете, что подзапрос

должен (по логике) вывести только одно значение, вы должны использовать =.

IN является подходящим, если запрос может ограниченно производить одно или более

значений, независимо от того, ожидаете вы их или нет. Предположим, мы хотим

знать комиссионные всех продавцов, обслуживающих заказчиков в Лондоне:

WHERE city = «London»);

Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения

комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в

Лондоне. Но это только для данного случая. Нет никакой причины, чтобы некоторые

заказчики в Лондоне не могли быть назначены кому-то ещё. Следовательно, IN — это

наиболее логичная форма для использования в запросе.

| WHERE city = ‘London’); |

Рисунок 10.5 Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для поля city в предыдущем примере не обязателен,

несмотря на возможную неоднозначность между полями city таблицы Заказчика и

таблицы Продавцов. SQL всегда ищет первое поле в таблице, обозначенной в

предложении FROM текущего подзапроса. Если поле с данным именем там не найдено,

проверяются внешние запросы. В вышеупомянутом примере, «city» в предложении

WHERE означает, что имеется ссылка на Customer.city (поле city таблицы

Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего

запроса, SQL предполагает что это правильно. Это предположение может быть

отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим

позже, когда будем говорить о соотнесенных подзапросах. Если возможен

беспорядок, конечно же, лучше всего использовать префиксы.

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

Смысл всех подзапросов, обсуждённых в этой главе, в том, что все они выбирают

одиночный столбец. Это обязательно, поскольку полученный вывод сравнивается с

одиночным значением. Подтверждением этому является то, что SELECT * не может

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

используются с оператором EXISTS, о котором мы будем говорить в Главе 12.

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

Вы можете использовать выражение, основанное на столбце, а не просто сам

столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью

реляционных операторов, или с IN. Например, следующий запрос использует

реляционный оператор = (вывод показан на Рисунке 10.6):

(SELECT snum + 1000

WHERE sname = Serres);

Он находит всех заказчиков, чьё значение поля cnum, равное 1000, выше поля snum

Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений

(это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, или

ограничением UNIQUE, обсуждаемым в Главе 18); иначе

| (SELECT snum + 1000 |

| WHERE sname = ‘Serres’ |

| cnum cname city rating snum |

| 2002 Giovanni Rome 200 1003 |

Рисунок 10.6 Использование подзапроса с выражением

подзапрос может произвести несколько значений. Когда поля snum и сnum не имеют

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

всегда хорошо, запрос типа вышеупомянутого невероятно полезен.

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

Вы можете также использовать подзапросы внутри предложения HAVING. Эти

подзапросы могут использовать свои собственные агрегатные функции, если они не

производят нескольких значений, или использовать GROUP BY или HAVING. Следующий

запрос является примером этого (вывод показан на Рисунке 10.7):

SELECT rating, COUNT (DISTINCT cnum)

GROUP BY rating

(SELECT AVG (rating)

WHERE city = » San Jose’);

| SELECT rating,count (DISTINCT cnum) |

| GROUP BY rating |

| (SELECT AVG (rating)snum + 1000 |

| WHERE city = ‘San Jose’); |

Рисунок 10.7 Поиск в San Jose заказчиков с оценкой выше среднего

Эта команда подсчитывает заказчиков в San Jose с рейтингами выше среднего. Так

как имеются другие оценки, отличные от 300, они должны быть выведены с числом

номеров заказчиков, которые имели эту оценку.

Теперь вы используете запросы в иерархической манере. Вы видели, что

использование результата одного запроса для управления другим расширяет

возможности, позволяя выполнить большее количество функций.

Вы теперь понимаете, как использовать подзапросы с реляционными операциями и со

специальным оператором IN, или в предложении WHERE, или в предложении HAVING

В следующих главах мы будем рассматривать подзапросы. Сначала, в Главе 11, мы

обсудим другой вид подзапроса, который выполняется отдельно для каждой строки

таблицы, вызываемой во внешнем запросе. Затем, в Главах 12 и 13, мы представим

вам несколько специальных операторов, которые функционируют на всех подзапросах,

как это делает IN, за исключением случаев, когда эти операторы могут

использоваться только в подзапросах.

1. Напишите запрос, который использовал бы подзапрос для получения

всех заказов для заказчика с именем Cisneros.

Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum.

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

имеющих усреднённые заказы.

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

в заказах для каждого продавца, у которого эта общая сумма больше,

SQL/подзапрос

SQL подзапрос (внутренний/вложенный запрос) — полноценный SQL запрос в контексте другого SQL запроса; запрос внутри запроса; вставка одного запроса внутрь другого.

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

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

Вложенные запросы используются в основном в конструкции WHERE. В случае с использованием внутреннего запроса и сравнения его результата при помощи оператора = подзапрос не должен возвращать больше одного ряда.

Подзапросы являются альтернативным путем получения данных из множества таблиц.

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

1) Обычно, подзапрос возвращает только одну запись, но случается и так, что записей может быть много, тогда в условии WHERE используются такие операторы, как IN, NOT IN. Запрос может выглядеть следующий образом:

Тогда результат будет примерно таким:

2) Давайте рассмотрим используемую ранее таблицу student_details . Если Вы знаете имена студентов, изучающих естествознание (science), то можете получить их id, используя приведенный ниже запрос

но, если Вы не знаете их имен, то для получения id Вам необходимо написать запрос, описанным ниже способом:

В описанном выше запросе, вначале выполняется внутренний запрос, затем внешний

3) Вы можете использовать подзапрос с оператором INSERT для добавления данных из одной таблицы в другую. Давайте попробуем сгруппировать всех студентов, которые изучают математику в таблицу math_group

4) Подзапрос может использоваться с оператором SELECT, как описано ниже. Давайте используем таблицы product и order_items, объединив их между собой

Соотнесенный подзапрос

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

Выполнение вложенных запросов

По всем законам программирования, вложенный запрос выполняется первый и после него внешний и так по всей иерархии. Это означает, что первым выполненным запросом будет тот, который лежит «глубже» всех и последним будет внешний запрос.

Ресурсы

Естественно вложенные запросы увеличивают ресурсозатраты, время обработки и выполнения т.к. они, по сути, являются ещё одними запросами. Максимальное количество вложенных запросов в СУБД от Oracle — 255.

Примечания

  • Вы можете использовать столько вложенных запросов, сколько захотите, но в Oracle не рекомендуется использовать более 16 вложений.
  • Если подзапрос не зависит от внешнего запроса – он называется несоотнесенным подзапросом.

ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО

КАК РАБОТАЕТ ПОДЗАПРОС?

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

Чтобы оценить внешний( основной ) запрос, SQL сначала должен оценить внутренний запрос ( или подзапрос ) внутри предложения WHERE. Он делает это так как и должен делать запрос имеющий единственную цель — отыскать через таблицу Продавцов все строки, где поле sname равно значению Motika, и затем извлечь значения пол snum этих строк.

Единственной найденной строкой естественно будет snum = 1004. Однако SQL, не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предиката прочитал что

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

Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать

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

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

примере возвращал одно и только одно значение.

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

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

Это плоха стратеги, чтобы делать что-нибудь подобное следующему:

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

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

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

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

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

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

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

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

Другими словами, вы не должны записывать предыдущий пример так:

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

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

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

Любой запрос использующий одиночную функцию агрегата без предложения 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 таблицы Заказчика и таблицы Продавцов.

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

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

одиночный столбец. Это обязательно, поскольку выбранный вывод сравнивает- с одиночным значением. Подтверждением этому то, что SELECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзап- росы используются с оператором EXISTS.

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

столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов или с IN. Например, следующий запрос использует реляционный оператор = ( вывод показывается в Рисунке 10.6 ):

Он находит всех заказчиков чье значение пол cnum равное 1000, выше пол snum Serres. Мы предполагаем что столбец sname не имеет никаких двойных значений; иначе

Рисунок 10.6: Использование подзапроса с выражением

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

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

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

Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San Jose

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

Подзапросы SQL, урок 15 — вложенные запросы

Что такое подзапросы

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

Наряду с операторами сравнения такими, как =, , >=, Подзапросы SQL: видео урок

MySQL — Использование переменных в запросе

Довольно часто спрашивают, есть ли аналоги аналитических (оконных) функций в MySQL. Примечание. На момент написания статьи таких аналогов не было, однако статья и ныне представляет собой академический интерес в плане разбора оригинального для MySQL подхода к использованию переменных.

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

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

Одним из этих средств является уникальный, нехарактерный для прочих СУБД механизм работы с переменными внутри запроса SQL. Мы можем объявить переменную внутри запроса, менять ей значение и подставлять в SELECT для вывода. Причем порядок обработки строк в запросе и, как следствие, порядок присвоения значений переменным можно задать в пользовательской сортировке!

Предупреждение. В статье подразумевается, что обработка выражений в предложении SELECT осуществляется слева направо, однако официального подтверждения такого порядка обработки в документации MySQL нет. Это необходимо иметь в виду при смене версии сервера. Для гарантии последовательности вычисления можно использовать фиктивный оператор CASE или IF.

Аналог рекурсии

Рассмотрим простой пример, который генерирует последовательность Фибоначчи (в последовательности Фибоначчи каждый член равен сумме двух предыдущих, а первые 2 равны единице):

Данный запрос генерирует 18 чисел Фибоначчи, не считая первых двух:

Разберём теперь как оно работает.

В строчках 5) 6) генерируется 9 записей. Тут ничего необычного.

В строчке 7) мы объявляем две переменные @I, @J и присваиваем им 1.

В строке 3) происходит следующее: сначала переменной @I присваивается сумма двух переменных. Затем то же самое присваиваем переменной @J, причем с учетом того, что значение @I уже поменялось.

Другими словами, вычисления в SELECT выполняются слева направо – см. также замечание в начале статьи.

Причем изменение переменных осуществляется в каждой из наших 9 записей, т.е. при обработке каждой новой строки в переменных @I и @J будут содержаться значения, вычисленные при обработке предыдущей строки.

Чтобы решить эту же задачу средствами других СУБД, нам пришлось бы писать рекурсивный запрос!

Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка 7), если бы мы объявили переменную в предложении SELECT, она, скорее всего, вычислилась бы только 1 раз (хотя конкретное поведение будет зависеть от версии сервера). Тип переменной определяется значением, которым она инициализирована. Этот тип может динамически меняться. Если переменной присвоить NULL, её типом будет BLOB.

Порядок обработки строк в SELECT, как было сказано выше, зависит от пользовательской сортировки. Простой пример нумерации строк в заданном порядке:

Аналоги аналитических функций

Переменные также можно использовать для замены аналитических функций. Далее несколько примеров. Для простоты будем считать, что все поля NOT NULL, а сортировка и партиционирование (PARTITION BY) происходят по одному полю. Использование NULL значений и более сложных сортировок сделает примеры более громоздкими, но суть не поменяет.

Для примеров создадим таблицу TestTable:

где
group_id – идентификатор группы (аналог окна аналитической функции);
order_id – уникальное поле, по которому будет производиться сортировка;
value – некоторое числовое значение.

Заполним нашу таблицу тестовыми данными:

Примеры замены некоторых аналитических функций.

1) ROW_NUMBER() OVER(ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10

2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1

3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1

4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Для LEAD всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC

Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. MS SQL, например, для этих целей «спулит» окно (временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну.

Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id ASC.

5) COUNT(*) OVER(PARTITION BY group_id)

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

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:

6) MAX(value) OVER(PARTITION BY group_id)

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)

Интересная вещь, которая отсутствует в MS SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

Производительность

Для начала сравним по производительности нумерацию строк в запросе с помощью самосоединения и с помощью переменных.

1) Классический способ с самомоединением

Что на 10000 записей в таблице TestTable выдаёт:

Duration / Fetch
16.084 sec / 0.016 sec

2) С использованием переменных:

Duration / Fetch
0.016 sec / 0.015 sec

Результат говорит сам за себя. Однако надо понимать, что вычисленные с помощью переменных значения не оптимально использовать в условиях фильтрации. Сортировка и вычисление будут происходить для ВСЕХ строк, несмотря на то, что в итоге нам нужна только малая их часть.

Рассмотрим более подробно на примере такой задачи:

Вывести по 2 первые строки из таблицы TestTable для каждого значения group_id, отсортированных по order_id.

Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:

Однако оптимизатор MySQL ничего не знает о том, по каким правилам мы вычисляем поле RowNum. Ему придётся пронумеровать ВСЕ строки, и только потом отобрать нужные.

Теперь представьте, что у нас 1 миллион записей и 20 уникальных значений group_id. Т.е. чтобы выбрать 40 строк, MySQL будет вычислять значение RowNum для миллиона строк! Красивого решения этой задачи одним запросом в MySQL нет. Но можно сначала получить список уникальных значений group_id, например, так:

Затем средствами любого другого языка программирования сгенерировать запрос вида:

20 лёгких запросов отработают намного быстрее, чем вычисление RowNum для миллиона строк.

SQL — Подзапросы

Дата публикации: 2020-12-13

От автора: в SQL подзапросы — или внутренние запросы, или вложенные запросы — это запрос внутри другого запроса SQL, который вложен в условие WHERE.

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

Подзапросы могут использоваться с инструкциями SELECT, INSERT, UPDATE и DELETE вместе с операторами типа =, ,> =,

Лекция Язык sql 46 вставка одного запроса внутрь другого 68 как работает подзапрос? 68 Лекция Распределенная обработка данных Архитектура распределенной обработки данных Архитектура «файл сервер»

Название Лекция Язык sql 46 вставка одного запроса внутрь другого 68 как работает подзапрос? 68 Лекция Распределенная обработка данных Архитектура распределенной обработки данных Архитектура «файл сервер»
страница 8/9
Тип Лекция

rykovodstvo.ru > Руководство эксплуатация > Лекция

ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО

КАК РАБОТАЕТ ПОДЗАПРОС?

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

WHERE sname = ‘Motika’);

Чтобы оценить внешний( основной ) запрос, SQL сначала должен оценить внутренний запрос ( или подзапрос ) внутри предложения WHERE. Он делает это так как и должен делать запрос имеющий единственную цель — отыскать через таблицу Продавцов все строки, где поле sname равно значению Motika, и затем извлечь значения пол snum этих строк.

Единственной найденной строкой естественно будет snum = 1004. Однако SQL, не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предиката прочитал что

WHERE snum = 1004

| WHERE sname = ‘Motika’); |

| onum amt odate cnum snum |

| 3002 1900.10 10/03/1990 2007 1004 |

Рисунок 10.1: Использование подзапроса

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

Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать

WHERE snum = 1004

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

Встроенный язык SQL

Цель встроенного SQL состоит в соединении возможностей декларативного и процедурного языков.
Следует выделять вложенный и встроенный языки SQL. В первом случае основным является язык SQL, в который вводятся операторы циклов и условных переходов. Нет резкого разделения на интерфейсный и вложенный языки. Операторы выборки (SELECT), обновления (DELETE, UPDATE, INSERT) относят к интерактивному языку SQL. Операторы CREATE, ALTER, DROP считаются принадлежащими к вложенному языку SQL. В качестве объектов выступают таблицы, поля, ограничения, индексы, домены, виды, генераторы, триггеры, хранимые процедуры.
Во втором случае операторы языка SQL «встраиваются» в алгоритмические языки. Часто им является язык Pascal. Для этого команды SQL помещаются в исходный текст главной программы, которой предшествует фраза EXEC SQL (EXECute SQL).
Когда встраиваются команды SQL в текст программы, написанной на другом языке, надо выполнить прекомпиляцию прежде, чем окончательно ее скомпилировать. Программа, называемая прекомпилятором (или препроцессором), будет просматривать текст программы и преобразовывать команды SQL в форму, удобную для использования базовым языком. Затем используется обычный транслятор, чтобы преобразовывать программу из исходного текста в выполняемый код.
Основная программа вызывает процедуры SQL, которые выбирают параметры из главной программы и возвращают уже обработанные значения в основную программу. Модуль может содержать любое число процедур, каждая из которых состоит из одиночной команды SQL.

Вставка одного запроса внутрь другого

Название Вставка одного запроса внутрь другого
Дата конвертации 21.07.2013
Размер 144.8 Kb.
Тип Документы
1. /SQL doc Новый/Глава 1.doc
2. /SQL doc Новый/Глава 10.doc
3. /SQL doc Новый/Глава 11.doc
4. /SQL doc Новый/Глава 12.doc
5. /SQL doc Новый/Глава 13.doc
6. /SQL doc Новый/Глава 14.doc
7. /SQL doc Новый/Глава 15.doc
8. /SQL doc Новый/Глава 16.doc
9. /SQL doc Новый/Глава 17.doc
10. /SQL doc Новый/Глава 18.doc
11. /SQL doc Новый/Глава 19.doc
12. /SQL doc Новый/Глава 2.doc
13. /SQL doc Новый/Глава 20.doc
14. /SQL doc Новый/Глава 21.doc
15. /SQL doc Новый/Глава 22.doc
16. /SQL doc Новый/Глава 23.doc
17. /SQL doc Новый/Глава 24.doc
18. /SQL doc Новый/Глава 25.doc
19. /SQL doc Новый/Глава 3.doc
20. /SQL doc Новый/Глава 4.doc
21. /SQL doc Новый/Глава 5.doc
22. /SQL doc Новый/Глава 6.doc
23. /SQL doc Новый/Глава 7.doc
24. /SQL doc Новый/Глава 8.doc
25. /SQL doc Новый/Глава 9.doc
26. /SQL doc Новый/Оглавление книги Основы SQL.doc
27. /SQL doc Новый/Оглавление книги Основы SQL1.txt
28. /SQL doc Новый/Приложение A.doc
29. /SQL doc Новый/Приложение B.doc
30. /SQL doc Новый/Приложение C.oc.DOC
31. /SQL doc Новый/Приложение D.doc
32. /SQL doc Новый/Приложение E.doc
Что такое «реляционная база данных»?
Вставка одного запроса внутрь другого
Соотнесённые подзапросы

Глава 10. Вставка одного запроса внутрь другого

В конце Главы 9 мы говорили, что запросы могут управлять другими запросами. В

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

внутрь предиката другого запроса и использования вывода внутреннего запроса в

верном или неверном условии предиката.

Вы сможете выяснить, какие виды операторов могут использовать подзапросы, и

посмотреть, как подзапросы работают со средствами SQL, такими как DISTINCT, с

составными функциями и выводимыми выражениями.

Вы узнаете, как использовать подзапросы с предложением HAVING, и получите

некоторые наставления, как правильно использовать подзапросы.

Как работает подзапрос?

С помощью SQL вы можете вкладывать запросы друга в друга. Обычно внутренний

запрос генерирует значение, которое проверяется в предикате внешнего запроса,

определяющего, верно оно или нет. Например, предположим, что мы знаем имя

продавца: Motika, но не знаем значение его поля snum и хотим извлечь все заказы

из таблицы Заказов. Вот способ сделать это (вывод показан на Рис. 10.1 ):

WHERE sname = ‘Motika’);

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний

запрос (или подзапрос) внутри предложения WHERE. Он делает это так, как и должен

делать запрос, имеющий единственную цель — отыскать через таблицу Продавцов все

строки, где поле sname равно значению Motika, а затем извлечь значения поля snum

Единственной найденной строкой, естественно, будет snum = 1004. Однако SQL не

просто выдает это значение, а помещает его в предикат основного запроса вместо

самого подзапроса, так чтобы предикат прочитал, что

WHERE snum = 1004

| WHERE sname = ‘Motika’); |

| onum amt odate cnum snum |

| 3002 1900.10 10/03/1990 2007 1004 |

Рисунок 10.1 Использование подзапроса

Основной запрос затем выполняется как обычно с вышеупомянутыми результатами.

Разумеется, подзапрос должен выбрать один, и только один, столбец, а тип данных

этого столбца должен совпадать с тем значением, с которым он будет сравниваться

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

имена (в данном случае snum), но это не обязательно. Конечно, если бы мы уже

знали номер продавца Motika, мы могли бы просто напечатать WHERE snum = 1004 и

работать далее с подзапросом в целом, но это было бы не так универсально. Этот

же запрос будет продолжать работать, даже если номер Motika изменился, а с

помощью простого изменения имени в подзапросе вы можете использовать его для

Значения, которые подзапрос может выводить

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

возвращал одно, и только одно, значение.

Имея выбранное поле snum » WHERE city = «London» вместо «WHERE sname = ‘Motika»,

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

основного запроса невозможным оценку верности или неверности, и команда выдаст

При использовании подзапросов в предикатах, основанных на реляционных операциях

(уравнениях или неравенствах, как объяснено в Главе 4), вы должны убедиться, что

использовали подзапрос, который будет выдавать одну, и только одну, строку

вывода. Если вы используете подзапрос, который не выводит никаких значений

вообще, команда не потерпит неудачи, но основной запрос не выведет никаких

значений. Подзапросы, которые не производят никакого вывода (или нулевой вывод),

вынуждают рассматривать предикат ни как верный, ни как неверный, а как

неизвестный. Однако неизвестный предикат имеет тот же самый эффект, что и

неверный: никакие строки не выбираются основным запросом (смотри в Главе 5

подробную информацию о неизвестном предикате).

Вот пример плохой стратегии:

WHERE city = Barcelona);

Поскольку мы имеем только одного продавца в Barcelona — Rifkin, то подзапрос

будет выбирать одиночное значение snum, и, следовательно, будет принят. Но это

только в данном случае. Большинство БД SQL имеют многочисленных пользователей,

и, если другой пользователь добавит нового продавца из Barcelona в таблицу,

подзапрос выберет два значения, и ваша команда потерпит неудачу.

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


В некоторых случаях вы можете использовать DISTINCT чтобы вынудить подзапрос

генерировать одиночное значение. Предположим что мы хотим найти все порядки

кредитования для тех продавцов, которые обслуживают Hoffman’а (cnum = 2001).

Вот способ сделать это (вывод показан на Рисунке 10.2):

(SELECT DISTINCT snum

WHERE cnum = 2001);

| (SELECT DISTINCT snum |

| 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 для получения одного значения из подзапроса

Подзапрос установил, что значение поля snum совпало с Hoffman — 1001, а затем

основной запрос выделил все заказы с этим значением snum из таблицы Заказов (не

разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен

продавцу, мы знаем, что каждая строка в таблице Заказов с данным значением cnum

должна иметь такое же значение snum. Однако, поскольку там может быть любое

число таких строк, подзапрос мог бы вывести много (хотя и идентичных) значений

snum для данного поля cnum. Аргумент DISTINCT предотвращает это. Если наш

подзапрос возвратит более одного значения, это будет указывать на ошибку в наших

данных — хорошая вещь для знающих об этом.

Должен быть и альтернативный подход, чтобы ссылаться к таблице Заказчиков, а не

к таблице Заказов в подзапросе. Так как поле cnum это первичный ключ таблицы

Заказчиков, запрос, выбирающий его, должен выдать только одно значение. Это

рационально, только если вы как пользователь имеете доступ к таблице Заказов, но

не к таблице Заказчиков. В этом случае вы можете использовать решение, которое

мы показали выше. (SQL имеет механизмы, которые определяют, кто имеет привилегии

на выполнение действий в определённой таблице. Это будет объясняться в Главе

Пожалуйста, учтите, что методика, используемая в предшествующем примере,

применима, только когда вы знаете, что два различных поля в таблице должны

всегда совпадать, как в нашем случае. Эта ситуация не является типичной в

реляционных базах данных (РБД), она является исключением из правил.

Предикаты с подзапросами являются необратимыми

Вы должны обратить внимание что предикаты, включающие подзапросы, используют

Другими словами, вы не должны записывать предыдущий пример так:

WHERE (SELECT DISTINCT snum

WHERE cnum = 2001)

В строгой ANSI-реализации это приведет к неудаче, хотя некоторые программы и

позволяют делать такие вещи. ANSI также предохраняет от появления в выводе

подзапроса обоих значений при сравнении.

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

Тип функций, который автоматически может производить одиночное значение для

любого числа строк, конечно же — агрегатная функция.

Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY,

будет выбирать одиночное значение для использования в основном предикате.

Например, вы хотите увидеть все заказы, имеющие сумму выше средней на 4-е

октября (вывод показан на Рисунке 10.3):

WHERE odate = 10/04/1990);

| 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 октября — 1788.98 (1713.23 + 75.75) делится

пополам, что в целом равняется 894.49. Все строки со значением в поле amt выше

этого являются выбранными. Имейте в виду, что сгруппированные агрегатные

функции, которые являются агрегатными функциями, определёнными в терминах

предложения GROUP BY, могут производить многочисленные значения. Они,

следовательно, недопустимы в подзапросах такого характера. Даже если GROUP BY и

HAVING используются таким способом, что только одна группа выводится с помощью

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

агрегатную функцию с предложением WHERE, что устранит нежелательные группы.

Например, следующий запрос который должен найти среднее значение комиссионных

продавца в Лондоне,

SELECT AVG (comm)

HAVlNG city = «London»;

не может использоваться в подзапросе! Во всяком случае, это не лучший способ

Другим способом может быть

SELECT AVG (comm)

WHERE city = «London»;

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

Вы можете использовать подзапросы, которые производят любое число строк, если вы

применяете специальный оператор IN (операторы BETWEEN, LIKE и IS NULL не могут

использоваться с подзапросами). Как вы помните, IN определяет набор значений,

одно из которых должно совпадать с другим термином уравнения предиката в заказе,

чтобы предикат был верным.

Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода

подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой

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

атрибуты таблицы Заказов для продавца в Лондоне (вывод показан на Рисунке 10.4):

WHERE city = «LONDON»);

| 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

должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и

проверить их снова по составному предикату. Проще и эффективнее извлекать из

таблицы Продавцов значения поля snum, где city = «London», а затем искать эти

значения в таблице Заказов, как это делается в варианте с подзапросом.

Внутренний запрос даёт нам snums=1001 и snum=1004. Внешний запрос затем даёт нам

строки из таблицы Заказов, где эти поля snum найдены.

Строго говоря, то, быстрее или нет работает вариант подзапроса, практически

зависит от реализации — в какой программе вы это используете. Часть вашей

программы, называемая оптимизатор, пытается найти наиболее эффективный способ

выполнения ваших запросов. Хороший оптимизатор в любом случае преобразует

вариант объединения в подзапрос, но нет достаточно простого способа, чтобы

выяснить, выполнено это или нет. Лучше сохранить ваши запросы в памяти, нежели

полагаться полностью на оптимизатор.

Конечно, вы можете также использовать оператор IN, даже когда вы уверены, что

подзапрос произведет одиночное значение. В любой ситуации, где вы можете

использовать реляционный оператор сравнения (=), вы можете использовать IN. В

отличие от реляционных операторов, IN не может заставить команду потерпеть

неудачу, если больше чем одно значение выбрано подзапросом. Это может быть или

преимуществом или недостатком. Вы не увидите непосредственно вывода из

подзапросов, если вы полагаете, что подзапрос собирается произвести только одно

значение, а он производит несколько. Вы не сможете объяснить различия в выводе

основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:

SELECT onum, amt, odate

WHERE cnum = 2001);

Вы можете устранить потребность в DISTINCT, используя IN вместо (=):

SELECT onum, amt, odate

WHERE cnum = 2001);

Что случится, если есть ошибка и один из заказов был аккредитован различным

продавцам? Версия, использующая IN, будет выдавать вам все заказы для обоих

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

сгенерированные отчеты или решения, сделанные на основе этого запроса, не будут

содержать ошибки. Вариант, использующий (=), просто потерпит неудачу. Это, по

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

выполнять поиск неисправности, выполнив этот подзапрос отдельно и наблюдая

значения, которые он производит. В принципе, если вы знаете, что подзапрос

должен (по логике) вывести только одно значение, вы должны использовать =.

IN является подходящим, если запрос может ограниченно производить одно или более

значений, независимо от того, ожидаете вы их или нет. Предположим, мы хотим

знать комиссионные всех продавцов, обслуживающих заказчиков в Лондоне:

WHERE city = «London»);

Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения

комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в

Лондоне. Но это только для данного случая. Нет никакой причины, чтобы некоторые

заказчики в Лондоне не могли быть назначены кому-то ещё. Следовательно, IN — это

наиболее логичная форма для использования в запросе.

| WHERE city = ‘London’); |

Рисунок 10.5 Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для поля city в предыдущем примере не обязателен,

несмотря на возможную неоднозначность между полями city таблицы Заказчика и

таблицы Продавцов. SQL всегда ищет первое поле в таблице, обозначенной в

предложении FROM текущего подзапроса. Если поле с данным именем там не найдено,

проверяются внешние запросы. В вышеупомянутом примере, «city» в предложении

WHERE означает, что имеется ссылка на Customer.city (поле city таблицы

Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего

запроса, SQL предполагает что это правильно. Это предположение может быть

отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим

позже, когда будем говорить о соотнесенных подзапросах. Если возможен

беспорядок, конечно же, лучше всего использовать префиксы.

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

Смысл всех подзапросов, обсуждённых в этой главе, в том, что все они выбирают

одиночный столбец. Это обязательно, поскольку полученный вывод сравнивается с


одиночным значением. Подтверждением этому является то, что SELECT * не может

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

используются с оператором EXISTS, о котором мы будем говорить в Главе 12.

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

Вы можете использовать выражение, основанное на столбце, а не просто сам

столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью

реляционных операторов, или с IN. Например, следующий запрос использует

реляционный оператор = (вывод показан на Рисунке 10.6):

(SELECT snum + 1000

WHERE sname = Serres);

Он находит всех заказчиков, чьё значение поля cnum, равное 1000, выше поля snum

Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений

(это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, или

ограничением UNIQUE, обсуждаемым в Главе 18); иначе

| (SELECT snum + 1000 |

| WHERE sname = ‘Serres’ |

| cnum cname city rating snum |

| 2002 Giovanni Rome 200 1003 |

Рисунок 10.6 Использование подзапроса с выражением

подзапрос может произвести несколько значений. Когда поля snum и сnum не имеют

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

всегда хорошо, запрос типа вышеупомянутого невероятно полезен.

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

Вы можете также использовать подзапросы внутри предложения HAVING. Эти

подзапросы могут использовать свои собственные агрегатные функции, если они не

производят нескольких значений, или использовать GROUP BY или HAVING. Следующий

запрос является примером этого (вывод показан на Рисунке 10.7):

SELECT rating, COUNT (DISTINCT cnum)

GROUP BY rating

(SELECT AVG (rating)

WHERE city = » San Jose’);

| SELECT rating,count (DISTINCT cnum) |

| GROUP BY rating |

| (SELECT AVG (rating)snum + 1000 |

| WHERE city = ‘San Jose’); |

Рисунок 10.7 Поиск в San Jose заказчиков с оценкой выше среднего

Эта команда подсчитывает заказчиков в San Jose с рейтингами выше среднего. Так

как имеются другие оценки, отличные от 300, они должны быть выведены с числом

номеров заказчиков, которые имели эту оценку.

Теперь вы используете запросы в иерархической манере. Вы видели, что

использование результата одного запроса для управления другим расширяет

возможности, позволяя выполнить большее количество функций.

Вы теперь понимаете, как использовать подзапросы с реляционными операциями и со

специальным оператором IN, или в предложении WHERE, или в предложении HAVING

В следующих главах мы будем рассматривать подзапросы. Сначала, в Главе 11, мы

обсудим другой вид подзапроса, который выполняется отдельно для каждой строки

таблицы, вызываемой во внешнем запросе. Затем, в Главах 12 и 13, мы представим

вам несколько специальных операторов, которые функционируют на всех подзапросах,

как это делает IN, за исключением случаев, когда эти операторы могут

использоваться только в подзапросах.

1. Напишите запрос, который использовал бы подзапрос для получения

всех заказов для заказчика с именем Cisneros.

Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum.

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

имеющих усреднённые заказы.

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

в заказах для каждого продавца, у которого эта общая сумма больше,

Глава 10. ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО

В конце Главы 9 мы говорили, что запросы могут управлять другими запросами. В этой главе вы узнаете, как это делается (большей частью) путём помещения запроса внутрь предиката другого запроса и использования вывода внутреннего запроса в верном или неверном условии предиката.

Вы сможете выяснить, какие виды операторов могут использовать подзапросы, и посмотреть, как подзапросы работают со средствами SQL, такими как DISTINCT, с составными функциями и выводимыми выражениями.

Вы узнаете, как использовать подзапросы с предложением HAVING, и получите некоторые наставления, как правильно использовать подзапросы.

КАК РАБОТАЕТ ПОДЗАПРОС?

С помощью SQL вы можете вкладывать запросы друга в друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса, определяющего, верно оно или нет. Например, предположим, что мы знаем имя продавца: Motika, но не знаем значение его поля snum и хотим извлечь все заказы из таблицы Заказов. Вот способ сделать это (вывод показан на Рис. 10.1 ):

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Он делает это так, как и должен делать запрос, имеющий единственную цель — отыскать через таблицу Продавцов все строки, где поле sname равно значению Motika, а затем извлечь значения поля snum этих строк.

Единственной найденной строкой, естественно, будет snum = 1004. Однако SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат прочитал, что

Основной запрос затем выполняется как обычно с вышеупомянутыми результатами. Разумеется, подзапрос должен выбрать один, и только один, столбец, а тип данных этого столбца должен совпадать с тем значением, с которым он будет сравниваться в предикате.
Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена (в данном случае 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):

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

Пожалуйста, учтите, что методика, используемая в предшествующем примере, применима, только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных (РБД), она является исключением из правил.

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

Вы должны обратить внимание что предикаты, включающие подзапросы, используют выражение

Другими словами, вы не должны записывать предыдущий пример так:

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

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

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

Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будет выбирать одиночное значение для использования в основном предикате. Например, вы хотите увидеть все заказы, имеющие сумму выше средней на 4-е октября (вывод показан на Рисунке 10.3):

Средняя сумма приобретений на 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):

В ситуации, подобной этой, подзапрос проще для понимания пользователем и проще для выполнения компьютером, чем если бы вы использовали объединение:

Хотя это и произведёт тот же самый вывод, что в примере с подзапросом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эффективнее извлекать из таблицы Продавцов значения поля snum, где city = «London», а затем искать эти значения в таблице Заказов, как это делается в варианте с подзапросом. Внутренний запрос даёт нам snums=1001 и snum=1004. Внешний запрос затем даёт нам строки из таблицы Заказов, где эти поля snum найдены.
Строго говоря, то, быстрее или нет работает вариант подзапроса, практически зависит от реализации — в какой программе вы это используете. Часть вашей программы, называемая оптимизатор, пытается найти наиболее эффективный способ выполнения ваших запросов. Хороший оптимизатор в любом случае преобразует вариант объединения в подзапрос, но нет достаточно простого способа, чтобы выяснить, выполнено это или нет. Лучше сохранить ваши запросы в памяти, нежели полагаться полностью на оптимизатор.
Конечно, вы можете также использовать оператор IN, даже когда вы уверены, что подзапрос произведет одиночное значение. В любой ситуации, где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу, если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов, если вы полагаете, что подзапрос собирается произвести только одно значение, а он производит несколько. Вы не сможете объяснить различия в выводе основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:

Вы можете устранить потребность в DISTINCT, используя IN вместо (=):

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

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

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

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

Смысл всех подзапросов, обсуждённых в этой главе, в том, что все они выбирают одиночный столбец. Это обязательно, поскольку полученный вывод сравнивается с одиночным значением. Подтверждением этому является то, что SELECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS, о котором мы будем говорить в Главе 12.

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

Вы можете использовать выражение, основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов, или с IN. Например, следующий запрос использует реляционный оператор = (вывод показан на Рисунке 10.6):

Он находит всех заказчиков, чьё значение поля cnum, равное 1000, выше поля snum Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18); иначе

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

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

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

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

РЕЗЮМЕ

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

Вы теперь понимаете, как использовать подзапросы с реляционными операциями и со специальным оператором IN, или в предложении WHERE, или в предложении HAVING внешнего запроса.

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

Запрос SQL из нескольких таблиц так же, как из одной

ДО ЭТОГО КАЖДЫЙ ЗАПРОС, КОТОРЫЙ МЫ ИССЛЕДОВАЛИ, основывался на одиночной таблице. В этой главе, вы узнаете, как сделать запрос данных из любого числа таблиц с помощью одной команды. Это — чрезвычайно мощное средство, потому что оно не только объединяет вывод из нескольких таблиц, но и определяет связи между ними. Вы обучитесь различным формам, которые могут использовать эти связи, а также устанавливать и использовать их, чтобы удовлетворять возможным специальным требованиям.

Объединение таблиц

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

Этот вид операции называется — объединением , которое является одним из видов операций в реляционных базах данных. Как установлено в Главе 1, главное в реляционном подходе это связи, которые можно создавать между позициями данных в таблицах. Используя объединения, мы непосредственно связываем информацию с любым номером таблицы, и таким образом способны создавать связи между сравнимыми фрагментами данных.

При объединении, таблицы, представленные списком в предложении FROM запроса, отделяются запятыми. Предикат запроса может ссылаться к любому столбцу любой связанной таблицы и, следовательно, может использоваться для связи между ними. Обычно предикат сравнивает значения в столбцах различных таблиц, чтобы определить, удовлетворяет ли WHERE установленному условию.

Имена таблиц и столбцов

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

Salespeople.snum
Salespeople.city
Orders.odate

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

Если мы должны связать эти столбцы (кратковременно), мы будем должны указать их с именами Salespeople.city или Customers.city, чтобы SQL мог их различать.

Создание объединения

Предположим, что вы хотите поставить в соответствии вашему продавцу ваших заказчиков в городе, в котором они живут, поэтому вы увидите все комбинации продавцов и заказчиков для этого города. Вы будете должны брать каждого продавца и искать в таблице Заказчиков всех заказчиков того же самого города. Вы могли бы сделать это, введя следующую команду (вывод показывается в Рисунке 8.1):

SELECT Customers.cname, Salespeople.sname, Salespeople.city
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city;

=============== SQL Execution Log ============
| SELECT Customers.cname, Salespeople.sname, |
| Salespeople.city |
| FROM Salespeople, Customers |
| WHERE Salespeople.city = Customers.city |
| ============================================= |
| cname cname city |
| ——- ——— —- |
| Hoffman Peel London |
| Clemens Peel London |
| Hoffman Motika London |
| Clemens Motika London |
| Liu Serres San Jose |
| Cisneros Serres San Jose |
============================================= ==

Рисунок 8.1. Объединение двух таблиц.

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

Что SQL в основном делает в объединении, так это исследует каждую комбинацию строк двух или более возможных таблиц, и проверяет эти комбинации по их предикатам. В предыдущем примере, требовалась строка продавца Peel из таблицы Продавцов и объединение ее с каждой строкой таблицы Пользователей, по одной в каждый момент времени.

Если комбинация производит значение которое делает предикат верным, и если поле city из строк таблиц Заказчика равно London, то Peel — это то запрашиваемое значение которое комбинация выберет для вывода. То же самое будет затем выполнено для каждого продавца в таблице Продавцов (у некоторых из которых не было никаких заказчиков в этих городах).

Объединение таблиц через справочную целостность

Эта особенность часто используется просто для эксплуатации связей встроенных в базу данных. В предыдущем примере, мы установили связь между двумя таблицами в объединении. Это прекрасно. Но эти таблицы уже были соединены через поле snum. Эта связь называется состоянием справочной целостности , как мы уже говорили в Главе 1. Используя объединение, можно извлекать данные в терминах этой связи. Например, чтобы показать имена всех заказчиков соответствующих продавцам, которые их обслуживают, мы будем использовать такой запрос:

SELECT Customers.cname, Salespeople.sname
FROM Customers, Salespeople
WHERE Salespeople.snum = Customers.snum;

Вывод этого запроса показывается в Рисунке 8.2.

=============== SQL Execution Log ============
| SELECT Customers.cname, Salespeople.sname, |
| FROM Salespeople, Customers |
| WHERE Salespeople.snum = Customers.snum |
| ============================================= |
| cname sname |
| ——- ——— |
| Hoffman Peel |
| Giovanni Axelrod |
| Liu Serres |
| Grass Serres |
| Clemens Peel |
| Cisneros Rifkin |
| Pereira Motika |
===============================================

Рисунок 8.2. Объединение продавцов с их заказчиками.

Это — пример объединения, в котором столбцы используются для определения предиката запроса, и в этом случае, столбцы snum из обеих таблиц удалены из вывода. И это прекрасно.

Вывод показывает, какие заказчики какими продавцами обслуживаются; значения поля snum, которые устанавливают связь — отсутствуют. Однако если вы укажете их в вывод, то вы должны или удостовериться, что вывод понятен сам по себе или обеспечить комментарий к данным при выводе.

Объединения таблиц по равенству значений в столбцах и другие виды объединений

Объединения, которые используют предикаты основанные на равенствах называются — объединениями по равенству . Все наши примеры в этой главе до настоящего времени, относились именно к этой категории, потому что все условия в предложениях WHERE базировались на математических выражениях использующих знак равно (=). Строки ‘city=’London’ и ‘Salespeople.snum=Orders.snum’ — примеры таких типов равенств, найденных в предикатах.

Объединения по равенству — это вероятно наиболее общий вид объединения, но имеются и другие. Вы можете, фактически, использовать любой из реляционных операторов в объединении. Здесь показан пример другого вида объединения (вывод показывается в Рисунке 8.3):

SELECT sname, cname
FROM Salespeople, Customers
WHERE sname

=============== SQL Execution Log ============
| SELECT sname, cname |
| FROM Salespeople, Customers |
| WHERE sname |
| Axelrod Pereira |
===============================================

Рисунок 8.3. Объединение, основанное на неравенстве.

Эта команда не часто бывает полезна. Она воспроизводит все комбинации имени продавца и имени заказчика так, что первый предшествует последнему в алфавитном порядке, а последний имеет оценку меньше чем 200. Обычно, вы не создаете сложных связей подобно этой, и, по этой причине, вы, вероятно, будете строить наиболее общие объединения по равенству, но вы должны хорошо знать и другие возможности.

Объединение более двух таблиц

Вы можете также создавать запросы, объединяющие более двух таблиц. Предположим что мы хотим найти все Заказы заказчиков не находящихся в тех городах где находятся их продавцы. Для этого необходимо связать все три наши типовые таблицы (вывод показывается в Рисунке 8.4):

SELECT onum, cname, Orders.cnum, Orders.snum
FROM Salespeople, Customers,Orders
WHERE Customers.city <> Salespeople.city
AND Orders.cnum = Customers.cnum
AND Orders.snum = Salespeople.snum;

=============== SQL Execution Log ==============
| SELECT onum, cname, Orders.cnum, Orders.snum |
| FROM Salespeople, Customers, Orders |
| WHERE Customers.city <> Salespeople.city |
| AND Orders.cnum = Customers.cnum |
| AND Orders.snum = Salespeople.snum; |
| =============================================== |
| onum cname cnum snum |
| —— ——- —— —— |
| 3001 Cisneros 2008 1007 |
| 3002 Pereira 2007 1004 |
| 3006 Cisneros 2008 1007 |
| 3009 Giovanni 2002 1003 |
| 3007 Grass 2004 1002 |
| 3010 Grass 2004 1002 |
=================================================

Рисунок 8.4. Объединение трех таблиц.

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

Резюме

Теперь вы больше не ограничиваетесь просмотром одной таблицы в каждый момент времени. Кроме того, вы можете делать сложные сравнения между любыми полями любого числа таблиц и использовать полученные результаты, чтобы решать какую информацию вы бы хотели видеть. Фактически, эта методика настолько полезна для построения связей, что она часто используется для создания их внутри одиночной таблицы. Это будет правильным: вы сможете объединить таблицу с собой, а это очень удобная вещь. Это будет темой Главы 9.

Работа с SQL


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

(См. Приложение A для ответов.)

Объединение таблицы с собой


В ГЛАВЕ 8 МЫ ПОКАЗАЛИ ВАМ, КАК ОБЪЕДИНЯТЬ ДВЕ или более таблиц вместе.

Достаточно интересно то, что та же самая методика может использоваться для объединения вместе двух копии одиночной таблицы.

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

Как делать объединение таблицы с собой?

Для объединения таблицы с собой, вы можете сделать каждую строку таблицы, одновременно, и комбинацией ее с собой и комбинацией с каждой другой строкой таблицы. Вы затем оцениваете каждую комбинацию в терминах предиката, также как в объединениях разных таблиц. Это позволит вам легко создавать определенные виды связей между различными позициями внутри одиночной таблицы — с помощью обнаружения пар строк со значением поля, например.

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

Другими словами, это объединение — такое же, как и любое другое объединение между двумя таблицами, за исключением того, что в данном случае обе таблицы идентичны.

Псевдонимы

Синтаксис команды для объединения таблицы с собой тот же, что и для объединения разных таблиц в одном запросе.

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

Вы можете сделать это с помощью определения временных имен, называемых переменными диапазона , переменными корреляции или просто псевдонимами .

Вы определяете их в предложении FROM запроса. Это очень просто: вы набираете имя таблицы, оставляете пробел, и затем набираете псевдоним для нее.

Имеется пример, который находит все пары заказчиков имеющих один и тот же самый рейтинг (вывод показывается в Рисунке 9.1):

SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating;

=============== SQL Execution Log ==============
| Giovanni Giovanni 200 |
| Giovanni Liu 200 |
| Liu Giovanni 200 |
| Liu Liu 200 |
| Grass Grass 300 |
| Grass Cisneros 300 |
| Clemens Hoffman 100 |
| Clemens Clemens 100 |
| Clemens Pereira 100 |
| Cisneros Grass 300 |
| Cisneros Cisneros 300 |
| Pereira Hoffman 100 |
| Pereira Clemens 100 |
| Pereira Pereira 100 |
=============================== ==================

Рисунок 9.1. Объединение таблицы с собой.

Примечание . В СУБД Interbase ‘SECOND ‘ является ключевым словом, пример должен быть модифицирован, например, так:

SELECT a.cname, b.cname, a.rating
FROM Customers a, Customers b
WHERE a.rating = b.rating;

Обратите внимание, что на Рисунке 9.1., как и в некоторых дальнейших примерах, полный запрос не может уместиться в окне вывода, и, следовательно, будет усекаться.

В вышеупомянутой команде, SQL ведет себя так, как если бы он соединял две таблицы называемые ‘первая’ и ‘вторая’ . Обе они, фактически, таблицы Заказчиков, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы первый и второй были установлены в предложении FROM запроса, сразу после имени копии таблицы.

Обратите внимание, что псевдонимы могут использоваться в предложении SELECT, даже если они не определены в предложении FROM.

Это — очень хорошо. SQL будет сначала допускать любые такие псевдонимы на веру, но будет отклонять команду, если они не определены далее в предложении FROM запроса.

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

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

Устранение избыточности

Обратите внимание, что наш вывод имеет два значения для каждой комбинации, причем второй раз в обратном порядке. Это потому, что каждое значение показано первый раз в каждом псевдониме, и второй раз (симметрично) в предикате.

Следовательно, значение A в псевдониме сначала выбирается в комбинации со значением B во втором псевдониме, а затем значение A во втором псевдониме выбирается в комбинации со значением B в первом псевдониме. В нашем примере, Hoffman выбрался вместе с Clemens, а затем Clemens выбрался вместе с Hoffman. Тот же самый случай с Cisneros и Grass, Liu и Giovanni, и так далее. Кроме того, каждая строка была сравнена сама с собой, чтобы вывести строки, такие как Liu и Liu.

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

SELECT first.cname, second.cname, first.rating
FROM Customers first, Customers second
WHERE first.rating = second.rating
AND first.cname

Вывод этого запроса показывается в Рисунке 9.2.

=============== SQL Execution Log ==============
| SELECT first.cname, second.cname, first.rating |
| FROM Customers first, Customers second |
| WHERE first.rating = second.rating |
| AND first.cname |
=================================================

Рисунок 9.2. Устранение избыточности вывода в объединении с собой.

Для СУБД Interbase ‘second ‘ нужно заменить чем-либо другим.

Hoffman предшествует Periera в алфавитном порядке, поэтому комбинация удовлетворяет обеим условиям предиката и появляется в выводе. Когда та же самая комбинация появляется в обратном Заказе — когда Periera в псевдониме первой таблицы сравнтвается с Hoffman во второй таблице псевдонима — второе условие не встречается. Аналогично, Hoffman не выбирается при наличии того же рейтинга что и он сам, потому что его имя не предшествует ему самому в алфавитном порядке. Если бы вы захотели включить сравнение строк с ними же в запросах подобно этому, вы могли бы просто использовать вместо .

Проверка ошибок

Таким образом, мы можем использовать эту особенность SQL для проверки определенных видов ошибок. При просмотре таблицы Заказов, вы можете видеть, что поля cnum и snum должны иметь постоянную связь.

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

SELECT first.onum, first.cnum, first.snum, second.onum, second.cnum, second.snum
FROM Orders first, Orders second
WHERE first.cnum = second.cnum
AND first.snum <> second.snum;

Для Interbase см . Примечание выше.

Хотя это выглядит сложно, логика этой команды достаточно проста. Она будет брать первую строку таблицы Заказов, запоминать ее под первым псевдонимом, и проверять ее в комбинации с каждой строкой таблицы Заказов под вторым псевдонимом, одну за другой. Если комбинация строк удовлетворяет предикату, она выбирается для вывода. В этом случае предикат будет рассматривать эту строку, найдет строку, где поле cnum=2008 а поле snum=1007, и затем рассмотрит каждую следующую строку с тем же самым значением поля cnum. Если он находит, что какая-то из них имеет значение, отличное от значения поля snum, предикат будет верен, и выведет выбранные поля из текущей комбинации строк. Если же значение snum с данным значением cnum в нашей таблице совпадает, эта команда не произведет никакого вывода.

Больше псевдонимов

Хотя объединение таблицы с собой — это первая ситуация, когда понятно, что псевдонимы необходимы, вы не ограничены в их использовании, чтобы только отличать копию одной таблицы от ее оригинала. Вы можете использовать псевдонимы в любое время, когда вы хотите создать альтернативные имена для ваших таблиц в команде (см. Примечание по Interbase выше). Например, если ваши таблицы имеют очень длинные и сложные имена, вы могли бы определить простые односимвольные псевдонимы, типа a и b, и использовать их вместо имен таблицы в предложении SELECT и предикате. Они будут также использоваться с соотнесенными подзапросами (обсуждаемыми в Главе 11).

Еще больше комплексных объединений

Вы можете использовать любое число псевдонимов для одной таблицы в запросе, хотя использование более двух в данном предложении SELECT * будет излишеством.

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

(Вывод показывается в Рисунке 9.3):

SELECT a.cnum, b.cnum, c.cnum
FROM Customers a, Customers b, Customers c
WHERE a.rating = 100 AND b.rating = 200 AND c.rating = 300;

=============== SQL Execution Log ==============
| cnum cnum cnum |
| —— —— —— |
| 2001 2002 2004 |
| 2001 2002 2008 |
| 2001 2003 2004 |
| 2001 2003 2008 |
| 2006 2002 2004 |
| 2006 2002 2008 |
| 2006 2003 2004 |
| 2006 2003 2008 |
| 2007 2002 2004 |
| 2007 2002 2008 |
| 2007 2003 2004 |
| 2007 2003 2008 |
=================================================

Рисунок 9.3. Комбинация продавцов с различными значениями рейтинга.

Как вы можете видеть, этот запрос находит все комбинации заказчиков с тремя значениями оценки, поэтому первый столбец состоит из заказчиков с оценкой 100, второй с 200, и последний с оценкой 300. Они повторяются во всех возможных комбинациях. Это — сортировка группировки которая не может быть выполнена с GROUP BY или ORDER BY, поскольку они сравнивают значения только в одном столбце вывода.

Вы должны также понимать, что не всегда обязательно использовать каждый псевдоним или таблицу которые упомянуты в предложении FROM запроса, в предложении SELECT. Иногда, предложение или таблица становятся запрашиваемыми исключительно потому, что они могут вызываться в предикате запроса. Например, следующий запрос находит всех заказчиков размещенных в городах, где продавец Serres (snum 1002) имеет Заказыков (вывод показывается в Рисунке 9.4):

SELECT b.cnum, b.cname
FROM Customers a, Customers b
WHERE a.snum = 1002
AND b.city = a.city;

Рисунок 9.4. Нахождение заказчиков в городах относящихся к Serres.

Псевдоним a будет делать предикат неверным за исключением случая, когда его значение столбца snum = 1002. Таким образом, псевдоним опускает все, кроме заказчиков продавца Serres. Псевдоним b будет верным для всех строк с тем же самым значением города, что и текущее значение города для a ; в ходе запроса, строка псевдонима b будет верна один раз, когда значение города представлено в a .

Нахождение этих строк псевдонима b — единственная цель псевдонима a , поэтому мы не выбираем все столбцы подряд. Как вы можете видеть, собственные заказчики Serres выбираются при нахождении их в том же самом городе, что и он сам, поэтому выбор их из псевдонима a необязателен. Короче говоря, псевдоним находит строки заказчиков Serres, Liu и Grass. Псевдоним b находит всех заказчиков размещенных в любом из их городов (San Jose и Berlin соответственно) включая, конечно, самих — Liu и Grass.

Вы можете также создать объединение, которое включает и различные таблицы и псевдонимы одиночной таблицы. Следующий запрос объединяет таблицу Пользователей с собой: чтобы найти все пары заказчиков обслуживаемых одним продавцом. В то же самое время, этот запрос объединяет заказчика с таблицей Продавцов с именем этого продавца (вывод показан на Рисунке 9.5):

SELECT sname, Salespeople.snum, first.cname, second.cname
FROM Customers first, Customers second, Salespeople
WHERE first.snum = second.snum
AND Salespeople.snum = first.snum
AND first.cnum

=============== SQL Execution Log ==================
| SELECT cname, Salespeople.snum, first.cname |
| second.cname |
| FROM Customers first, Customers second, Salespeople |
| WHERE first.snum = second.snum |
| AND Salespeople.snum = first.snum |
| AND first.cnum —— —— ——— ——— |
| Serres 1002 Liu Grass |
| Peel 1001 Hoffman Clemens |
=====================================================

Рисунок 9.5. Объединение таблицы с собой и с другой таблицей .

Примечание . Для Interbase замените ‘ second ‘ другим псевдонимом.

Резюме

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

Следующим шагом после комбинации многочисленных таблиц или многочисленных копий одной таблицы в запросе, будет комбинация многочисленных запросов, где один запрос будет производить вывод, который будет затем управлять работой другого запроса. Это другое мощное средство SQL, о котором мы расскажем в Главе 10 и более тщательно в последующих главах.

Работа с SQL


    1. Напишите запрос, который бы вывел все пары продавцов, живущих в одном и том же городе. Исключите комбинации продавцов с ними же, а также дубликаты строк, выводимых в обратном порядке.
    2. Напишите запрос, который вывел бы все пары Заказов по данным заказчикам, именам этих заказчиков, и исключал дубликаты из вывода, как в предыдущем вопросе.
    3. Напишите запрос, который вывел бы имена (cname) и города (city) всех заказчиков с такой же оценкой (rating) как у Hoffman. Напишите запрос, использующий поле cnum Hoffman, а не его оценку, так, чтобы оно могло быть использовано, если его оценка вдруг изменится.

(См. Приложение A для ответов.)

Вставка одного запроса внутрь другого

В КОНЕЦ ГЛАВЫ 9, МЫ ГОВОРИЛИ, ЧТО ЗАПРОСЫ могут управлять другими запросами. В этой главе вы узнаете, как это делается (большей частью), помещая запрос внутрь предиката другого запроса, и используя вывод внутреннего запроса в верном или неверном условии предиката. Вы сможете выяснить, какие виды операторов могут использовать подзапросы и посмотреть как подзапросы работают со средствами SQL, такими как DISTINCT, с составными функциями и выводимыми выражения. Вы узнаете, как использовать подзапросы с предложением HAVING и получите некоторые наставления, как правильно использовать подзапросы.

Как работает подзапрос?

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

Предположим что мы знаем имя sname продавца Motika, но не знаем его номер snum, и хотим извлечь все его Заказы из таблицы Заказов. Имеется один способ, чтобы сделать это (вывод показывается в Рисунке 10.1):

SELECT *
FROM Orders
WHERE snum = (SELECT snum
FROM Salespeople
WHERE sname = ‘Motika’);

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Он делает это так, как и должен делать запрос имеющий единственную цель — отыскать через таблицу Продавцов все строки, где поле sname равно значению Motika, и затем извлечь значения поля snum этих строк.

Единственной найденной строкой, естественно, будет snum = 1004. Однако SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предиката прочитал, что snum = 1004

Рисунок 10.1. Использование подзапроса.

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

Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать

WHERE snum = 1004

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

Значения, которые могут выдавать подзапросы

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

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

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

Это плохая стратегия, чтобы делать что-нибудь подобное следующему :

SELECT *
FROM Orders
WHERE snum = (SELECT snum
FROM Salespeople
WHERE city = ‘Barcelona’);

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

DISTINCT с подзапросами

Вы можете в некоторых случаях использовать DISTINCT, чтобы вынудить подзапрос генерировать одиночное значение. Предположим, что мы хотим найти все Заказы для тех продавцов, которые обслуживают Hoffman (cnum = 2001). Имеется один способ, чтобы сделать это (вывод показывается в Рисунке 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 для получения одного значения из подзапроса .

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

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

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

Предикаты с подзапросами являются необратимыми

Вы должны обратить внимание, что предикаты, включающие подзапросы, используют выражение

Другими словами, вы не должны записывать предыдущий пример так:

SELECT *
FROM Orders
WHERE (SELECT DISTINCT snum
FROM Orders
WHERE cnum = 2001) = snum;

В строгой ANSI реализации, это приведет к неудаче, хотя некоторые программы и позволяют делать такие вещи (в частности, в Interbase такой запрос работает). ANSI также предохраняет вас от появления обоих значений при сравнении, которые нужно вывести с помощью подзапроса.

Использование агрегатных функций в подзапросах

Один тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, — агрегатная функция. Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будет выбирать одиночное значение для использования в основном предикате. Например, вы хотите увидеть все Заказы, имеющие сумму приобретений выше средней на 4-е Октября (вывод показан на Рисунке 10.3):

SELECT *
FROM Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders
WHERE odate = 10/04/1990);

Для Interbase нужно изменить так:

SELECT *
FROM Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders
WHERE odate = CAST(’10/04/1990′ AS DATE));

=============== 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 5160,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 Октября — 1788.98 (1713.23 + 75.75) делится пополам, что в целом равняется = 894.49. Все строки со значением в поле amt выше этого — являются выбранными.

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

SELECT AVG (comm)
FROM Salespeople
GROUP BY city
HAVING city = ‘London;

не может использоваться в подзапросе! Во всяком случае, это не лучший способ формировать запрос. Другим способом может быть

SELECT AVG (comm)
FROM Salespeople
WHERE city = ‘London’;

Использование подзапросов, которые выдают много строк с помощью оператора IN

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

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 должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату.

Проще и эффективнее извлекать из таблицы Продавцов значения поля snum где city = ‘London’, и затем искать эти значения в таблице Заказов, как это делается в варианте с подзапросом. Внутренний запрос дает нам snum=1001 и snum=1004. Внешний запрос затем дает нам строки из таблицы Заказов, где эти поля snum найдены.

Строго говоря, быстрее или нет работает вариант подзапроса, практически зависит от реализации — в какой программе вы это используете. Эта часть вашей программы, называемая оптимизатор , пытается найти наиболее эффективный способ выполнения ваших запросов.

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

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

Вы не увидите непосредственно вывода из подзапросов; если вы полагаете, что подзапрос собирается произвести только одно значение, а он производит различные. Вы не сможете объяснить различия в выводе основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:

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, будет давать вам все Заказы для обоих продавцов. Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения, сделанные на основе этого запроса, не будут содержать ошибки. Вариант, использующий (=), просто потерпит неудачу.

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

В принципе, если вы знаете, что подзапрос должен (по логике) вывести только одно значение, вы должны использовать = . IN является подходящим, если запрос может ограниченно производить одно или более значений, независимо от того ожидаете вы их или нет.

Предположим, мы хотим знать комиссионные всех продавцов обслуживающих заказчиков в Лондоне:

SELECT comm
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE city = ‘London’);

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

Рисунок 10.5: Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для поля city необязателен в предыдущем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов.

SQL всегда ищет первое поле в таблице, обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, «city» в предложении WHERE означает, что имеется ссылка к Customer.city (поле city таблицы Заказчиков).

Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает, что это — правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже, когда будем говорить об соотнесенных подзапросах. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.

Подзапросы выбирают одиночные столбцы

Смысл всех подзапросов обсужденных в этой главе тот, что все они выбирают одиночный столбец. Это обязательно, поскольку выбранный вывод сравнивается с одиночным значением. Подтверждением этому то, что SELECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS, который мы будем представлять в Главе 12.

Использование выражений в подзапросах

Вы можете использовать выражение, основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов, или с IN. Например, следующий запрос использует реляционный оператор = (вывод показывается в Рисунке 10.6):

SELECT *
FROM Customers
WHERE cnum = (SELECT snum + 1000
FROM Salespeople
WHERE sname = ‘Serres’);

=============== 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. Использование подзапроса с выражением.

Он находит всех заказчиков, чье значение поля cnum равное 1000, выше поля snum Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18), иначе подзапрос может произвести многочисленные значения. Когда поля snum и сnum не имеют такого простого функционального значения как например первичный ключ, что не всегда хорошо, запрос типа вышеупомянутого невероятно полезен.

Подзапросы в предложении HAVING

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

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) |
| FROM Customers |
| WHERE city = ‘San Jose’ |
|===============================================|
| rating |
| ——— ——— |
| 300 2 |
===============================================

Рисунок 10.7. Нахождение заказчиков с оценкой выше среднего в San Jose.

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

Резюме

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

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

Работа с SQL


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

(См. Приложение A для ответов.)

НОВОСТИ ФОРУМА
Рыцари теории эфира
01.10.2020 — 05:20: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ — Upbringing, Inlightening, Education ->
[center][Youtube]69vJGqDENq4[/Youtube][/center]
[center]14:36[/center]
Osievskii Global News
29 сент. Отправлено 05:20, 01.10.2020 г.’ target=_top>Просвещение от Вячеслава Осиевского — Карим_Хайдаров.
30.09.2020 — 12:51: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ — Upbringing, Inlightening, Education ->
[center][Ok]376309070[/Ok][/center]
[center]11:03[/center] Отправлено 12:51, 30.09.2020 г.’ target=_top>Просвещение от Дэйвида Дюка — Карим_Хайдаров.
30.09.2020 — 11:53: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ — Upbringing, Inlightening, Education ->
[center][Youtube]VVQv1EzDTtY[/Youtube][/center]
[center]10:43[/center]

интервью Раввина Борода https://cursorinfo.co.il/all-news/rav.
мой телеграмм https://t.me/peshekhonovandrei
мой твиттер https://twitter.com/Andrey54708595
мой инстаграм https://www.instagram.com/andreipeshekhonow/

[b]Мой комментарий:
Андрей спрашивает: Краснодарская синагога — это что, военный объект?
— Да, военный, потому что имеет разрешение от Росатома на манипуляции с радиоактивными веществами, а также иными веществами, опасными в отношении массового поражения. Именно это было выявлено группой краснодарцев во главе с Мариной Мелиховой.

[center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

Доминико Риккарди: Россию ждёт страшное будущее (хотелки ЦРУ):
https://tainy.net/22686-predskazaniya-dominika-rikardi-o-budushhem-rossii-sdelannye-v-2000-godu.html

Завещание Алена Даллеса / Разработка ЦРУ (запрещено к ознакомлению Роскомнадзором = Жид-над-рус-надзором)
http://av-inf.blogspot.com/2013/12/dalles.html

[center][b]Сон разума народа России [/center]

[center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

Доминико Риккарди: Россию ждёт страшное будущее (хотелки ЦРУ):
https://tainy.net/22686-predskazaniya-dominika-rikardi-o-budushhem-rossii-sdelannye-v-2000-godu.html

Завещание Алена Даллеса / Разработка ЦРУ (запрещено к ознакомлению Роскомнадзором = Жид-над-рус-надзором)
http://av-inf.blogspot.com/2013/12/dalles.html

[center][b]Сон разума народа России [/center]

Илон Маск рекомендует:  Abstract - Директива Delphi
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL