SQL JOIN страница 4

• INNER JOIN (внутреннее соединение)
• Написать запросы SQL с JOIN самостоятельно, а затем посмотреть решения
• LEFT OUTER JOIN (левое внешнее соединение)
• RIGHT OUTER JOIN (правое внешнее соединение)
• FULL OUTER JOIN (полное внешнее соединение)
• Псевдонимы соединяемых таблиц
• CROSS JOIN (перекрестное соединение)
• JOIN и соединение более двух таблиц
• FULL JOIN для соединения непересекающихся результатов

• Реляционная модель данных
• Оператор SELECT

• Страница 1
• Страница 3

JOIN и соединение более двух таблиц

Реляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных, в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой) таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают внешние ключи - данные пересекаются, но только третья таблица содержит условие, в зависимости от которого может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора SQL JOIN в одном запросе можно соединить большое число таблиц. В таких запросах за одной секцией соединения следует другая, причём каждый следующий JOIN соединяет со следующей таблицей таблицу, которая была второй в предыдущем звене цепочки. Таким образом, синтаксис SQL запроса для соединения более двух таблиц следующий:

Особенности соединения трех и более таблиц

Мы уже соединяли две и три таблицы и уяснили, что эта операция требуется тогда, когда требуется вывести значения столбцов не из одной, а из нескольких таблиц. Но значения столбцов из некоторых соединяемых таблиц вовсе не требуются в результате. Такие таблицы требуется включать в цепочки соединений, потому что только через их первичные и внешние ключи можно "достучаться" до других таблиц, в которых как раз и содержатся необходимые для результата данные.

Итак, нас ждут увлекательные соединения в цепочки уже не только трёх, а четырёх и пяти таблиц.

Работать будем с базой данных Mediastar. Схема базы данных - на рисунке ниже. Для увеличения рисунка щёлкните по нему левой кнопкой мыши.

Скачать архив со скриптом на SQL для установки этой базы со всеми данными можно по этой ссылке.

Рассмотрим каждую таблицу базы данных и установим, какие данные содержатся в каждом столбце каждой таблицы.

Таблица Reader (Читатель). Содержит данные о читателях медиа (сайтов), которые дали своё согласие на отслеживание их активности в сети. Данные анонимны. В нашей базе представлены 100 читателей, что представляет некоторую уменьшенную модель данных, используемых в реальных исследованиях. Данные были уменьшены во избежание громоздкости выводимых результатов. В таблице содержатся столбцы:

• Reader_ID - идентификационный номер читателя, первичный ключ таблицы;
• Age - возраст в годах;
• Sex - пол (M - мужской, F - женский);
• City_ID - идентификационный номер города проживания, внешний ключ, связывающий эту таблицу с таблицей City.

Таблица City (Город) В таблице содержатся столбцы:.

• City_ID - идентификационный номер города, первичный ключ таблицы;
• Name - название;
• District - область;
• Populsize - численность населения в тысячах человек.

Таблица Visit (Визит) Содержит данные о визитах в интернет в течение 4 недель в пределах одного месяца. В таблице содержатся столбцы:.

• Visit_ID - идентификационный номер визита, первичный ключ таблицы;
• Datetimestart - дата и время начала визита в формате 'ДД-ММ-ГГГГ чч:мм:сс';
• Duration - продолжительность в секундах;
• Reader_ID - идентификационный номер читателя, внешний ключ, связывающий эту таблицу с таблицей Reader;
• Media_ID - идентификационный номер медиа (сайта), внешний ключ, связывающий эту таблицу с таблицей Media;
• Adv_ID - идентификационный номер единицы рекламы, внешний ключ, связывающий эту таблицу с таблицей Advertisment.

Таблица Media (Медиа) Содержит данные о сайтах, на которые заходили читатели в течение 4 недель в пределах одного месяца. В таблице содержатся столбцы:.

• Media_ID - идентификационный номер медиа, первичный ключ таблицы;
• Name - название, например, "Всегда на колёсах";
• Mlevel - уровень, допустимые значения: "National" (национальный) "Regional" (региональный);
• Mcont_ID - идентификационный типа содержания (контента) медиа, внешний ключ, связывающий эту таблицу с таблицей Mconttype;
• Mtheme_ID - идентификационный темы медиа (сайта), внешний ключ, связывающий эту таблицу с таблицей Mediatheme.

Таблица Mconttype (Тип контента медиа) Содержит данные о типах контента сайтов. В таблице содержатся столбцы:.

• Mcont_ID - идентификационный номер типа контента медиа, первичный ключ таблицы;
• Ctype - тип контента медиа, состоит из латинских букв T (текст), A (аудио), V (видео), буквы располагаются в обозначении типа в порядке убывания удельного веса того или иного подтипа содержания.

Таблица Mediatheme (Тема медиа) Содержит данные о темах сайтов. В таблице содержатся столбцы:.

• Mtheme_ID - идентификационный номер темы медиа, первичный ключ таблицы;
• Name - название темы, например, "Авто", "Строительство", "Новости".

Таблица Advertisment (Реклама) Содержит данные о единицах рекламы, которая показывалась читателям во время визита. В таблице содержатся столбцы:.

