Для получения записей, содержащихся в одном наборе и отсутствующих в другом, служит оператор EXCEPT:
Запрос1
EXCEPT
Запрос2;
Чтобы узнать, для всех ли коробок есть соответствующие им по размеру, количеству и цвету крышки, можно воспользоваться следующим запросом:
SELECT Размер, Количество, Цвет
FROM Коробки
EXCEPT
SELECT Размер, Количество, Цвет
FROM Крышки;
Возвращенные этим запросом записи будут содержать сведения о коробках, для которых нет подходящих крышек. Если же запрос вернет пустую таблицу, то это будет означать, что для всех коробок есть соответственные крышки.
Внешние соединения
Все соединения таблиц, рассмотренные ранее, являются внутренними. Во всех примерах вместо ключевого слова JOIN можно писать INNER JOIN. Из таблицы, получаемой при внутреннем соединении, удаляются все записи, у которых нет соответствующих строк одновременно в обеих исходных таблицах. При внешнем соединении (OUTER JOIN) несоответствующие строки сохраняются. В этом и заключается отличие внешнего соединения от внутреннего.
В запросе, имеющем соединение, будем называть таблицу левой, если ее имя в операторе запроса предшествует ключевому слову JOIN, и правой, если ее имя следует за словом JOIN.
Внешнее соединение может сохранить записи, для которых не находится соответствия в другом наборе. В этом случае недостающие поля заполняются значением NULL. Решение о том, войдет ли такая запись в результат внешнего соединения, зависит от того, в каком из соединяемых наборов (таблиц) отсутствуют данные, и от типа внешнего соединения.
Существуют три разновидности внешних соединений.
Левое внешнее соединение
Операция LEFT JOIN (LEFT OUTER JOIN) возвращает все строки из левой таблицы, соединенные с теми строками из правой таблицы, для которых выполняется условие соединения. Если во второй таблице нет таких строк, то в качестве значений столбцов правой таблицы будут установлены значения NULL.
В базе данных booktown в таблице books содержится общая информация о книгах, а в таблице editions хранятся данные, относящиеся к конкретному изданию – код ISBN, издатель и дата публикации. В таблицу editions входит поле book_id, связывающее ее с полем id, которое является первичным ключом таблицы books.
Допустим, требуется информация о каждой книге вместе со всеми имеющимися кодами ISBN:
SELECT title, isbn
FROM books INNER JOIN editions
ON (books.id = editions.book_id);
Если у книги нет печатного издания (или информация об этом издании еще не занесена в базу данных), информация о ней не будет включена в результат данного запроса.
Чтобы получить данные о каждой книге, следует воспользоваться следующим запросом:
SELECT title, isbn
FROM books LEFT OUTER JOIN editions
ON (books.id = editions.book_id);
Теперь в итоговом наборе будут присутствовать и те книги, у которых отсутствуют коды ISBN. В этом запросе использовано левое внешнее соединение. Выбор объясняется тем, что запрос должен вернуть названия книг, для которых существуют или не существуют коды ISBN. Поскольку таблица books стоит слева от ключевого слова JOIN, задача решается при помощи левого внешнего соединения.
Правое внешнее соединение
Операция RIGHT JOIN (RIGHT OUTER JOIN) возвращает все строки из правой таблицы, соединенные с теми строками из левой таблицы, для которых выполняется условие соединения. Если во второй таблице нет таких строк, то в качестве значений столбцов правой таблицы будут установлены значения NULL.
В качестве примера получим названия книг, у которых нет кодов ISBN:
SELECT title
FROM editions RIGHT JOIN books
ON (editions.book_id = books.id)
WHERE isbn IS NULL;
Полное внешнее соединение
Операция 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 подразумевают внешнее соединение.
Подзапросы
Подзапросом называется команда SELECT, заключенная в круглые скобки, которая выполняется в контексте другой команды SQL. Обычно подзапросы содержатся в условии оператора WHERE или HAVING внешнего запроса. В свою очередь, подзапрос может содержать другой подзапрос и т.д.
Простые подзапросы
Простые подзапросы характеризуются тем, что они формально никак не связаны с содержащими их внешними запросами. Это обстоятельство позволяет сначала выполнить подзапрос, результат которого затем используется для выполнения внешнего запроса. Кроме простых подзапросов, существуют еще и связанные подзапросы.
Рассматривая простые подзапросы, следует выделить три частных случая:
· подзапросы, возвращающие единственное значение;
· подзапросы, возвращающие список значений из одного столбца таблицы;
· подзапросы, возвращающие набор записей.