5.2.2. Правое внешнее соединение

Операция RIGHT JOIN (RIGHT OUTER JOIN) возвращает все строки из правой таблицы, соединенные с теми строками из левой таблицы, для которых выполняется условие соединения. Если во второй таблице нет таких строк, то в качестве значений столбцов правой таблицы будут установлены значения NULL.

В качестве примера получим названия книг, у которых нет кодов ISBN:

SELECT title

FROM editions RIGHT JOIN books

ON (editions.book_id = books.id)

WHERE isbn IS NULL;

5.2.3. Полное внешнее соединение

Операция FULL OUTER JOIN выполняет одновременно и левое, и правое внешние соединения. В итоговом наборе наряду с соответствующими записями сохраняются и несоответствующие строки как из левой, так и из правой таблиц. Поля, которым не нашлось соответствия в другом наборе, заполняются значением NULL.

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

SELECT title, isbn

FROM books FULL OUTER JOIN editions

ON (books.id = editions.book_id);

Ключевое слово OUTER во внешних соединениях PostgreSQL является необязательным. Определения LEFT JOIN, RIGHT JOIN и FULL JOIN подразумевают внешнее соединение.

5.3. Подзапросы

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

5.3.1. Простые подзапросы

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

Рассматривая простые подзапросы, следует выделить три частных случая:

• подзапросы, возвращающие единственное значение;

• подзапросы, возвращающие список значений из одного столбца таблицы;

• подзапросы, возвращающие набор записей.

Работа с единственным значением

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

SELECT *

FROM editions

WHERE cost > (SELECT AVG(cost)

FROM editions);

В данном запросе сначала выполняется подзапрос (SELECT AVG(cost) FROM editions). Он возвращает единственное значение (а не набор записей) – среднее значение столбца cost. Точнее, данный подзапрос возвращает единственную запись, содержащую единственное поле. Далее выполняется внешний запрос, который выводит все столбцы таблицы editions и записи, в которых значение столбца cost больше значения, полученного с помощью подзапроса. Таким образом, сначала выполняется подзапрос, а затем внешний запрос, использующий результат подзапроса.

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

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

SELECT *

FROM shipments

WHERE customer_id IN (SELECT id

FROM customers

WHERE first_name = 'James');

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

Теперь сформулируем запрос, возвращающий коды ISBN, книги с которыми никто не покупал:

SELECT isbn

FROM editions

WHERE isbn NOT IN (SELECT isbn

FROM shipments);

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

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

SELECT isbn, cost, retail

FROM stock

WHERE (isbn, stock) IN (SELECT isbn, 0

FROM editions

WHERE type = 'p');

Подзапрос к таблице editions группирует поле isbn с целочисленной константой 0 для всех книг в бумажной обложке. Возвращаемые подзапросом записи сравниваются с полем isbn и stock таблицы stock с использованием ключевого слова IN.

Пусть подзапрос возвращает несколько записей. Тогда чтобы в условии внешнего оператора WHERE можно было использовать операторы сравнения, требующие единственного значения, используются кванторы ALL и SOME (ANY).

Получим список книг с самой высокой ценой:

SELECT *

FROM stock AS s1

WHERE s1.retail >= ALL (SELECT s2.retail

FROM stock AS s2);

Подзапрос SELECT s2.retail FROM stock AS s2 возвращает список цен всех книг. Выражение >= ALL означает, что внешний запрос должен вернуть только те записи, в которых значение столбца retail больше или равен каждого значения, возвращенного вложенным подзапросом.

Итоговый набор книг будет иным, если вместо квантора ALL применить SOME или ANY:

SELECT *

FROM stock AS s1

WHERE s1.retail > SOME (SELECT s2.retail

FROM stock AS s2);

Этот запрос вернет список книг, цена которых выше цены хотя бы одной какой-либо книги.

Работа с набором записей

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

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

SELECT *

FROM

(SELECT Начальный_пункт, Конечный_пункт

FROM Рейсы

UNION

SELECT T1.Начальный_пункт, T2.Конечный_пункт

FROM Рейсы T1, Рейсы T2

WHERE T1.Конечный_пункт = T2.Начальный_пункт) AS T

WHERE T.Начальный_пункт = 'A';