Виды вложенных запросов (подзапросы) в SQL особенности применения

Лекция

Привет, Вы узнаете о том , что такое вложенные запросы, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое вложенные запросы, подзапросы в sql , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

Вложенные подзапросы

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

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

Существуют простые и коррелированные вложенные подзапросы. Они включаются в WHERE (HAVING) фразу с помощью условий IN, EXISTS или одного из условий сравнения ( = | <> | < | <= | > | >= ). Простые вложенные подзапросы обрабатываютя системой "снизу вверх". Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т.д.

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

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

Использование подзапросов в базах данных SQLite: оператор EXISTS и SELECT | IT-блог о веб-технологиях, серверах, протоколах, базах данных, СУБД, SQL, компьютерных сетях, языках программирования и создание сайтов.

Виды вложенных запросов (подзапросы) в SQL особенности применения

1) Вложенные подзапросы

SQL позволяет вкладывать запросы друг в друга. Об этом говорит сайт https://intellect.icu . Обычно подзапрос возвращает одно значение, которое проверяется на предмет истинности предиката.

Виды условий поиска:
• Сравнение с результатом вложенного запроса (=, <>, <, <=, >, >=)
• Проверка на принадлежность результатам подзапроса (IN)
• Проверка на существование (EXISTS)
• Многократное (количественное) сравнение (ANY, ALL)

Примечания по вложенным запросам:
• Подзапрос должен выбирать только один столбец (за исключением подзапроса с предикатом EXISTS), и тип данных его результата должен соответствовать типу данных значения, указанному в предикате.
• В ряде случаев можно использовать ключевое слово DISTINCT для гарантии получения единственного значения.
• Во вложенном запросе нельзя включать раздел ORDER BY и UNION.
• Подзапрос может находиться и лева и справа от условия поиска.
• В подзапросах могут использоваться функции агрегирования без раздела GROUP BY, которые автоматически выдают специальное значение для любого количества строк, специальный предикат IN, а также выражения, основанные на столбцах.
• По возможности следует вместо подзапросов использовать соединение таблиц JOIN.

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

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Orders WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
SELECT * FROM Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)

2) Связанные подзапросы

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

Примеры на связанные подзапросы:

SELECT * FROM SalesPeople Main WHERE 1<(SELECT COUNT(*) FROM Customer WHERE SNum=Main.SNum) //возвращает всех продавцов, обслуживших более одного покупателя SELECT * FROM Orders O1 WHERE Amt>(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //возвращает все заказы, величина которых превосходит среднюю величины заказа для данного покупателя

3) Предикат EXISTS Запросы, использующие EXISTS

Синтаксическая форма: [NOT] EXISTS (<подзапрос>)

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

Квантор EXISTS (существует) - понятие, заимствованное из формальной логики. В языке SQL предикат с квантором существования представляется выражением EXISTS (SELECT * FROM ...).

Такое выражение считается истинным только тогда, когда результат вычисления "SELECT * FROM ..." является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.)

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

Результат:

  SELECT	Название
FROM	Поставщики
WHERE	EXISTS
	(	SELECT	*
		FROM	Поставки
		WHERE	ПС = Поставщики.ПС
		AND	ПР = 11 );

Название
СЫТНЫЙ
УРОЖАЙ
КОРЮШКА
ЛЕТО

Система последовательно выбирает строки таблицы Поставщики, выделяет из них значения столбцов Название и ПС, а затем проверяет, является ли истинным условие существования, т.е. су-ществует ли в таблице Поставки хотя бы одна строка со значением ПР=11 и значением ПС, равным значению ПС, выбранному из таблицы Поставщики. Если условие выполняется, то полученное значение столбца Название включается в результат.

Предположим, что первые значения полей Название и ПС равны, соответственно, 'СЫТНЫЙ' и 1. Так как в таблице Поставки есть строка с ПР=11 и ПС=1, то значение 'СЫТНЫЙ' должно быть включено в результат.

Хотя этот первый пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.

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

Результат:

  SELECT	Название, Статус
  FROM	Поставщики
  WHERE	NOT EXISTS
	(	SELECT	*
		FROM 	Поставки
		WHERE	ПС = Поставщики.ПС
		AND	ПР = 11 );

Название	Статус
ПОРТОС	кооператив
ШУШАРЫ	совхоз
ТУЛЬСКИЙ	универсам
ОГУРЕЧИК	ферма

Примечания по предикату EXISTS:
• EXISTS – предикат, возвращающий значение TRUE или FALSE, и его можно применять отдельно или вместе с другими булевыми выражениями.
• EXISTS не может использовать функции агрегирования в своем подзапросе.
• В коррелирующих (связанных, зависимых – Correlated) подзапросах предикат EXISTS выполняется для каждой строки внешней таблицы.
• Можно комбинировать предикат EXISTS с соединениями таблиц.

