Операторы для работы с несколькими запросами

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

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

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

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

1) Левое внешнее соединение (LEFT OUTER JOIN)

2) Правое внешнее соединение (RIGHT OUTER JOIN)

3) Полное внешнее соединение (FULL OUTER JOIN)

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

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

Синтаксис внешних объединений похож на синтаксис других объединений.

SELECT columns

FROM table1

LEFT OUTER JOIN table2

ON join_conditions

SELECT columns

FROM table1

RIGHT OUTER JOIN table2

ON join_conditions

SELECT columns

FROM table1

FULL OUTER JOIN table2

ON join_conditions

Ключевое слово OUTER может быть опущено.

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

Пример:

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

SELECT a.au_id, a.fname, a.lname

FROM authors a

LEFT OUTER JOIN title_authors ta

ON a.au_id = ta.au.id

WHERE ta.au_id IS NULL;

Операторы для работы с несколькими запросами

UNION

Иногда требуется как-то работать с несколькими результатами запросов.

Комбинировать строки можно с помощью операторов UNION, INTERSECT и EXCEPT. Данные операторы предназначены для преобразования двух команд SELECT в единый результат. Можно смешивать эти операторы так, чтобы комбинировать несколько таблиц, а не только две. Оператор UNION из перечисленных используется чаще всего.

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

1) списки столбцов команды SELECT в двух запросах должны включать одинаковое число столбцов;

2) соответствующие столбцы в двух запросах должны быть заданы в одинаковом порядке;

3) если имена соответствующих столбцов совпадает, то их название будет использовано в результате. Если названия соответствующих столбцов различаются, то СУБД самостоятельно определит имя столбца в результате. Большинство СУБД заимствуют названия столбцов для результата из первого отдельного запроса в команде UNION

4) предложения GROUP BY и HAVING можно задавать только в отдельных запросах, их нельзя использовать для изменения конечного результата

5) предложение ORDER BY может использоваться только в последнем запросе команды UNION. Эта сортировка применяется к конечному результату после комбинирования. Так как названия столбцов в итоге различаются для разных СУБД, для указания порядка сортировки проще всего использовать относительные положения столбцов. То есть вместо названия столбца можно указывать порядковый номер этого столбца.

Комбинирование строк

select_statement1

UNION [ALL]

select_statement2

select_statement1 и select_statement2 - команды SELECT. Необходимо задать количество и порядок столбцов для двух команд, а тип данных в соответствующих столбцах должен быть совместимым. Если опция ALL не задается, то повторяющиеся строки будут удалены из результата.

Примеры:

1) отобразить список городов, в которых живут авторы и находятся издательства.

SELECT city FROM authors

UNION

SELECT city FROM publishers

По умолчанию UNION удаляет из результата повторяющиеся строки.

2) Отобразить список фамилий авторов и названий издательств из города Москва, указав тип, то есть нужно указать, автор это или издательство. Отсортировать по названию/имени.

SELECT 'author' AS "Type",

lname,

city

FROM authors

WHERE city='Moscow'

UNION

SELECT 'publisher',

name,

city

FROM publishers

WHERE city='Moscow'

ORDER BY 1 ASC

3) Повысить цены на книги по истории на 10%, а на книги по психологии - на 20%, не меняя цены на другие книги. Показать название книги, жанр, цену и измененную цену.

SELECT title_name, type, price,

price*1.10 AS "New Price"

FROM titles

WHERE type = 'history'

UNION

SELECT title_name, type, price, price*1.20

FROM titles

WHERE type = 'psychology'

UNION

SELECT title_name, type, price, price

FROM titles

WHERE type NOT IN ('psychology', 'history')

ORDER BY type ASC, title_name ASC

UNION является коммутативной командой: A UNION B - это то же самое, что B UNION A.

Для объединения в одной команде UNION и UNION ALL следует пользоваться круглыми скобками, чтобы задать порядок расчетов.

SELECT * FROM table1

UNION ALL

(

SELECT * FROM table2

UNION

SELECT * FROM table3

)

и

(

SELECT * FROM table1

UNION ALL

SELECT * FROM table2

)

UNION

SELECT * FROM table3

Первая команда удаляет повторы в объединении table2 и table3, но не удаляет их в результате и table1. Вторая команда сохраняет повторы в объединении table1 и table2, но удаляет их в последующем объединении с table3, поэтому ALL не влияет на конечный результат команды.

INTERSECT

Команда INTERSECT преобразует результаты двух запросов в один результат, который включает все общие строки в результатах выполнения двух запросов. Аересечения имеют такие же ограничения, как и объединения UNION.

Поиск общих строк

select_statement1

INTERSECT

select_statement2

select_statement1 и select_statement2 - команды SELECT. Необходимо задать количество и порядок столбцов для двух команд, а тип данных в соответствующих столбцах должен быть совместимым. Повторяющиеся строки будут удалены из результата.

INTERSECT является коммутативной командой: A INTERSECT B – это то же самое, что B INTERSECT A.

В SQL у INTERSECT более высокий приоритет по сравнению с UNION и EXCEPT, но в конкретной СУБД приоритеты могут быть другими. Чтобы задать порядок расчета в запросах со смешанными операторами, стоит пользоваться круглыми скобками.

Очень полезно воспринимать UNION как логический вариант OR, а INTERSECT - как логический вариант AND. Например, если нужно узнать, какие товары поставляются продавцов A и B.

SELECT product_id

FROM vendor_a_product_list

UNION

SELECT product_id

FROM vendor_b_product_list

Если нужно узнать, какие товары поставляются и продавцом A, и продавцом B, то:

SELECT product_id

FROM vendor_a_product_list

INTERSECT

SELECT product_id

FROM vendor_b_product_list

Microsoft Access, Microsoft SQL Server и MySQL не поддерживают команду INTERSECT. Её можно переписать с использованием INNER JOIN или с использованием предлжения EXISTS.

SELECT DISTINCT authors.city

FROM authors

INNER JOIN publishers

ON authors.city = publishers.city

SELECT DISTINCT city

FROM authors

WHERE EXISTS

(SELECT *

FROM publishers

WHERE authors.city = publishers.city)

EXCEPT

Команда EXCEPT преообразует результаты двух запросов в один результат, который включает только строки первого запроса. Разница между EXCEPT и INTERSECT в том, что A INTERSECT B включает строки из таблицы A, которые повторяются в таблице B, а A EXCEPT B включает строки из таблицы A, которые не повторяются в таблице B.

Поиск разных строк

select_statement1

EXCEPT

select_statement2

select_statement1 и select_statement2 - команды SELECT. Необходимо задать количество и порядок столбцов для двух команд, а тип данных в соответствующих столбцах должен быть совместимым. Повторяющиеся строки будут удалены из результата.

В отличие от команд UNION и INTERSECT, команда EXCEPT не является коммутативной:

A EXCEPT B – это не то же самое, что B EXCEPT A.

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

SELECT city FROM authors

EXCEPT

SELECT city FROM publishers

Microsoft Access, Microsoft SQL Server и MySQL не поддерживают команду INTERSECT.

Её можно переписать с использованием внешнего запроса или с использованием предлжения NOT EXISTS или NOT IN.

SELECT DISTINCT a.city

FROM authors a

LEFT OUTER JOIN publishers p

ON a.city = p.city

WHERE p.city IS NULL

или

SELECT DISTINCT city

FROM authors

WHERE NOT EXISTS

(SELECT *

FROM publishers

WHERE authors.city = publishers.city)

или

SELECT DISTINCT city

FROM authors

WHERE NOT IN

(SELECT city

FROM publishers)