нее объединение таблицы EMP с таблицей EMP BONUS, чтобы даже служащие, не получавшие премии, были учтены при вычислении результата. Если служащий не получал премию, для него в столбце EMP BONUS.TYPE будет возвращено значение NULL. Это важно помнить, поскольку выражение CASE в данном случае немного отличается от представленного в решении раздела Осуществление объединений при использовании агрегатных функций . Если в столбце EMP BONUS.TYPE содержится значение NULL, выражение CASE возвращает нуль, который не оказывает никакого влияния на сумму.

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

select d.deptno,

d.total sal,

sum(e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end) as total bonus

from emp e,

emp bonus eb, (

select deptno, sum(sal) as total sal from emp

where deptno = 10 group by deptno ) d

where e.deptno = d.deptno and e.empno = eb.empno group by d.deptno,d.total sal

DEPTNO TOTAL SAL TOTAL BONUS 10 8750 390

Возвращение отсутствующих данных из нескольких таблиц

Задача

Одновременно из нескольких таблиц требуется выбрать данные, отсутствующие в той или иной таблице. Чтобы возвратить строки таблицы DEPT, которых нет в таблице EMP (любой отдел, в котором нет служащих), необходимо провести внешнее объединение. Рассмотрим следующий запрос, в результате которого будут возвращены все значения столбцов DEPTNO и DNAME таблицы DEPT и имена всех служащих всех отделов (если в отделе есть служащие):

select d.deptno,d.dname,e.ename from dept d left outer join emp e on (d.deptno=e.deptno)

Последняя строка, отдел OPERATIONS (операции), возвращена, несмотря на то что в отделе нет ни одного служащего. Это является следствием левостороннего внешнего объединения таблицы DEPT с таблицей EMP. Теперь, предположим, есть служащий, не относящийся ни к одному отделу. Как получить приведенное выше результирующее множество со строкой служащего, не приписанного ни к одному отделу? Иначе говоря, в том же запросе необходимо провести внешнее объединение таблицы EMP с таблицей DEPT и таблицы DEPT с таблицей EMP. После создания нового служащего первая попытка может быть такой:

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) select 1111,YODA,JEDI,null,hiredate,sal,comm,null

from emp where ename = KING

select d.deptno,d.dname,e.ename from dept d right outer join emp e on (d.deptno=e.deptno)

DEPTNO DNAME

ENAME

10 ACCOUNTING

10 ACCOUNTING

10 ACCOUNTING

20 RESEARCH

20 RESEARCH

20 RESEARCH

20 RESEARCH

20 RESEARCH

30 SALES

30 SALES

MILLER

KING

CLARK

FORD

ADAMS

SCOTT

JONES

SMITH

JAMES

TURNER

30 SALES

30 SALES

30 SALES

30 SALES

BLAKE MARTIN

WARD ALLEN

YODA

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

DEPTNO DNAME

ENAME

10 ACCOUNTING

10 ACCOUNTING

10 ACCOUNTING

20 RESEARCH

20 RESEARCH

20 RESEARCH

20 RESEARCH

20 RESEARCH

30 SALES

30 SALES

30 SALES

30 SALES

30 SALES

30 SALES

40 OPERATIONS

CLARK KING MILLER ADAMS

FORD JONES

SCOTT

SMITH

ALLEN

BLAKE

JAMES

MARTIN

TURNER

WARD

YODA

Решение

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

DB2, MySQL, PostgreSQL, SQL Server

Чтобы вместе с совпадающими строками возвратить из обеих таблиц отсутствующие в другой таблице строки, явно используйте команду FULL OUTER JOIN (полное внешнее объединение):

1 select d.deptno,d.dname,e.ename

2 from dept d full outer join emp e

3 on (d.deptno=e.deptno)

Или объедините результаты двух разных внешних объединений:

1 select d.deptno,d.dname,e.ename

2 from dept d right outer join emp e

3 on (d.deptno=e.deptno)

4 union

5 select d.deptno,d.dname,e.ename