Поэтому я указал NULL, чтобы обратить ваше внимание на объединение в подзапросе, а не на элементы списка SELECT.

Извлечение из таблицы строк, для которых нет соответствия в другой таблице

Задача

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

DEPTNO DNAME LOC

40 OPERATIONS BOSTON

Поиск отдела, в котором работает каждый из служащих, требует проведения операции эквиобъединения таблиц EMP и DEPT по столбцу DEPTNO. Столбец DEPTNO представляет общее значение для обеих таблиц. К сожалению, эквиобъединение не даст информацию о том, в каком отделе нет служащих, поскольку в результате этой операции будут возвращены все строки, удовлетворяющие условию объединения. А нам нужны только те строки таблицы DEPT, которые не удовлетворяют условию объединения.

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

Решение

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

DB2. MySQL, PostgreSQL, SQL Server

Используйте внешнее объединение и фильтр для значений NULL (ключевое слово OUTER (внешний) является необязательным):

1 select d.*

2 from dept d left outer join emp e

3 on (d.deptno = e.deptno)

4 where e.deptno is null

Oracle

Предыдущее решение подойдет пользователям, работающим с Oracle 9i Database и более поздними версиями. В качестве альтернативы может использоваться собственный синтаксис Oracle для внешнего объединения:

1 select d.*

2 from dept d, emp e

3 where d.deptno = e.deptno (+)

4 and e.deptno is null

Этот собственный синтаксис (обратите внимание на использование + в скобках) - единственный доступный в Oracle 8i Database и более ранних версиях синтаксис внешнего объединения.

Обсуждение

Данное решение обеспечивает внешнее объединение с последующим возвращением только тех строк, которые не имеют соответствия. Операции такого рода иногда называют антиобъединением (anti-join). Чтобы лучше понять принцип антиобъединения, рассмотрим результирующее множество, получаемое без фильтрации значений NULL:

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

Обратите внимание, в столбцах EMP.ENAME и EMP DEPTNO последней строки отсутствуют значения, потому что в 40-м отделе нет служащих. С помощью предиката WHERE в решении выбираются только строки со значением NULL в столбце EMP DEPTNO (таким образом, возвращаются только те строки DEPT, которым нет соответствия вEMP).

Независимое добавление объединений в запрос Задача

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

select * from emp bonus

EMPNO RECEIVED

TYPE

7369 14-MAR-2005 7900 14-MAR-2005 7788 14-MAR-2005

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

select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno

ENAME

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

DALLAS

CHICAGO

CHICAGO

DALLAS

CHICAGO

CHICAGO

NEW YORK

DALLAS

NEW YORK

CHICAGO

DALLAS

CHICAGO

DALLAS

NEW YORK

К этим результатам требуется добавить даты выдачи премии, но объединение с таблицей EMP BONUS приведет к тому, что будут возвращены только те строки, которые соответствуют служащим, получившим премию:

select e.ename, d.loc,eb.received

from emp e, dept d, emp bonus eb

where e.deptno=d.deptno

and e.empno=eb.empno