39. Язык sql. Оператор select: подзапросы, многотабличные запросы, операции соединения.

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

Отметим относительно приводимых ниже примеров, что производители СУБД, как правило, реализуют не весь стандарт ISO, а основные его части, плюс добавляют какие-то свои функции. Соответственно, некоторые из представленных ниже запросов (с NATURAL JOIN, SOME/ANY, ALL) будут части СУБД работать, в части – нет.

Существует 3 типа подзапросов:

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

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

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

Пример 1. Составьте список персонала, работающею в отделении компании, расположенном по адресу 463 Main St.

SELECT staffNo, fName, IName, position

FROM Staff

WHERE branchNo =

(SELECT branchNo

FROM Branch

WHERE street = '163 Main St' )

Имеется 2 таблицы – Staff (персонал) и Branch (отделения компании). В таблице Staff есть поле branchNo, являющееся внешним ключом и соответствующим отделению компании, в котором работает сотрудник. Так как в таблице Staff хранится не информация об адресе отделения, а только его идентификатор, внутренний скалярный подзапрос возвращает из таблицы Branch идентификатор подразделения, находящегося по искомому адрес (предполагается, что по адресу располагается только одно подразделение). Внешний запрос выдает информацию о сотрудниках подразделения.

Пример 2. Составьте список всех сотрудников, имеющих зарплату выше средней, указав, насколько их зарплата превышает среднюю зарплату по предприятию.

SELECT staffNo, fName, IName, position,

salary - (SELECT AVG(salary) FROM Staff) AS salDiff

FROM Staff

WHERE salary > (SELECT AVG(salary) FROM Staff ) ;

К подзапросам применяются следующие правила и ограничения:

• В подзапросах не должна использоваться конструкция ORDER BY, хотя она может присутствовать во внешнем операторе SELECT.

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

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

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

SELECT staffNo, fName, IName, position, salary

FROM Staff

WHERE (SELECT AVG(salary) FROM Staff) < salary

- некорректный вариант

Пример 3. Вложенные подзапросы и использование предиката IN. Составьте перечень сдаваемых в аренду объектов, за которые отвечают работники отделения компании, расположенного по адресу '163 Main st'.

SELECT propertyNo, street, city, postcode, type, rooms, rent

FROM PropertyForRent

WHERE staffNo IN

(SELECT staffNo

FROM Staff

WHERE brancliNo =

(SELECT branchNo

FROM Branch

WHERE street = '163 Main St ' ) )

В данном примере используется еще одна таблица PropertyForRent, ссылающаяся на таблицу Staff поле staffNo.

Ключевые слова ANY(SOME) и ALL

Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел. Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным только в том случае, если оно выполняется для всех значений в результирующем столбце подзапроса. Если тексту подзапроса предшествует ключевое слово ANY, то условие сравнения будет считаться выполненным, если оно удовлетворяется хотя бы для какого-либо (одного или нескольких) значения в результирующем столбце подзапроса. Если в результате выполнения подзапроса будет получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY — невыполненным. Согласно стандарту ISO дополнительно можно использовать ключевое слово SOME, являющееся синонимом ключевого слова ANY.

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

SELECT staffNo, fName, IName, position, salary

FROM Staff

WHERE salary > SOME

(SELECT salary

FROM Staff

WHERE branchNo = 'B003')

Пример 5. Найдите всех работников, чья заработная плата больше заработной платы любого работника отделения компании под номером 'B003' (ключевое слово ALL).

SELECT staffNo, fName, IName, position, salary

FROM Staff

WHERE salary > ALL

(SELECT salary

FROM Staff

WHERE branchNo = 'B003')

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

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

Пример 6. Простое соединение. Составьте список имен всех клиентов, которые уже осмотрели хотя бы один сдаваемый в аренду объект и сообщили свое мнение по этому поводу.

SELECT c.clientNo, fName, IName, propertyNo, comment

FROM Client c, Viewing v

WHERE c.clientNo = v.clientNo

Стандарт SQL дополнительно предоставляет следующие способы определения

данного соединения.

SELECT c.clientNo, fName, IName, propertyNo, comment

FROM Client с JOIN Viewing v ON с.clientNo = v.clientNo

SELECT c.clientNo, fName, IName, propertyNo, comment

FROM Client JOIN Viewing USING clientNo

(соединение с использованием поля clientNo)

SELECT c.clientNo, fName, IName, propertyNo, comment

FROM Client NATURAL JOIN Viewing

(естественное соединение – для соединения используются одноименные поля таблиц Client и Viewing; одинаковое название имеет только поле clientNo, поэтому соединение происходит также по данному полю)

Пример 7. Сортировка результатов соединения таблиц.

SELECT s.branchNo, s.staffNo, fName, IName, propertyNo

FROM Staff s, PropertyForRent p

WHERE s.staffNo = p.staffNo

ORDER BY s.branchNo, s.staffNo, propertyNo

Пример 8. Соединение трех таблиц.

SELECT b.branchNo, b.city, s.staffNo, fName, IName, propertyNo

FROM Branch b, Staff s, PropertyForRent p

WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo

ORDER BY b.branchNo, s.staffNo, propertyNo

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

SELECT s.branchNo, s.staffNo, COUNT(*) AS count

FROM Staff s, PropertyForRent p

