Результирующее множество должно быть таким:

ENAME

CLARK

KING

MILLER

NEW YORK NEW YORK NEW YORK

Решение

Для получения необходимого результирующего множества используйте объединение таблиц в конструкции FROM:

1 select e.ename, d.loc

2 from emp e, dept d

3 where e.deptno = 10

4 and d.deptno = e.deptno

Обсуждение

Если мы посмотрим на данные таблицы DEPT:

select * from dept

DEPTNO DNAME LOC

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

NEW YORK DALLAS CHICAGO BOSTON

то увидим, что 10-й отдел находится в Нью-Йорке. Таким образом, понятно, что включение в результирующее множество служащих не из Нью-Йорка является ошибочным. Количество строк, возвращаемое неверным запросом, является следствием кардинальности двух таблиц конструкции FROM. В исходном запросе фильтр по выбору 10-го отдела, примененный к таблице EMP, обеспечит возвращение трех строк.

Поскольку для DEPT фильтра нет, возвращаются все четыре строки DEPT. Умножая три на четыре, получаем двенадцать; таким образом, неправильный запрос возвращает двенадцать строк. Обычно, чтобы не происходило прямого (декартова) произведения, применяется правило n-1, где n представляет количество таблиц в FROM, а n-1 - минимальное число объединений, необходимое во избежание прямого произведения. В зависимости от того, что представляют собой колонки и ключи в вашей таблице, вероятно, вам понадобится более, чем n-1 объединений, но при написании запроса можно начать с n-1 объединения.

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

Осуществление объединений при использовании агрегатных функций

Задача

Необходимо осуществить агрегацию, но запрос обращен к нескольким таблицам. Требуется обеспечить, что объединения не нарушат агрегацию. Например, стоит задача найти сумму заработных плат служащих 10-го отдела, а также сумму их премий. Некоторые служащие получили не одну премию, и объединение таблиц EMP и EMP BONUS приводит к тому, что агрегатная функция SUM возвращает неверные значения. Для обсуждаемой задачи таблица EMP BONUS содержит следующие данные:

select * from emp bonus

EMPNO RECEIVED TYPE

7934 17-MAR-2005 1

7934 15-FEB-2005 2

7839 15-FEB-2005 3

7782 15-FEB-2005 1

Теперь рассмотрим запрос, возвращающий зарплаты и премии всех служащих 10-го отдела. Столбец BONUS.TYPE определяет размер премии. Премия 1-го типа составляет 10% заработной платы служащего, премия 2-го типа - 20%, и 3-го типа - 30%.

select e.empno, e.ename, e.sal, e.deptno,

e.sal*case when eb.type = 1 then .1

when eb.type = 2 then .2 else .3 end as bonus from emp e, emp bonus eb where e.empno = eb.empno and e.deptno = 10

До сих пор все идет хорошо. Однако проблемы начинаются при попытке объединения с таблицей EMP BONUS для суммирования премий:

select deptno,

sum(sal) as total sal,

sum(bonus) as total bonus from ( select e.empno,

e.ename,

e.sal,

e.deptno,

e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from emp e, emp bonus eb where e.empno = eb.empno and e.deptno = 10 ) x

group by deptno

DEPTNO TOTAL SAL TOTAL BONUS

10050

2135

В столбце TOTAL BONUS (сумма премий) получаем верное значение, тогда как значение столбца TOTAL SAL (сумма заработных плат) ошибочное. Сумма всех заработных плат 10-го отдела составляет 8750, как показывает следующий запрос:

select sum(sal) from emp where deptno=10

SUM(SAL)

8750

Почему в столбце TOTALSAL получено неверное значение? Причина состоит в дублировании строк, возникшем в результате объединения. Рассмотрим следующий запрос, который осуществляет объединение

таблиц EMP и EMP BONUS: