Просмотр содержимого таблиц, находящихся с обеих сторон от операции соединения, с помощью конструкции full JOIN

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

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

Вначале выполним соединение двух первых таблиц с использованием конструкции FULL JOIN:

Пример 9:

 

SELECT d.department_id, department_name, job_id, employee_id

FROM departments d

FULL OUTER JOIN job_history jh

ON (d.department_id = jh.department_id)

 

Рисунок №57 - Результат выборки из примера №9.

 

Введем еще одну конструкцию JOIN:

Пример 10:

SELECT d.department_id, department_name, jh.job_id, employee_id, j.job_title

FROM departments d

FULL OUTER JOIN job_history jh

ON (d.department_id = jh.department_id)

FULL JOIN jobs j

on (jh.job_id = j.job_id)

 

 

Рисунок №58 - Результат выборки из примера №10.

 

Теперь в нашем распоряжении имеется оператор, позволяющий получить из рас­сматриваемых таблиц всю имеющуюся в них информацию.

 

 

Конструкция CROSSJOIN

Операторы с конструкциями CROSS JOIN обладают действительно необычными особенностями. Соединения CROSS JOIN отличаются от соединений других типов тем, что в них отсутствуют операции ON, а также тем, что в них происходит соедине­ние каждой строки таблиц, находящихся с одной стороны от ключевого слова JOIN, с каждой строкой таблиц, находящихся с другой стороны от ключевого слова JOIN. Короче говоря, в конечном итоге формируется декартово произведение всех строк, заданных по обе стороны от ключевого слова JOIN. Операторы с конструкцией CROSS JOIN имеют такой же синтаксис, как и любые другие операторы JOIN, за ис­ключением того, что в них используется ключевое слово CROSS (вместо INNER, OUTER или FULL), а операция ON отсутствует. Ниже приведен краткий пример.

 

Пример 11:

SELECT d.department_id, department_name, jh.job_id, employee_id

FROM departments d

CROSS JOIN job_history jh

 

Рисунок №59 - Результат выборки из примера №11.

 

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

 

Операция UNION

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

Формируя запросы? содержащие конструкцию UNION следуют руководствоваться следующим соображениями:

- Все запросы, результаты которых объединяются, должны иметь одинаковое количество столбцов в списке выборки, иначе СУБД выдаст ошибку, не выполнив запрос.

- Заголовки для столбцов результирующего запроса берутся только из формулировки первого запроса.

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

- В запросах с конструкцией UNION в отличие от обычных запросов применяется использование опции DISTINCT, а не ALL. Из-за этого иногда происходит путаница, т.к. DISTINCT удаляет все повторяющиеся строки данных в результирующем запросе.

 

Пример 12:

SELECT department_name

FROM departments

union select job_id

from employees

 

Рисунок №60 - Результат выборки из примера №12.

 

Однако, заметим в результирующей таблице присутствует только одна запись с фамилией Иванов, т.к. при применении операции UNION были удалены все повторяющиеся строки. Чтобы вывести все строки, добавим ключевое слово ALL после ключевого слова UNION.

 

Пример 13:

Рисунок №61 - Результат выборки из примера №13.

 

3. Задания для самостоятельной работы

Для проектируемой базы данных создайте соединения следующих типов:

- Соединение с конструкцией JOIN;

- Соединение с конструкцией INNER JOIN;

- Соединение с помощью конструкции OUTER JOIN;

- Соединение с помощью конструкции FULL JOIN;

- Соединение с помощью конструкции CROSS JOIN;

- С помощью операции UNION.

 

4 Контрольные вопросы

1. Дайте определение соединению.

2. Перечислите основные конструкции для формирования соединений.

3. Приведите пример использования конструкции JOIN.

4. Приведите пример использования конструкции INNER JOIN.

5. Чем отличаются результаты выполнения соединений конструкций JOIN и INNER JOIN?

6. Перечислите общие свойства конструкции INNER JOIN И конструкции WHERE.

7. Дайте определение связующей таблице?

8. Для чего и в каком случае используются связующие таблицы?

9. Приведите пример соединения с использованием конструкции OUTER JOIN.

10. Приведите пример соединения с использованием конструкции FULL JOIN.

11. Приведите пример соединения с использованием конструкции CROSS JOIN.

12. Приведите структуру и синтаксис операции UNION.

 

Лабораторная работа №6

Представления в Oracle.

 

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

1.1 Цель работы

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

 

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

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

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

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

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

 

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

 

Представление – виртуальный объект в базе данных (виртуальными их можно считать, лишь в определённом смысле).

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

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

- Обеспечить доступ пользователей ко всем необходимым данным и вместе с тем запретить доступ к некоторым столбцам, к которым доступ обычных пользователей нежелателен.

- Предусмотреть в базе дополнительные средства индексации, повышающие производительность всей базы.

В действительности же представление – это не что иное, как хранимый запрос. Удобство состоит в том, что они обеспечивают выборку данных из одной или нескольких таблиц, так же обеспечивают согласование данных.

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

Преимущества представлений

Использование представлений в базах данных различных типов может оказаться полезным в самых разнообразных ситуациях. В базах данных на персональных компь­ютерах представления применяются для удобства и позволяют упрощать запросы к базе данных В промышленных базах данных представления играют главную роль в создании собственной структуры базы данных для каждого пользователя и обеспечении ее безопасности. Основные преимущества представлений перечислены ниже:

-Безопасность: каждому пользователю можно разрешить доступ к небольшому числу представлений, содержащих только ту информацию, которую ему позволено знать Таким образом можно осуществить ограничение доступа пользовате­лей к хранимой информацииПростота запросов С помощью представления можно извлечь данные из нескольких таблиц и представить их как одну таблицу, превращая тем самым запрос ко многим таблицам в однотабличный запрос к представлению

-Простота структуры: с помощью представлений для каждого пользователя мож­но создать собственную "структуру" базы данных, определив ее как множество доступных пользователю виртуальных таблиц

-Защита от изменении: представление может возвращать непротиворечивый и неизменный образ структуры базы данных, даже если исходные таблицы разде­ляются, реструктурируются или переименовываются

-Целостность данных: если доступ к данным или ввод данных осуществляется с помощью представления, СУБД может автоматически проверять, выполняются ли определенные условия целостности

Недостатки представлений

Наряду с перечисленными выше преимуществами, представления обладают и двумя существенными недостатками:

- Производительность: представление создает лишь видимость существования соответствующей таблицы, и СУБД приходится преобразовывать запрос к представлению в запрос к исходным таблицам Если представление отображает многотабличный запрос, то простой запрос к представлению становится сложным объединением и на его выполнение может потребоваться много времени.

- Ограничения на обновление: когда пользователь пытается обновить строки представления, СУБД должна установить их соответствие строкам исходных таблиц, а также обновить последние Это возможно только для простых представлений, сложные представления обновлять нельзя, они доступны только для выборки.

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

 

Простые представления

Синтаксическая структура оператора – комбинация рассматриваемых ранее операторов CREATE, а так же оператора SELECT.

CREATE VIEW <название_представления>

AS

SELECT <описание_оператора…>

FROM <название таблицы>

 

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

Пример 1:

 

create view pr1

as

select first_name, last_name

from employees

 

 

Рисунок №62 – Результат создания представления.

 

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

 

select *

from pr1

 

Результат будет следующим:

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

 

 

Данный запрос был аналогичен запросу:

 

select first_name, last_name

from employees

 

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

При необходимости в инструкции CREATE VIEW можно задать имя для каждого столбца создаваемого представления Если указывается список имен столбцов, то он должен содержать столько элементов, сколько столбцов содержится в запросе Обратите внимание на то, что задаются только имена столбцов, тип данных, длина и другие характеристики берутся из определения столбца в исходной таблице Если список имен столбцов в инструкции CREATE VIEW отсутствует, каждый столбец пред­ставления получает имя соответствующего столбца запроса Если в запрос входят вычисляемые столбцы или два столбца с одинаковыми именами, то без такого списка обойтись невозможно