лабораторное занятие. 21 лаб. Демонстрирующие работы запроса select с использованием join
 Скачать 37.02 Kb.
|
|
В процессе объяснения вы , увидите примеры, демонстрирующие работы запроса SELECT с использованием JOIN. Объединение таблиц в SQL запросах SELECT: LEFT JOIN, LEFT OUTER JOIN, INNER JOIN, CROSS JOIN. Разница между запросами JOIN. Стандарт SQL делит объединение таблиц на три вида: внутреннее объединение таблиц (INNER JOIN), внешнее объединение таблиц (LEFT OUTER JOIN, RIGHT JOIN, FULL JOIN) и перекрестное объединение таблиц (CROSS JOIN). Вообще, стандарт SQL выделяет гораздо больше модификаторов JOIN: INNER JOIN – внутреннее объединение таблиц. LEFT JOIN или LEFT OUTER JOIN – левое внешнее объединение таблиц. RIGHT JOIN или RIGHT OUTER JOIN – правое внешнее объединение таблиц. FULL JOIN – полное объединение таблиц. CROSS JOIN – перекрестное объединение таблиц. Итак, создадим таблицы в базе данных при помощи команды CREATE:
Мы создали и наполнили таблицы, теперь мы можем приступать к объединению таблиц при помощи запросов SELECT с ключевыми словами JOIN во всех их проявлениях, доступных в SQL синтаксисе Внутреннее объединение таблиц в базах данных: INNER JOIN в SQL Внутреннее объединение таблиц реализуется в SQL при помощи ключевой фразы INNER JOIN. Важной особенностью внутреннего объединения таблиц в SQL запросах SELECT является то, что уточняющая фраза INNER JOIN работает симметрично, а это значит, что не имеет никакого значения, какая таблица будет использована слева от INNER JOIN, а какая справа. Когда вы хотите сделать внутреннее объединение таблиц в базах данных, то можете опускать ключевое слово INNER, так как конструкция INNER JOIN – это объединение таблиц по умолчанию. INNER JOIN работает довольно просто: SQL запрос SELECT сравнивает строки из левой таблицы со строками правой таблицы, после сравнения SQLite3 выполняет проверку условия соединения или, как еще говорят, вычисляется предикат соединения. Если это вычисление дает значение TRUE, то в результирующую таблицу будет добавлена новая строка, если результат FALSE, то строка в объединённую таблицу добавлена не будет. Как и во многих других СУБД можно использовать ключевое слово USING ключевое слово USING в запросах JOIN используется для перечисления списка столбцов, которые должны существовать в обеих таблицах, общий синтаксис USING в JOIN можно представить, как:
Объединение таблиц INNER JOIN – самый распространенный способ объединения. Давайте реализуем команду SELECT, которая будет использовать конструкцию INNER JOIN для объединения таблиц базы данных:
Таким образом мы сделали внутреннее объединение таблиц при помощи ключевой фразы INNER JOIN, мы объединили таблицу трэков и таблицу альбомов. В результирующей таблице нет трэков, у которых нет исполнителей и нет альбомов, в которых нет трэков, это очень важно и на это стоит обратить внимание. Но заметьте, что это не совсем удобно – выводить все столбцы объединенных таблиц в результирующую, давайте это исправим:
Такое внутреннее объединение таблиц выглядит более интересным, здесь нет суррогатных столбцов id. В общем, если вы объединяете таблицы конструкцией JOIN, то после SELECT вам необходимо перечислить имена столбцов из обеих таблиц, которые будут отображаться в результирующей таблице. Обратите внимание: если при объединение таблиц имена столбцов одинаковы, то следует использовать квалификатор: table_name.col_name. Мы можем задавать различные условия выборки данных клаузулой WHERE в том случае, когда объединяем таблице предикатом JOIN:
В данном случае мы получили объединенные таблицы при помощи INNER JOIN, выбрали только те столбцы, которые хотели увидеть, плюс сделали фильтрацию данных, оставив только песни группы Сплин. Так работает конструкция INNER JOIN в SQL и базах данных. Подведем итог: внутреннее объединение таблиц INNER JOIN в результате дает таблицу, состоящую из строк объединяемых таблиц, для которых результат выполнения предиката объединения ON равен TRUE. Другими словами: в результирующей таблицы будут только те строки из левой таблицы, для которых есть связанные строки из правой. Левое объединение таблиц в базах данных: LEFT JOIN и LEFT OUTER JOIN в SQL. Мы рассмотрели внутреннее объединение таблиц в базах данных, теперь давайте перейдем к рассмотрению внешнего объединения, и разберемся в чем разница между внутренним объединение таблиц и внешним. Есть только один вид внешнего объединения таблиц: LEFT JOIN или LEFT OUTER JOIN. Стандарт SQL на самом деле определяет три типа внешнего объединения: LEFT JOIN, RIGHT JOIN и FULL JOIN, но только LEFT OUTER JOIN. Поговорим про левое объединение таблиц, хотя более полно это звучит так: левое внешнее объединение таблиц. Внешнее объединение таблиц работает точно так же, как и внутреннее объединение таблиц, но есть разница в выводе строк после проверки предиката соединения. Давайте посмотрим пример LEFT OUTER JOIN и заодно поймем отличие от INNER JOIN, а также особенность внешнего объединения таблиц:
Обратите внимание на результаты работы LEFT OUTER JOIN: в результирующей таблицы есть все строки из таблицы tracks. Давайте изменим запрос SELECT так, чтобы таблица albums была слева:
Этими двумя примерами мы продемонстрировали разницу между внутренним объединением таблицы и внешним объединение: разницу между INNER JOIN и LEFT JOIN. В обоих случая вычисляет предикат объединения, который указан после ключевого слова ON, но разница в том, что при внешнем объединение LEFT OUTER JOIN в результирующей таблицы окажутся все строки левой таблицы, а к ним, если существуют, добавятся строки правой таблицы. Объяснение долгое, но понятное и написано простыми словами. Можно сказать по-другому: в результате работы LEFT OUTER JOIN в таблице, которую вернет SELECT, будут все строки из левой таблицы, к которым будут добавлены соответствующие значения из правой таблицы даже в том случае, если эти значения будут равны NULL. Если и так непонятно, то представим работу LEFT OUTER JOIN в виде диаграммы: Стоит заметить, что LEFT JOIN работает несимметрично и для него имеет значение, какая из таблиц будет указана первой, так как в результирующей таблицы будут находиться все строки из первой таблицы. Это еще одно отличие LEFT JOIN от INNER JOIN. Для INNER JOIN порядок таблиц в SQL запросе не имеет значения. Итак, мы разобрались с внешним объединением таблиц в SQL и подробно рассмотрели, как работает LEFT OUTER JOIN в базах данных, также мы выяснили разницу между INNER JOIN и LEFT JOIN. Перекрёстное объединение таблиц в SQL: CROSS JOIN в базах данных Перекрестное объединение таблиц или CROSS JOIN является симметричной операцией, как и INNER JOIN, а это означает, что порядок написания таблиц в запросе не имеет значения, результирующая таблица будет одинакова. Перекрестное объединение таблиц в математике представляет собой декартово произведение, а это означает, что в результате выполнения операции CROSS JOIN будет соединять каждую строку первой таблицы с каждой строкой второй таблицы и в результате мы получим таблицу, в которой будут все сочетания строк, которые возможны. Попробуем написать предложение SELECT, использующее ключевую фразу CROSS JOIN для объединения таблиц:
Здесь мы не будем приводить результаты работы CROSS JOIN, так как считаем не лучшей затей заставлять вас просматривать 45 строк результирующей таблицы. Но нам не обязательно смотреть на значения всех столбцов результирующей таблицы, их мы можем ограничить (на самом деле мы можем ограничить количество строк выборки при помощи LIMIT, сделать группировку выборки при помощи GROUP BY, упорядочить выборку при помощи ORDER BY, всё это реализуется так же, как и в простых запросах на выборку данных) и указать только нужные столбцы, давайте сделаем такой CROSS JOIN:
Надеемся, что вы разобрались с тем, как работает CROSS JOIN в базах данных и у вас не возникнет трудностей с тем, чтобы написать SQL запрос, делающий перекрёстное объединение таблиц в базе данных. Естественное объединение таблиц в SQL: NATURAL JOIN. Вы можете использовать для всех запросов на объединение таблиц ключевое слово NATURAL: NATURAL LEFT JOIN, NATURAL INNER JOIN и NATURAL CROSS JOIN. Принцип действия NATURAL JOIN очень похож на принцип действия ограничения USING с той лишь разницей, что NATURAL JOIN автоматически осуществляет проверку на равенство между значениями каждого столбца, которые есть в обеих таблицах:
Примерно так можно описать общий синтаксис использования NATURAL JOIN в SQL и базах данных. Примеры использования NATURAL JOIN в базах данных:
Вся разница в том, что вам не следует вычислять значения предикатов ON и USING, когда вы используете NATURAL JOIN, если же всё-таки вы используете NATURAL JOIN вместе с ON или USING, то получите ошибку: Error: a NATURAL join may not have an ON or USING clause. Объединение трех и более таблиц в SQL и базах данных Операция JOIN очень мощная, но довольно-таки ресурсоемкая штука, без объединения таблиц нам бы не было смысла нормализовывать отношения до второй нормальной формы или до третьей нормальной (как бы мы получали сводные данные из связанных таблиц, если бы не было JOIN?), конечно, мы немного утрируем, но операции объединения таблиц очень облегчают нам работу с базами данных. Мы уже видели на примерах INNER JOIN, CROSS JOIN и LEFT JOIN, как объединяются две таблицы, а теперь давайте посмотрим: как с помощью JOIN объединить три и более таблиц в одну. На самом деле всё очень просто, сперва пример объединения трех SQL таблиц в одну с помощью INNER JOIN:
А теперь пример объединения трех и более SQL таблиц для LEFT OUTER JOIN в базе данных :
Для CROSS JOIN мы пример писать не будем, так как в результате появится таблица с очень большим количеством строк, которую мы всё равно здесь не покажем. Добавим, что можно комбинировать вместе INNER JOIN, LEFTT JOIN и CROSS JOIN это обусловлено тем, что все действия по объединению нескольких таблиц делаются по порядку (можно представить, что JOIN – это операторы сложения). Сначала объединятся первых две таблицы и получится результирующая, после чего результирующая таблица будет объединена с третьей таблицей и так можно до бесконечности, главное, чтобы ваши таблицы были связаны значениями. Подведем общий итог: мы разобрались, как составлять SQL запросы SELECT, которые позволяют нам объединять две и более таблицы в одну. В языке SQL это делается при помощи ключевого слова JOIN, в том числе и в СУБД. MySQL дает возможность объединять таблицы тремя разными способами: INNER JOIN – внутреннее объединение таблиц, CROSS JOIN – перекрестное объединение таблиц и LEFT OUTER JOIN – внешнее объединение таблиц. |