• Adv_ID - идентификационный номер единицы рекламы, первичный ключ таблицы;
• Acont_ID - идентификационный типа содержания (контента) рекламы, внешний ключ, связывающий эту таблицу с таблицей Advconttype;
• Advtheme_ID - идентификационный темы рекламы, внешний ключ, связывающий эту таблицу с таблицей Advtheme.

Таблица Advconttype (Тип контента рекламы) Содержит данные о типах контента сайтов. В таблице содержатся столбцы:.

• Acont_ID - идентификационный номер типа контента рекламы, первичный ключ таблицы;
• Advtype - тип контента рекламы, состоит из латинских букв T (текст), A (аудио), V (видео), буквы располагаются в обозначении типа в порядке убывания удельного веса того или иного подтипа содержания.

Таблица Advtheme (Тема рекламы) Содержит данные о темах рекламы. В таблице содержатся столбцы:.

• Advtheme_ID - идентификационный номер темы рекламы, первичный ключ таблицы;
• Name - название темы, например, "Авто", "Строительство", "Новости".

Пример 1. Вывести темы медиа (сайтов) и соответствующую каждой среднюю длительность просмотра. Отсортировать по возрастанию длительности просмотра.

Итак, в результирующей таблице будут столбцы из двух таблиц - Mediatheme и Visit. А таблица Media в цепочке из трёх соединяемых таблиц будет выполнять роль промежуточного звена, так как только через её первичный ключ Media_ID из таблицы Mediatheme можно достучаться до значений продолжительности просмотров в таблице Visit. Группировать достаточно по одному столбцу - Name из таблицы Mediatheme, так как применение агрегатной функции AVG к столбцу Duration из таблицы Visit обеспечивает автоматическую группировку по этому столбцу. Остаётся применить для сортировки оператор ORDER BY к столбцу Avgdur результирующей таблицы. Применение прямой сортировки (без дополнительного слова DESC) к столбцу с числовым типом данных даёт в результате выстраивание значений по возрастанию.

Запрос для выполнения этого задания должен быть таким:

SELECT t.Name, AVG(v.Duration) AS Avgdur FROM Mediatheme t JOIN Media m ON m.Mtheme_ID=t.Mtheme_ID JOIN Visit v ON m.Media_ID=v.Media_ID GROUP BY t.Name ORDER BY Avgdur

В результате выполнения этого запроса будет выведена следующая таблица:

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Работаем с таблицами, показанными на рисунке ниже, кроме таблицы City. Для увеличения рисунка щёлкните по нему левой кнопкой мыши.

Пример 2. Вывести темы медиа (сайты), имеющие число просмотров, большее или равно числу просмотров всех медиа (сайтов), имеющих тип контента "VAT".

До сих пор мы рассматривали запросы, в которых условие в ключевом слове ON действовало для двух таблиц, между которыми располагался JOIN. А что делать, если требуется с какой-либо таблицей соединить не одну, а, например, две таблицы? Самое удобное решение - расположить эту ещё одну таблицу последней в запросе и далее задать условие ON. В этом примере в начале цепочки соединяются таблицы Mediatheme, Media и Mconttype. Но к таблице Media нужно "прицепить" ещё и таблицу Visit. Спокойно располагаем эту таблицу в конце запроса: JOIN Visit v, а в условии ON задаём совпадение значений Media_ID из этой таблицы и таблицы Media. Таким образом, соединяемые таблицы могут и не быть "соседями" по расположению в запросе.

А как решается эта задача в целом? Применяем кванторную функцию ALL. И основной запрос, и подзапрос в качестве критерия сравнения содержат число просмотров, определяемое агрегатной функцией COUNT. Следовательно, в основном запросе условие выборки должно быть задано не в секции WHERE, а оператором HAVING. В подзапросе же условие задаётся в секции WHERE. Для сравнения числа просмотров применяем оператор "больше или равно" (>=). Так как основной запрос содержит агрегатную фукнцию, не забываем произвести группировку по столбцу Name таблицы Mediatheme. Оператор группировки GROUP BY размещаем перед условием, заданным оператором HAVING.

Запрос для выполнения этого задания должен быть таким:

SELECT mt.Name FROM Mediatheme mt JOIN Media m ON m.Mtheme_ID=mt.Mtheme_ID JOIN Mconttype mc ON mc.Mcont_ID=m.Mcont_ID JOIN Visit v ON v.Media_ID=m.Media_ID GROUP BY mt.Name HAVING COUNT(v.Visit_ID) >= ALL (SELECT COUNT(v.Visit_ID) FROM Mediatheme mt JOIN Media m ON m.Mtheme_ID=mt.Mtheme_ID JOIN Mconttype mc ON mc.Mcont_ID=m.Mcont_ID JOIN Visit v ON v.Media_ID=m.Media_ID WHERE mc.Ctype='VAT')

В результате выполнения этого запроса будет выведена следующая таблица:

• INNER JOIN (внутреннее соединение)
• Написать запросы SQL с JOIN самостоятельно, а затем посмотреть решения
• LEFT OUTER JOIN (левое внешнее соединение)
• RIGHT OUTER JOIN (правое внешнее соединение)
• FULL OUTER JOIN (полное внешнее соединение)
• Псевдонимы соединяемых таблиц
• CROSS JOIN (перекрестное соединение)
• JOIN и соединение более двух таблиц
• FULL JOIN для соединения непересекающихся результатов

Поделиться с друзьями