DeepEdit!

Программирование баз данных на Oracle, техническая документация, литература, статьи и публикации

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта

Объединение таблиц


С помощью запросов SELECT можно организовать совместное использование
нескольких наборов данных. Для этого наборы данных должны быть объединены.
Объединение таблиц означает создание нового набора, представляющего собой
декартово произведение строк из этих таблиц. Напомним, что в теории множеств
декартовым произведением множеств называется такое множество, элемнтами
которого являются пары - всевозможные комбинации элементов
множеств-сомножителей. Например, множества A {a,b,c} и B{u,v}, их декартово
произведение - это множество A x B {au,av,bu,bv,cu,cv}.

Две таблицы могут быть объединены, если их имена указать в секции FROM
оператора выборки SELECT. Но результат - декартово произведение строк исходных
таблиц, представляет собой таблицу полями, входящими в каждую из таблиц и
строками, являющимися комбинациями строк этих таблиц. Например:

       SELECT * FROM authors,books;

Однако полученный набор является черезчур большим, как правило при объединении
вводят ограничивающие условия в секции WHERE
       
       SELECT last_name, title FROM authors,books 
                               WHERE authors.id=books.authors_id ;

В языке SQL возможность выборки данных из разных источников с использованием
всех их полей формально называется объединением (join) и производится по
критериям, задаваемым в специальной секции JOIN оператора SELECT.
Существуют три разновидности объединений.
       Перекрестные объединения (CROSS JOIN) - декартово произведение двух 
       наборов        данных. Произведение не определяет отношений между наборами,
       а лишь содержит все возможные комбинации записей объединяемых 
       наборов.
       Внутренние объединения (INNER JOIN) - подмножество декартова про-
       изведения двух наборов с критерием, используемым для объединения
       записей ( критерий - выражение, возвращающее логическую величину
       - признак вхождения записи в объединенный набор.
       Внешние объединения (OUTER JOIN) - как и внутренние объединения,
       содержат критерий объединения записей, но обязательно возвращают минимум
       один экземпляр каждой записи заданного набора. Это может быть левый
       набор (источник данных слева от ключевого слова JOIN), правый набор 
       (источник данных справа от ключевого слова JOIN) или оба набора в 
       зависимости от конкретной разновидности внешнего объединения. 
       Пустые поля в тех частях записей, кoторые не отвечают
       критерию объединения, содержат значение NULL.

3.1. Перекрестные объединения.

Результат перекрестного объединения принципиально не отличается от
перечисления источников через запятую. Поэтому в команде выборки с
перекрестным объединением практически всегда должна присутствовать секция
WHERE, уточняющая связи между объединяемыми наборами данных

       SELECT b.id, title, a.id, last_name
       FROM books AS b CROSS JOIN authors AS a
       WHERE b.author_id = a.id;

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

3.2. Внутренние и внешние объединения.

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

Синтаксис:    источник_1 [ NATURAL ] тип_объединения  источник_2
             [ { ON (условие [,...])  | USING имя_столбца [,...] } ] 

       Здесь: "источник_1" - первый из объединямых наборов, имя таблицы
       или подзапрос;
       [NATURAL] - два набора объединяются по равным значениям одноименных
       полей, при наличии ключевого слова NATURAL учитываются синонимы полей
       (если были назначены)? а секции ON и USING становятся не просто
       излишними, а недопустимыми;
       тип_объединения - один из терминов [INNER] JOIN, LEFT [OUTER] JOIN,
       RIGHT [OUTER] JOIN, FULL [OUTER] JOIN;
       "источник_2" - второй из объединямых наборов, имя таблицы
       или подзапрос;
       ON ( условие [,...]) - отношение между источниками, в этой секции
       можно задавать произвольный критерий по аналогии с тем, как задают
       условия в секции WHERE;
       USING ( поле [,...]) - одноименные поля источников, по совпадающим
       значениям которых производится объединение ( в отличие от NATURAL
       JOIN позволяет ограничиться отдельными одноименными полями, тогда
       как NATURAL проводит объ единение по всем одноименным полям, по
       аналогии с NATURAL в параметрах секции учитываются синонимы полей).

3.3. Внутренние объединения.

Конструкция INNER JOIN введена в язык SQLдля того, чтобы разделить условия
объединения (JOIN) и условия принадлежности строк к данному набору (WHERE).
Так, два следующих запроса идентичны (т.е. дают одинаковый результат)

       SELECT title,last_name FROM books , authors 
       WHERE (books.author_id = authors.id) AND last_name = 'Geisel';

       SELECT title,last_name FROM books AS b INNER JOIN authors AS a
       ON (b.author_id = a.id) 
       WHERE last_name = 'Geisel';

но второй вариант отделяет крирерии связи от критериев отбора, что существенно
упрощает чтение и модификацию запросов, т.е. более технологично.
При простых объединениях по совпадающим значениям вместо ON можно использовать
секции USING и NATURAL, но при этом имена полей связи должны быть одинаковыми
в разных таблицах. Если это не так, то надо назначать псевдонимы для таких
полей.

       SELECT title,last_name 
       FROM books NATURAL INNER JOIN authors AS a (author_id)
       WHERE last_name = 'Geisel';

В данном примере команда SELECT назначает синоним autor_id первому полю
таблицы authors, которое в действительности называется id, после чего задается
объединение по NATURAL.
Следующий пример демонстрирует использование секции USING

       SELECT title,last_name 
       FROM books INNER JOIN authors AS a (author_id)
       USING (author_id) 
       WHERE last_name = 'Geisel';

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

3.4. Внешние объединения.

Внешнее объединение может сохранить записи, для которых нет соответствующих в
другом наборе. В этом случае недостающие поля заполняются значениями NULL.
Решение о том, войдет ли такая запись в итоговый набор, зависит от того, в
каком из наборов отсутствуют данные и от типа объединения.
Различают три вида внешних объединений:
       - левое внешнее объединение LEFT OUTER JOIN, содержащее как минимум
       один экземпляр каждой записи из набора, указанного слева от
       ключевого слова JOIN, при этом отсутствующие значения из правого 
       набора заполняются значениями NULL;

       - правое внешнее объединение RIGHT OUTER JOIN, содержащее как минимум
       один экземпляр каждой записи из набора, указанного справа от
       ключевого слова JOIN, при этом отсутствующие значения из левого 
       набора заполняются значениями NULL;

       - полное внешнее объединение FULL OUTER JOIN, содержащее как минимум
       один экземпляр каждой записи из каждого объединяемого набора, при этом
       отсутствующие значения в записях нового набора заполняются 
       значениями NULL.
Запрос

       SELECT title,isbn 
       FROM books LEFT OUTER JOIN editions
       ON (books.id = editions.book_id) ;

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

3.5. Сложные объединения.

Хотя секция JOIN объединяет только два набора данных, на практике объединяться
могут любое количество источников. За набором, созданным посредством
объединения, может следовать новая секция JOIN, при этом для группировки
следует использовать круглые скобки.

       SELECT last_name, title, isbn FROM
       (authors AS a JOIN books AS b (ON a.id = b.author_id ))
       JOIN editions AS e ON (b.id = e.book_id);


Задания


1.  Получить список фамилий и имен авторов и названия написанных ими книг,
    отсортированный по авторам.
2.  Получить список названий и авторов книг с указанием их темы, отсортировать
    по названию книги.
3.  Получить список номеров ISBN для книг в бумажном переплете (тип "р") 
    с указанием издательств, выпустивших книгу; список отсортировать 
    по издательствам.
4.  Изменить предыдущий запрос так, чтобы выводить еще и название книги.
5.  Составить список покупателей с названиями купленных ими книг.
    Отсортировать по покупателям.
6.  Вывести количество книг, которые приобрел каждый покупатель. Отсортировать
    по покупателю.
7.  Модифицировать предыдущий запрос таким образом, чтобы в списке
    присутствовали и те покупатели, которые не купили ни одной книги.
8.  Вывести список покупателей, не покупавших книг. Отсортировать по
    покупателю.

 









jAntivirus