select e.ename, d.loc from emp e inner join dept d

on (e.deptno = d.deptno) where e.deptno = 10

Если вы предпочитаете реализовывать логику объединения не в WHERE, а в конструкции FROM, используйте JOIN. Оба варианта допускаются стандартом ANSI и могут применяться для всех последних версий СУБД, обсуждаемых в данной книге.

Поиск одинаковых строк в двух таблицах Задача

Требуется найти общие строки в двух таблицах, но объединение возможно по нескольким столбцам. Например, рассмотрим такое представление V:

create view V as

select ename,job,sal

from emp where job = CLERK

select * from V

ENAME

SMITH ADAMS JAMES

MILLER

CLERK CLERK CLERK CLERK

1100

1300

Представление V содержит только строки для клерков. Но в него включены не все столбцы EMP. Требуется выбрать значения столбцов EMPNO, ENAME, JOB, SAL и DEPTNO для всех служащих таблицы EMP соответственно строкам представления V. Результирующее множество должно быть таким:

EMPNO ENAME

DEPTNO

7369 SMITH

7876 ADAMS

7900 JAMES

7934 MILLER

CLERK CLERK CLERK CLERK

1100

1300

Решение

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

Обсуждение

Чтобы получить требуемый результат, необходимо правильно выбирать столбцы для объединения. Это особенно важно, когда значения строк в одних столбцах могут быть одинаковыми, а в других - нет.

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

1 В SQL Server 2005 работает вариант, описанный для DB2 и PostgreSQL, т. к. в нем уже поддерживается конструкция INTERSECT. - Примеч. науч.ред.

MySQL и SQL Server1

Объедините таблицу EMP с представлением V, используя несколько условий объединения:

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

2 from emp e, V

3 where e.ename = v.ename

4 and e.job = v.job

5 and e.sal = v.sal

Или аналогичное объединение можно осуществить посредством оператора JOIN:

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

2 from emp e join V

3 on ( e.ename = v.ename

4 and e.job = v.job

5 and e.sal = v.sal )

DB2, Oracle и PostgreSQL

Решение для MySQL и SQL Server подходит и для DB2, Oracle и PostgreSQL. Его следует применять, если требуется возвратить значения представления V.

Если нет необходимости возвращать столбцы представления V, можно использовать операцию над множествами INTERSECT в сочетании спредикатом IN:

1 select empno,ename,job,sal,deptno

2 from emp

3 where (ename,job,sal) in (

4 select ename,job,sal from emp

5 intersect

6 select ename,job,sal from V

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

Задача

Требуется в одной таблице, назовем ее исходной, найти значения, которых нет в другой таблице, назовем ее целевой. Например, необходимо выяснить, каких отделов (если таковые имеются), представленных в таблице DEPT, нет в таблице EMP. В примере базы данных в таблице DEPT есть DEPTNO 40, которого нет в EMP; таким образом, результирующее множество должно быть следующим:

DEPTNO 40

Решение

Для решения этой задачи очень полезны функции, осуществляющие операцию вычитания множеств. DB2, PostgreSQL и Oracle поддерживают операции вычитания множеств. Если ваша СУБД не поддерживает таких функций, используйте подзапрос, как показано для MySQL и SQL Server.

DB2 and PostgreSQL

Используйте операцию над множествами EXCEPT (за исключением):

1 select deptno from dept

2 except

3 select deptno from emp

Oracle

Используйте операцию над множествами MINUS (минус):

1 select deptno from dept

2 minus

3 select deptno from emp

MySQL и SQL Server

Используйте подзапрос, возвращающий все значения столбца DEPT-NO таблицы EMP. Внешний запрос будет искать в таблице DEPT строки, которых нет среди строк, возвращенных подзапросом:

1 select deptno

2 from dept

3 where deptno not in (select deptno from emp)