Соединение таблиц и анализ данных.

 

Цель и порядок работы

1.1 Цель работы

Ознакомиться со структурой и синтаксисом основных конструкций формирования соединений.

 

1.2 Порядок выполнения работы

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

- Выполнить задание для самостоятельной работы;

- Ответить на контрольные вопросы;

- Представить результаты работы преподавателю.

 

2. Общие сведения

Для создателей стандарта SQL2 внешние объединения были серьезной пробле­мой. Так как внешние объединения — это единственный способ представления результатов ряда крайне необходимых запросов, было важно, чтобы стандарт SQL2 поддерживал данное понятие. Кроме того, внешние объединения поддерживались многими СУБД и становились все более важной частью SQL. Однако, как видно из предыдущей лабораторной работы, способы представления внешних объединений в различных СУБД сильно отличались друг от друга. Кроме того, все способы обозначения внешних объединений в коммерческих программных продуктах страдали недостат­ками и выбирались по принципу их минимального влияния на язык SQL, а не из соображений ясности и точности.

На этом фоне в стандарте SQL2 был определен совершенно новый метод поддержки внешних объединений, который не опирался ни на одну популярную СУБД. В спецификации стандарта SQL2 поддержка внешних объединений осуществляется в предложении FROM с тщательно разработанным синтаксисом, позволяю­щим пользователю точно определить, каким образом исходные таблицы должны быть объединены в запросе. Механизм поддержки внешних объединений, представленный в стандарте SQL2, обладает двумя преимуществами. Во-первых, с появлением стан­дарта SQL2 стало возможным создавать объединения самых сложных видов. Во-вто­рых, существующие СУБД могут без каких-либо конфликтов поддерживать имею­щиеся в стандарте SQL2 расширения стандарта SQL1 и в то же время сохранять поддержку своего собственного синтаксиса для внешних объединений.

Этих преимуществ удалось добиться за счет значительного усложнения прежде одного из самых простых разделов языка SQL. По сути, расширенная поддержка объ­единений является частью значительного расширения возможностей запросов в SQL2 в целом. Стало возможным выполнять операции над результатами запроса как над множествами (сложение, пересечение, вычитание таблиц) и применять выражения, манипулирующие строками, таблицами и подчиненными запросами.

 

 

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

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

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

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

В число этих конструкций входят следующие:

- INNER JOIN—внутреннее соединение.

- OUTER JOIN— внешнее соединение, как левое (LEFT), так и правое (RIGHT).

- FULL JOIN— полное соединение.

- CROSS JOIN—перекрестное соединение.

 

Конструкции JOIN

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

Конструкция JOIN выполняет именно ту задачу, на которую указывает смысл соот­ветствующего английского глагола, — соединяет информацию из двух таблиц в один результирующий набор. Результирующий набор можно рассматривать как «виртуаль­ную» таблицу. В него входят и столбцы, и строки, а сами столбцы характеризуются определенными типами данных. Ре­зультирующий набор можно использовать так, как если бы это была таблица, и обра­щаться к нему для выполнения других запросов.

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

Например, предположим, что строки с данными о департаменте берутся из таблицы Departments (таблица 6).

 

Таблица 6 – Одна строка с данными из таблицы Departments.

Department_id	Department_name	Location_id
	IT

 

Теперь перейдем к рассмотрению строки из таблицы с данными сотрудниках, называемой employees (таблица 7).

 

Таблица 7 – Одна строка с данными из таблицы Emploees

First_name	Last_name	Department_id
Bruce	Ernst

 

Конструкция JOIN позволяет создать одну строку из двух строк, находящихся в полностью отдельных таблицах (таблица 8).

 

Таблица 8 – Строка, полученная в результате соединения строк с данными из таблиц Departments и Employees

Department_id	Department_name	Location_id	First_name	Last_name
	IT		Bruce	Ernst

 

С помощью этой конструкции JOIN строки соединяются на основании СВЯЗИ "один к одному" (по крайней мере такое впечатление складывается на основании приведен­иях данных). Одна строка из таблицы Departments соединяется с одной строкой из табли­цы Employees.

Немного дополним условия этого примера и рассмотрим, что при этом произойдет. Введем еще одну строку в таблицу Employees (таблица 9).

 

Таблица 9 - Две строки с данными из таблицы Emploees

First_name	Last_name	Department_id
Bruce	Ernst
David	Austin

 

Теперь рассмотрим, что произойдет после соединения дополненной таблицы Employees с той же таблицей Departments (содержащей только одну строку) (таблица 10).

 

Таблица 10 - Результаты соединения дополненной таблицы Employees с таблицей Department

 

Department_id	Department_name	Location_id	First_name	Last_name
	IT		Bruce	Ernst
	IT		David	Austin

 

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

 

Условия соединения синтаксически описываются следующим образом (заметьте, что типы соединения намеренно исключены из этого примера).

SELECT *

FROM имя_таблицы1 JOIN имя_таблицы2

ON имя_тоблицы1. столбец1 = имя_таблицы2.столбец2

{AND|OR} имя_таблицы1.столбецЗ = имя_таблицы2.столбец4

JOIN table_nameЗ

ON имя_таблицы1.столбецА = имя_таблицыЗ.столбецА

[{AND|OR} имя_таблицы1.столбецЗ = имя_таблицы2.столбец4

Для создания предложения JOIN с несколькими условиями используется оператор AND или OR.

 

КОНСТРУКЦИИ INNER JOIN

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

Наиболее предпочтительный формат кода для конструкции INNER JOIN выглядит примерно таким образом:

SELECT *

FROM имя_таблицы1 INNER JOIN имя_таблицы2

ON имя_тоблицы1. столбец1 = имя_таблицы2.столбец2

{AND|OR} имя_таблицы1.столбецЗ = имя_таблицы2.столбец4

 

Отличительная особенность INNER JOIN состоит в том, что она возвращает только те строки, которые были согласованны по всем полям.

 

Пример 1:

SELECT departments.department_id, department_name, location_id, employee_id, first_name, last_name

FROM departments inner join employees

ON (departments.department_id = employees.department_id)

 

Рисунок №49 – Результат выборки

 

Рассмотрим еще один небольшой запрос, который позволяет ознакомиться с особенностя­ми конструкции JOIN:

Пример 2:

SELECT departments.department_id, department_name, location_id, employee_id, first_name, last_name

FROM departments inner join employees

ON (departments.department_id = employees.department_id)WHERE employee_id > 107

 

Рисунок №50 – Результат выборки из примера №2