II.2.6. Внешние соединения

Использование внешнего соединения для таблицы EMPLOYEE. Внешнее соединение задается опреатором (+) (символ плюс, заключенный в круглые скобки) и позволяет выбрать строки одной таблицы, для которых в другой таблице нет строк, соответствующих условию соединения. Оператор (+) помещается на той стороне соединяющего условия, которая соответствует таблице с отсутствующими данными. Он предписывает Oracle в случае отсутствия строк, удовлетворяющих условию соединения, возвращать NULL (неопределенное значение) для всех выражений списка выборки, которые содержат имена столбцов таблицы с отсутствующими данными.

Q2_23 Получить перечень отделов с их названиями, фамилии сотрудников и идентификаторы их работы. Список должен включать и те отделы, в которых нет сотрудников.

demo@10g> select d.DEPARTMENT_ID,last_name,job_id,name

from EMPLOYEE e,DEPARTMENT d

where e.DEPARTMENT_ID(+)=d.DEPARTMENT_ID

order by d.DEPARTMENT_ID;

Отделы 34, 40 и их названия представлены, а сотрудников этих отделах в таблице EMPLOYEE нет, как и самих номеров отделов.

Q2_24 В синтаксисе стандарта ANSI SQL, определенного для SQL/92 (начиная с Oracle9i) можно указывать в инструкции FROM тип объ­единения вместе с ключевыми словами LEFT JOIN.

Выполним запрос Q2_16 в варианте внешнего объединения:

demo@10g> select last_name,job_id,name,department_id

from DEPARTMENT left join EMPLOYEE

using (DEPARTMENT_ID)

where department_id in (10,34,40);

Поменяем местами таблицы DEPARTMENT, EMPLOYEE в последнем запросе:

demo@10g> select last_name,job_id,name,department_id

from EMPLOYEE left join DEPARTMENT

using (DEPARTMENT_ID)

where department_id in (10,34,40);

Внешнее объединение не «сработало». Слева надо вставлять название той таблицы, в которой есть информация (отделы 34, 40). Если мы заменим в последнем запросе «left join» на «right join» внешнее объединение отработает верно:

demo@10g> select last_name,job_id,name,department_id

from EMPLOYEE right join DEPARTMENT

using (DEPARTMENT_ID)

where department_id in (10,34,40);

Если теперь предположить, что не только есть отделы, в которых нет служащих, но и есть служащие, не прикрепленные ни к какому отделу, то запрос, который бы показал и отделы, в которых нет служащих, и служащих, не прикрепленных ни к какому отделу, должен использовать «full outer join».

Q2_25 Комплексный пример использования объединений (внутренних и внешних).

Извлечь информацию: фамилия служащего, его должность, фамилия его руководителя, зарплата, категория зарплаты, название отдела

demo@10g> select e.last_name name,function job,m.last_name boss,e.salary sal,s.

grade_id grade,d.name department

from EMPLOYEE e,EMPLOYEE m,SALARY_GRADE s,DEPARTMENT d,JOB

where e.manager_id = m.EMPLOYEE_ID(+)

and e.salary between s.lower_bound and s.upper_bound

and e.DEPARTMENT_ID = d.DEPARTMENT_ID

and e.job_id=job.job_id

order by s.grade_id desc,e.salary desc,d.DEPARTMENT_ID,e.last_name