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

Использование Exist (существование)

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

Exist (подзапрос)

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

Select P.pokup_name from pokupateli P

Where exist (select R.pokup_name from Rashod R

Where P.pokup_name= R.pokup_name)//если существует запись об этом объекте в др таблице

 

Использование Singular (единственный)

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

Singular (подзапрос)

#Составить список покупателей купивших только 1 товар

Информация о покупках хранится в таблице Rashod

Select P.pokup_name from pokupateli P

Where Singular (select R.pokup_name from Rashod R

Where P.pokup_name= R.pokup_name)

Использование подзапросов возвращающих множество значений

15.1 Использование All или some=any

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

<сравниваемое значение> [NOT] <оператор> {ALL|SOME|ANY} (<подзапрос>)

Отношение сравниваемого значения и значений, возвращаемых подзапросом устанавливается словами:

· ALL – указывает, что условие поиска истинно только тогда, когда сравниваемое значение находится в нужном отношении со всеми значениями, возвращаемыми подзапросом.

· SOME(ANY) – условие поиска истинно, когда сравниваемое значение находится в нужном отношении хотя бы с одним значением, возвращаемым подзапросом.

 

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

Select R1.* from Rashod R1

Where R1.kolvo > ALL

(select R2.kolvo, R2.pokup_name from Rashod R2,

Group by R2.pokup_name)

 

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

Select R1.* from Rashod R1

Where R1.kolvo > ANY

(select R2.kolvo, R2.pokup_name from Rashod R2,

Group by R2.pokup_name)

 

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

Если в условии поиска для вложенного запроса нужно указать агрегатную функцию используется предложение HAVING

#Определить покупателя, у которого средняя покупка (количество) больше средней покупки других покупателей и среднее количество покупок этого покупателя.

Select R1.pokup_name, avg (R1.kolvo) from Rashod R1

Group by R1.pokup_name

Having R2.pokup_name, avg(R1.kolvo)>=ALL //имеющего ср. Количество>=всех..

(Select avg R2.kolvo from Rashod R2

Group by R2.pokup_name)

 

#Определить адрес покупателя, который приобрёл наибольшее количество товара

Select * from Pokupateli P

Where P.pokup_name = (select R.pokupname from Rashod R

Having sum (R.kolvo) >=ALL (select sum(RR.kolvo), RR.pokup_name from Rashod RR Group by RR.pokup_name))

 

Объединение результатов выполнения нескольких запросов (UNION)

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

 

#Select * from Tovar Where kolvo>100	#select * from Tovar Where name_tovar starting with ‘A’	#Select * from Tovar Where price between (100 and 500)
Select * from Tovar Where kolvo>100 UNION select * from Tovar Where name_tovar starting with ‘A’ UNION Select * from Tovar Where price between (100 and 500)

 

 

Внешние соединения

Внешнее соединение определяется в предложении from согласно спецификации:

Select {*|<значение1>[,<значение2>]} from <table1> <вид соединения> join <table2> ON <условие поиска>

 

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

· Left - левое внешнее соединение, когда ведущей является table1

· Right - правое внешнее соединение, когда ведущей является table2

· Full – полное внешнее соединение, когда ведущей являются обе таблицы. В результирующий набор данных включаются все записи обеих таблиц, по следующему алгоритму: если для записи table1 имеются записи table2 которые удовлетворяют условию соединения, то в результирующий набор данных будут включены все комбинации соединения таких записей таблицы table1 и table2, в противном случае, в результирующий набор данных будет включена запись table1 соединенная с пустой записью, тоже относится и к записям table2.

 

#Пусть имеют таблицы А и В со связью по столбцу P1

A

P1	P2	P3
a	x
b	x
C	Y
d

B

P1	P2
X
y
z

 

Select A.P1,A.P2,B.P2 from A leftjoin B on A.P2=B.P1	Select A.P1,A.P2,B.P2 from A right join B on A.P2=B.P1	Select A.P1,A.P2,B.P1,B.P2 from A full join B on A.P2=B.P1
A.P1 A.P2 B.P2 a x   b x   C Y   d - -	A.P1 A.P2 B.P2 a x   b x   C Y   - -	A.P1 A.P2 B.P1 B.P2 a x x   b x x   C Y y   d - - - - z -