Примеры на предикат EXISTS:

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – возвращает всех покупателей, если кто-то из них проживает в San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNum<>First.CNum) – возвращает номера продавцов, обслуживших только одного покупателя.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNum<>T.CNum AND F.SNum=S.SNum) – возвращает номера, имена и города проживания всех продавцов, обслуживших нескольких покупателей.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1<(SELECT COUNT(*) FROM Orders WHERE Orders.CNum=Send.CNum)) – возвращает всех продавцов, обслуживших покупателей, сделавших больше одного заказа.

4) Предикаты количественного сравнения

Синтаксическая форма: <конструктор значений строки> [NOT] {=|>|<|>=|<=|<>} ANY|ALL (<подзапрос>)

Эти предикаты используют в качестве аргумента подзапрос, однако, по сравнению с предикатом EXISTS, они применяются в конъюнкции с предикатами отношения (=,<>,<,<=,>,>=). В этом смысле они сходны с предикатом IN, но применяются только с подзапросами. Стандарт допускает использовать вместо ANY ключевое слово SOME, однако не все СУБД его поддерживают.

Примечания по предикатам сравнения:
• Предикат ALL принимает значение TRUE, если каждое значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
• Предикат ANY принимает значение TRUE, если хотя бы одно значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
• Если подзапрос не возвращает строк, то ALL автоматически принимает значение TRUE (считается, что условие сравнения выполняется), а для ANY – FALSE.
• Если сравнение не имеет значения TRUE ни для одной строки и есть одна или несколько строк с NULL значением, то ANY возвращает UNKNOWN.
• Если сравнение не имеет значения FALSE ни для одной строки и есть одна или несколько строк с NULL значением, то ALL возвращает UNKNOWN.

Примеры на предикат количественного сравнения:

SELECT * FROM SalesPeople WHERE City=ANY(SELECT City FROM Customer)
SELECT * FROM Orders WHERE AmtALL(SELECT Rating FROM Customer WHERE City=’Rome’)

5) Предикат уникальности

UNIQUE|DISTINCT (<подзапрос>)

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

6) Предикат совпадений

<конструктор значений строки> MATCH [UNIQUE] [PARTIAL|FULL] (<подзапрос>)

Предикат MATCH проверяет, будет ли значение строки запроса совпадать со значением любой строки, полученной в результате подзапроса. От предикатов IN И ANY такой подзапрос отличается тем, что позволяет обрабатывать «частичные» (PARTIAL) совпадения, которые могут встречаться среди строк, имеющих часть NULL-значений.

7) Запросы в разделе FROM

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

Пример:

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//подзапрос возвращает суммарную величину заказов, сделанных каждым покупателем из Лондона.

8) Рекурсивные запросы

WITH RECURSIVE
Q1 AS SELECT … FROM … WHERE …
Q2 AS SELECT … FROM … WHERE …

9.Простые вложенные подзапросы

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

Результат:

SELECT	Название, Статус
FROM	Поставщики
WHERE	ПС IN
	(	SELECT	ПС
		FROM	Поставки
		WHERE	ПР = 11 );

Название	Статус
СЫТНЫЙ	рынок
УРОЖАЙ	коопторг
ЛЕТО	агрофирма
КОРЮШКА	кооператив

Как уже отмечалось в п.3.3.1, при обработке полного запроса система выполняет прежде всего вложенный подзапрос. Этот подзапрос выдает множество номеров поставщиков, которые поставляют продукт с кодом ПР = 11, а именно множество (1, 5, 6, 8). Поэтому первоначальный запрос эквивалентен такому простому запросу:

SELECT	Название, Статус
FROM	Поставщики
WHERE	ПС IN (1, 5, 6, 8);

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

SELECT	Название, Статус
FROM	Поставщики
WHERE	ПС IN
	(	SELECT	ПС
		FROM	Поставки
		WHERE	ПР IN
			(	SELECT	ПР
				FROM	Продукты
				WHERE	Продукт = 'Помидоры' ));

В данном случае результатом самого внутреннего подзапроса является только одно значение (11). Как уже было показано выше, подзапрос следующего уровня в свою очередь дает в результате множество (1, 5, 6, 8). Последний, самый внешний SELECT, вычисляет приведенный выше окончательный результат. Вообще допускается любая глубина вложенности подзапросов.

Тот же результат можно получить с помощью соединения

SELECT	Название, Статус
FROM	Поставщики, Поставки, Продукты
WHERE	Поставщики.ПС = Поставки.ПС
AND	Поставки.ПР = Продукты.ПР
AND	Продукт = 'Помидоры';

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

Использование одной и той же таблицы во внешнем и вложенном подзапросе

Выдать номера поставщиков, которые поставляют хотя бы один продукт, поставляемый поставщиком 6.

Результат:

SELECT	DISTINCT ПС
FROM	Поставки
WHERE	ПР IN
	(	SELECT	ПР
		FROM	Поставки
		WHERE	ПС = 6);

ПС
1
3
5
6
8

Отметим, что ссылка на Поставки во вложенном подзапросе означает не то же самое, что ссылка на Поставки во внешнем запросе. В действительности, два имени Поставки обозначают различные значения. Чтобы этот факт стал явным, полезно использовать псевдонимы, например, X и Y:

