Операторы LEFT OUTER JOIN и RIGHT OUTER JOIN

С операторами LEFT OUTER JOIN и RIGHT OUTER JOIN вы уже знакомы. Напомним: оператор LEFT OUTER JOIN предполагает, что в результат слияния обязательно войдут все записи из левой таблицы. Записи из правой таблицы сохранятся только в том случае, если значения в поле, по которому происходит объединение, совпадают со значениями в левой таблице. Оператор RIGHT OUTER JOIN устроен аналогичным образом, но бó‎льшим приоритетом обладает правая таблица.

Сразу перейдём к практике. Что нужно сделать:

Объединить данные таблиц artist и album и вывести всех исполнителей, добавив информацию о музыкальных альбомах, которые они выпустили.

Таблица artist хранит информацию об исполнителях и содержит поле artist_id с идентификатором и поле name с именем исполнителя. Таблица album содержит данные о музыкальных альбомах и включает в себя поля album_id и title с идентификатором альбома и его названием соответственно, а также поле artist_id с идентификатором исполнителя.

Выбираем тип объединения

Таблицы artist и album связаны по ключу artist_id, и связь между ними — «один ко многим», ведь одному исполнителю может соответствовать несколько музыкальных альбомов.

Раз нужно вывести данные обо всех исполнителях, оператор INNER JOIN не подойдёт: в таком случае исполнители без альбома в итоговую таблицу не попадут. Если использовать оператор LEFT OUTER JOIN, все исполнители попадут в выдачу.

Соединяем таблицы

Чтобы объединить таблицы, нужно указать подходящий оператор: LEFT OUTER JOIN или сокращённую версию LEFT JOIN.

SQL
SELECT art.name,
       alb.title
FROM artist AS art
LEFT OUTER JOIN album AS alb ON art.artist_id = alb.artist_id
LIMIT 10; 

Таблицы artist и album объединены по ключу artist_id, и благодаря оператору LEFT OUTER JOIN все значения из поля name таблицы artist попадут в итоговую таблицу. Если для какого-то исполнителя не найдётся альбома, на этом месте в поле title появится значение NULL.

name	title
AC/DC	For Those About To Rock We Salute You
AC/DC	Let There Be Rock
Accept	Balls to the Wall
Accept	Restless and Wild
Aerosmith	Big Ones
Alanis Morisette	Jagged Little Pill
Alice in Chains	Facelift
Antônio Carlos Jobim	Warner 25 Anos
Antônio Carlos Jobim	Chill: Brazil (Disc 2)
Apocalyptica	Plays Metallica By Four Cellos

Получить такую же таблицу можно и другим запросом.

SQL
SELECT art.name,
       alb.title
FROM album AS alb
RIGHT OUTER JOIN artist AS art ON art.artist_id = alb.artist_id
LIMIT 10; 

name	title
AC/DC	For Those About To Rock We Salute You
AC/DC	Let There Be Rock
Accept	Balls to the Wall
Accept	Restless and Wild
Aerosmith	Big Ones
Alanis Morisette	Jagged Little Pill
Alice in Chains	Facelift
Antônio Carlos Jobim	Warner 25 Anos
Antônio Carlos Jobim	Chill: Brazil (Disc 2)
Apocalyptica	Plays Metallica By Four Cellos

Результат не поменялся. Это связано с тем, что LEFT OUTER JOIN и RIGHT OUTER JOIN в большинстве случаев взаимозаменяемы. Достаточно просто поменять местами таблицы в запросе. В некоторых СУБД оператор RIGHT OUTER JOIN совсем не используют, потому что получить нужный результат можно, меняя таблицы местами.

Агрегируем и сортируем

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

SQL
SELECT art.name,
       COUNT(alb.title) AS total_album
FROM artist AS art
LEFT OUTER JOIN album AS alb ON art.artist_id = alb.artist_id
GROUP BY art.name
ORDER BY total_album DESC
LIMIT 10; 

name	total_album
Iron Maiden	21
Led Zeppelin	14
Deep Purple	11
Metallica	10
U2	10
Ozzy Osbourne	6
Pearl Jam	5
Faith No More	4
Foo Fighters	4
Various Artists	4

Так выглядит топ-10 исполнителей по количеству альбомов. Неудивительно, что Iron Maiden на первом месте, — они играют ещё с начала восьмидесятых!

Схема базы данных

Задача 2 / 6

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

Сначала соедините таблицы track и invoice_line по ключу track_id, а затем присоедините таблицу invoice по ключу invoice_id. В итоговую таблицу поместите два поля: name из таблицы track и invoice_date из таблицы invoice. Приведите дату к нужному формату.

Подсказка

Помните, что в итоговую таблицу должны войти все треки, даже если они не продавались. Выберите один из операторов: LEFT OUTER JOIN или RIGHT OUTER JOIN.

Посчитайте для каждого года число уникальных названий купленных треков.

Подсказка

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

Выгрузите таблицу из двух полей: первое поле с фамилией сотрудника, второе — с количеством пользователей, чьи запросы этот сотрудник обработал. Назовите поля employee_last_name и all_customers соответственно. Сгруппируйте записи по идентификатору сотрудника. Отсортируйте количество пользователей по убыванию, а фамилии сотрудников — в лексикографическом порядке.

Подсказка

У некоторых сотрудников есть менеджеры — их идентификаторы указаны в поле reports_to. Посмотрите внимательно на схему базы: таблица staff отсылает сама к себе. Это нормально, можно не создавать новую таблицу с менеджерами.

Теперь можно разобраться в иерархии команды. Отобразите таблицу с двумя полями: в первое поле войдут фамилии всех сотрудников, а во второе — фамилии их менеджеров. Назовите поля employee_last_name и manager_last_name.

Подсказка

Отобразите названия фильмов, в которых снимались актёры, не указанные в базе.

Подсказка

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

Подсказка

xxxxxxxxxx
 
SELECT extract (year from cast (i.invoice_date as timestamp)) AS year_of_invoice,
count(distinct(t.name))
FROM invoice as i
left join invoice_line as il ON i.invoice_id=il.invoice_id
left join track as t on il.track_id=t.track_id
GROUP BY year_of_invoice;

year_of_invoice	count
2009	442
2010	446
2011	440
2012	439
2013	433