WHERE S.staffNo = p.staffNo

GROUP BY s.branchNo, s.staffNo

Декартово произведение

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

Пример 10. Вывести все пары сотрудников и отделений (независимо от того, работает ли в данном отделении сотрудник).

SELECT *

FROM Staff, Branch

(без условия WHERE)

SELECT *

FROM Staff CROSS JOIN Branch

(с использованием специального формата CROSS JOIN)

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

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

Рассмотрим пример.

Таблица Branch (информация об отделениях компании):

branchNo	bCity
B003	Glasgow
B004	Bristol
B005	London

Таблица PropertyForRent (информация о сдаваемых в аренду объектах):

propertyNo	pCity
PA14	Aberdeen
PL94	London
PG4	Glasgow

Выполним обычное (внутреннее) соединение этих таблиц с помощью следующего оператора SQL:

SELECT b.*, p.*

FROM Branch b, PropertyForRentl p

WHERE b.bCity = p.pCity;

Результат выполнения запроса:

branchNo	bCity	propertyNo	pCity
B003	Glasgow	PG4	Glasgow
B005	London	PL94	London

Результат выполнения запроса – информация об отделениях компании и сдаваемых в тех же городах в аренду объектах недвижимости. Как видно, в результате отсутствуют отделение компании в городе Bristol (в этом городе нет сдаваемых в аренду объектов) и объект в городе Aberdeen (в этом городе нет отделений компании). Чтобы в результат запроса попали также записи, которым не соответствует запись из другой таблицы, используется внешнее соединение: левое соединение (left join) – в результат попадают все записи из левой таблицы, правое соединение (right join) – все записи из правой таблицы, полное соединение (full join) – все записи из обеих таблиц.

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

SELECT b.*, р.*

FROM Branch b LEFT JOIN PropertyForRent p ON b.bCity = p.pCity;

Результат выполнения запроса:

branchNo	bCity	propertyNo	pCity
B003	Glasgow	PG4	Glasgow
B005	London	PL94	London
B004	Bristol	NULL	NULL

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

SELECT b.*, р.*

FROM Branch b RIGHT JOIN PropertyForRent p ON b.bCity = p.pCity;

Результат выполнения запроса:

branchNo	bCity	propertyNo	pCity
B003	Glasgow	PG4	Glasgow
B005	London	PL94	London
NULL	NULL	PA14	Aberdeen

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

branchNo	bCity	propertyNo	pCity
B003	Glasgow	PG4	Glasgow
B005	London	PL94	London
B004	Bristol	NULL	NULL
NULL	NULL	PA14	Aberdeen

Ключевые слова EXISTS и NOT EXIST

Ключевые слова EXISTS и NOT EXISTS предназначены для использования только совместно с подзапросами. Результат их обработки представляет собой логическое значение TRUE или FALSE. Для ключевого слова EXISTS результат равен TRUE в том и только в том случае, если в возвращаемой подзапросом результирующей таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результатом обработки ключевого слова EXISTS будет значение FALSE.

Пример 13. Вернуть данные об отделениях компании, в городах которых есть сдаваемые в аренду объекты.

SELECT *

FROM Branch b

WHERE EXISTS

(SELECT *

FROM PropertyForRent p

WHERE b.branchNo = p.BranchNo)

Комбинирование результирующих таблиц (операции UNION, INTERSECT и EXCEPT)

• Объединением двух таблиц А и В называется таблица, содержащая все строки, которые имеются в первой таблице (А), во второй таблице (В) или в обеих этих таблицах одновременно (операция UNION).

• Пересечением двух таблиц называется таблица, содержащая все строки, присутствующие в обеих исходных таблицах одновременно (операция INTERSECT).

• Разностью двух таблиц А и В называется таблица, содержащая все строки, которые присутствуют в таблице А, но отсутствуют в таблице В (операция EXCEPT).

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

Синтаксис:

operator [ALL] [CORRESPONDING [BY {columnl [, … ] }]]

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

Пример 14. Создайте список всех регионов, в которых либо находится отделение компании, либо располагаются сдаваемые в аренду объекты.

(SELECT city

FROM Branch

WHERE city IS NOT NULL)

UNION

(SELECT city

FROM PropertyForRent

WHERE city IS NOT NULL)

Другой вариант:

(SELECT *

FROM Branch

WHERE city IS NOT NULL)

UNION CORRESPONDING BY city

(SELECT *

FROM PropertyForRent

WHERE city IS NOT NULL)

Во втором варианте в запросах select возвращаются все столбцы, а не только те, по которым происходит объединение. В результате необходимо использовать конструкцию CORRESPONDING BY.

Пример 15. Использование операции INTERSECT. Создайте список всех городов, в которых располагаются и отделения компании, и сдаваемые в аренду объекты.

(SELECT city

FROM Branch

WHERE city IS NOT NULL)

INTERSECT

(SELECT city

FROM PropertyForRent

WHERE city IS NOT NULL)

Пример 16. Использование операции EXCEPT. Создайте список всех городов, в которых имеется отделение компании, но нет сдаваемых в аренду объектов.

(SELECT city

FROM Branch

WHERE city IS NOT NULL)

EXCEPT

(SELECT city

FROM PropertyForRent

WHERE city IS NOT NULL)