Лаборотарная работа №3 « создание запросов к одной и нескольким таблицам»

ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

I Виды соединений таблиц

Соединение таблиц (Join) позволяет объединить колонки из нескольких таблиц в одну (результирующую). Объединение происходит временное и целостность исходных таблиц не нарушается. Существует три типа соединений:

• inner join (внутреннее соединение);

• outer join (внешнее соединение);

• cross join (декартово произведение).

Внутреннее соединение (Inner join) необходимо для получения только тех строк, для которых существует взаимное соответствие записей главной таблицы и присоединяемой. Иными словами условие соединения двух таблиц должно выполняться всегда, для каждой пары записей обеих таблиц.

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

Внешнее соединение бывает 3-х видов: left (левое), right (правое) и full (полное).

В случае с левым внешним соединением (left join) из главной (левой) таблицы будут выбраны все записи, даже если в присоединяемой (правой) таблице нет совпадений.

В случае с правым внешним соединением (right join) отображает все строки, удовлетворяющие правой части условия соединения, даже если они не имеют соответствия в главной (левой) таблице.

В случае с полным внешним соединением (full join) отобразятся все строк из нескольких таблиц. Иными словами, это объединение результатов left и right join. Если строке главной таблице нет соответствующей строки в присоединяемой – она будет дополнена Null-значениями, и если сроке присоединяемой таблице нет соответствующей строки в главной – она тоже будет дополнена Null-значениями.

II Синтаксис соединений нескольких таблиц в стандарте sql/86

Исторически большинство СУБД поддерживают синтаксис соединений в соответствии со стандартом SQL/86.

Пример обычного внутреннего соединения:

SQL> SELECT c.course_name, c.period, e.student_name

2 FROM course c, enrollment e

3 WHERE c.course_name = e.course_name

4 AND c.period = e.period;

Пример обычного внешнего соединения:

SQL> SELECT c.course_name, c.period, e.student_name

2 FROM course c, enrollment e

3 WHERE c.course_name = e.course_name(+)

4 AND c.period = e.period(+);

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

III ansi-стандарт синтаксиса соединений нескольких таблиц (sql/92)

ANSI-стандарт синтаксиса простых соединений

В ANSI-стандарт синтаксиса SQL-соединений входит несколько новых ключевых слов и операторов, которые позволяют полностью описать соединение только во фразе FROM SELECT-предложения. Например, для создания внутреннего соединения между таблицами COURSE и ENROLLMENT следует написать:

FROM courses c

INNER JOIN enrollment e

После описания типа соединения описываются условия этого соединения. Условия соединения описываются в операторе ON, который является частью фразы FROM:

SELECT c.course_name, c.period,

e.student_name

FROM course c INNER JOIN enrollment e

ON c.course_name = e.course_name

AND c.period = e.period;

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

ON c.course_name = e.course_name

AND c.period = e.period;

Можно просто использовать оператор USING:

USING (course_name, period);

Оператор USING в этом примере описывает, какие строки двух таблиц должны соединяться, когда в соответствующих столбцах этих таблиц COURSE_NAME и PERIOD появляются одинаковые значения. Однако в операторе USING слегка затрагивается семантика запроса. При написании соединения с помощью оператора ON все столбцы обоих таблиц доступны. Таким образом, столбец COURSE_NAME можно выбирать из обоих таблиц:

SELECT C.COURSE_NAME, E.COURSE_NAME

Если в операторе USING использовать алиас (псевдоним, имя-заменитель) столбца, то возникнет ошибка "неверное имя столбца". При описании оператора USING ядро базы данных соединяет два столбца COURSE_NAME и в результате распознает только один такой столбец. Этот столбец невозможно связать ни с одной из соединяемых таблиц, поэтому нельзя уточнять его с помощью алиаса. Это имеет смысл, потому что по определению соединение по равенству означает, что для каждой строки, возвращаемой запросом, существует только одно значение столбца COURSE_NAME.

Внешние соединения, синтаксис ANSI

ANSI-синтаксис распознает три типа внешних соединений: левые, правые и полные внешние соединения. Левые и правые внешние соединения – это фактически одно и то же – все строки одной таблицы включены в соединение, независимо от наличия соответствующих строк в другой таблице. Эти типы соединений отличаются только порядком перечисления таблиц. Три следующих запроса, в первом из которых используется старый синтаксис, семантически одинаковы:

SELECT c.course_name, c.period,

e.student_name

FROM course c, enrollment e

WHERE c.course_name = e.course_name(+)

AND c.period = e.period(+);

SELECT c.course_name, c.period,

e.student_name

FROM course c LEFT OUTER JOIN enrollment e

ON c.course_name = e.course_name

AND c.period = e.period;

SELECT c.course_name, c.period,

e.student_name

FROM enrollment e RIGHT OUTER JOIN course c

ON c.course_name = e.course_name

AND c.period = e.period;

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

Множественные соединения

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

SELECT course_name, period, student_name,

s.grade_level

FROM course c INNER JOIN enrollment e

USING (course_name, period)

INNER JOIN student s USING (student_name);

SELECT course_name, period, student_name,

s.grade_level

FROM (course c INNER JOIN enrollment e

USING (course_name, period))

INNER JOIN student s USING (student_name);

Первый запрос соединяет COURSE с ENROLLMENT, а затем результат соединяет со STUDENT. Во втором запросе используются скобки для описания точно такого же порядка соединения.

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Проверьте, действительно ли все коды служащих в таблице ЕМР уникальны (напротив каждого табельного номера напишите – уникален он или нет).

2. На вход поступает строка в формате «nn/nn». Проверьте, что два первых и два последних символа – это действительные числа, а символ в середине – наклонная черта. Напечатайте «Yes», если это так, и «No», если не так.

3. Служащему, зачисленному на работу до 15 числа любого месяца, платят первую зарплату в последнюю пятницу этого месяца. Зачисленные 15 числа и позже получают первую зарплату в последнюю пятницу следующего месяца. Распечатайте список имен служащих, дат из зачисления на работу и дат первой выплаты. Отсортируйте данные по дате зачисления на работу.

==============================================================