SELECT	DISTINCT X.ПС
FROM	Поставки X
WHERE	X.ПР IN
	(	SELECT	Y.ПР
		FROM	Поставки Y
		WHERE	Y.ПС = 6 );

Здесь X и Y – произвольные псевдонимы таблицы Поставки, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.

10 Вложенный подзапрос с оператором сравнения, отличным от IN

Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6.

Результат:

SELECT	ПС
FROM	Поставщики
WHERE	Город =	
	(	SELECT	Город
		FROM	Поставщики
		WHERE 	ПС = 6 );

ПС
1
4
6

В подобных запросах можно использовать и другие операторы сравнения (<>, <=, <, >= или >), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка.

11 Коррелированные вложенные подзапросы

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

SELECT	Название, Статус
FROM	Поставщики
WHERE	11 IN
	(	SELECT	ПР
		FROM	Поставки
		WHERE	ПС = Поставщики.ПС );

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

Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.ПС будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос
```
(	SELECT	ПР
	FROM	Поставки
	WHERE	ПС = 1 );
```
получая в результате множество (9, 11, 12, 15). Теперь система может завершить обработку для поставщика с номером 1. Выборка значений Название и Статус для ПС=1 (СЫТНЫЙ и рынок) будет проведена тогда и только тогда, когда ПР=11 будет принадлежать этому множеству, что, очевидно, справедливо.
Далее система будет повторять обработку такого рода для следующего поставщика и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Поставщики.

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

Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и коррелированном вложенном подзапросе.

Выдать номера всех продуктов, поставляемых только одним по-ставщиком.

Результат:

SELECT	DISTINCT X.ПР
FROM	Поставки X
WHERE	X.ПР NOT IN
	(	SELECT	Y.ПР	
		FROM	Поставки Y	
		WHERE	Y.ПС <> X.ПС );

X.ПР
17

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

Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним - либо X, либо Y.

12. Функции в подзапросе

Теперь, после знакомства с различными формулировками вложенных подзапросов и псевдонимами легче понять текст и алгоритм реализации запроса (п. 3.1) на получение тех поставщиков продуктов для Сырников, которые поставляют эти продукты за минимальную цену:

SELECT	Продукт, Цена, Название, Статус
FROM	Продукты, Состав, Блюда, Поставки, Поставщики
WHERE	Продукты.ПР = Состав.ПР
AND	Состав.БЛ = Блюда.БЛ
AND	Поставки.ПР = Состав.ПР
AND	Поставки.ПС = Поставщики.ПС
AND	Блюдо = 'Сырники'
AND	Цена =	(	SELECT MIN(Цена)
			FROM	Поставки X
			WHERE	X.ПР = Поставки.ПР );

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

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

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

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

Однострочные подзапросы

Виды вложенных запросов (подзапросы) в SQL особенности применения

Выполнение однострочных подзапросов

Использование групповых функций в подзапросах

Использование подзапросов в условии HAVING

Правила вложенных запросов mssql (ограничения)

На вложенный запрос распространяются следующие ограничения:

Список выбора вложенного запроса, начинающийся с оператора сравнения, может включать только одно выражение или имя столбца (за исключением операторов EXISTS и IN в инструкции SELECT * или в списке соответственно).
Если предложение WHERE внешнего запроса включает имя столбца, оно должно быть совместимо для соединения со столбцом в списке выбора вложенного запроса.
Типы данных ntext, text и image не могут быть использованы в списке выбора вложенных запросов.
Вложенные запросы, представленные оператором неизмененного сравнения (после которого нет ключевого слова ANY или ALL), не могут включать предложения GROUP BY и HAVING.
Ключевое слово DISTINCT не может быть использовано во вложенном запросе, включающем предложение GROUP BY.
Предложения COMPUTE и INTO не могут быть указаны.
Предложение ORDER BY может быть указано только вместе с предложением TOP.
Представление, созданное с помощью вложенного запроса, не может быть обновлено.
Список выбора вложенного запроса, начинающегося с предложения EXISTS, по соглашению содержит звездочку (*) вместо отдельного имени столбца. Правила для вложенного запроса, начинающегося с предложения EXISTS, являются такими же, как для стандартного списка выбора, поскольку вложенный запрос, начинающийся с предложения EXISTS, проводит проверку на существование и возвращает TRUE или FALSE вместо данных.

Виды вложенных запросов (подзапросы) в SQL особенности применения

Вложенные подзапросы

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

1) Вложенные подзапросы

2) Связанные подзапросы

3) Предикат EXISTS Запросы, использующие EXISTS

4) Предикаты количественного сравнения

5) Предикат уникальности

6) Предикат совпадений

7) Запросы в разделе FROM

8) Рекурсивные запросы

Использование одной и той же таблицы во внешнем и вложенном подзапросе

10 Вложенный подзапрос с оператором сравнения, отличным от IN

11 Коррелированные вложенные подзапросы

12. Функции в подзапросе

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

Правила вложенных запросов mssql (ограничения)

Рекомендации по составлению подзапросов (рекомендации)

Комментарии

Оставить комментарий

Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL

Термины: Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL