Соединения таблиц в предложении SELECT

Поясняющие примеры соединений

Здесь приводятся примеры разных видов соединений по критерию полноты. В данном случае речь идет о "самосоединениях", то есть соединениях по значениям столбцов фактически одной и той же таблицы-источника, указанной во фразе FROM более одного раза. Самосоединения позволяют взглянуть на одну таблицу с разных смысловых точек зрения. В нашем случае таблица EMP воспринимается (а) как таблица с данными о подчиненных и (б) как таблица с данными о начальниках.

Пример закрытого соединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr = chief.empno
;

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

Пример полуоткрытого "влево" соединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr = chief.empno ( + )
;

Пример "левого" антисоединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr = chief.empno ( + ) AND chief.ename IS NULL
;

Пример полуоткрытого "вправо" соединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr ( + ) = chief.empno
;

Пример "правого" антисоединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr ( + ) = chief.empno AND subordinate.ename IS NULL
;

Упражнение. Проверьте результат выполнения приведенных соединений.

До введения в версии Oracle 9 специального синтаксиса непосредственная запись открытого соединения была невозможна (иными словами, приписать '( + )' к обоим соединяемым столбцам одновременно не разрешается) и ее приходилось моделировать объединением результатов двух полуоткрытых соединений.

Приведенные выше формулировки полуоткрытых и антисоединений придают запросу некоторую краткость, но содержательно не сообщают ничего нового диалекту SQL в Oracle (и формулировкам стандарта SQL, о которых речь пойдет далее). Здесь в очередной раз проявляет себя избыточность SQL в Oracle и в стандарте. Так, левое антисоединение может быть сформулировано без дополнительного синтаксиса, к примеру, следующим образом:

SELECT subordinate.ename, 'в подчинении у', NULL ename
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.mgr = chief.empno
     )
;

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

SELECT subordinate.ename, 'в подчинении у', chief.empno
FROM   emp subordinate, emp chief
WHERE  subordinate.mgr = chief.empno
UNION ALL
SELECT subordinate.ename, 'в подчинении у', NULL
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.mgr = chief.empno
     )
;

Аналогичная пара для правого анти- и открытого соединений может быть выражена так:

SELECT NULL ename, 'в подчинении у', subordinate.ename
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.empno = chief.mgr
     )
;
SELECT chief.ename, 'в подчинении у', subordinate.ename
FROM   emp subordinate, emp chief
WHERE  subordinate.empno = chief.mgr
UNION ALL
SELECT NULL ename, 'в подчинении у', subordinate.ename
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.empno = chief.mgr
     )
;

И это не единственные примеры альтернативных формулировок. Примечательно, что часто Oracle технически обрабатывает разные формулировки по-разному.

Упражнение. На основе приведенных формулировок постройте запрос на полное самосоединение с информацией о подчиненности сотрудников.

Предостерегающий и типовой примеры полуоткрытых соединений

Использование полуоткрытых соединений плодотворно для программиста, но их составление, как и многое в SQL, требует внимания. Ниже в виде упражнений рассматривается пример неумышленно неправильного составления запроса и пример типового использования полуоткрытого соединения.

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

SELECT   dname, COUNT ( * ) emp_count, SUM ( sal ) tot_sal
FROM     emp, dept 
WHERE    emp.deptno ( + ) = dept.deptno
GROUP BY dname
;

Предложите изменение запроса, позволяющее получить правильный ответ.

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

SELECT 
   dname
 , ( SELECT COUNT ( * ) FROM emp e WHERE e.deptno = d.deptno ) emp_count
 , ( SELECT SUM ( sal ) FROM emp e WHERE e.deptno = d.deptno ) tot_sal
FROM dept d
;

Ответы (возможно) будут разниться только техникой обработки, однако тема оптимизации запросов здесь не рассматривается. Если ее не касаться, выбор конкретной формулировки запроса в подобных случаях — дело вкуса программиста и удобства восприятия.

Упражнение 2. Упражнение показывает популярный случай употребления полуоткрытого соединения при построении отчетов. Предположим, нужно составить отчет о том, сколько сотрудников нанималось на работу за определенный период времени. Подготовим рабочую таблицу PIVOT_YEARS:

CREATE TABLE pivot_years 
AS 
  SELECT ( ROWNUM - 1 ) + 1980 AS year 
  FROM   emp 
  WHERE  ( ROWNUM - 1 ) <= 10
;

Она плотно заполнена "значениями года", от 1980 до 1990. Тогда следующий запрос выдаст сведения о количестве сотрудников, приходивших на работу в указанных в PIVOT_YEARS годах:

SELECT   p.year, COUNT ( e.empno ) 
FROM     pivot_years p, emp e 
WHERE    p.year = EXTRACT ( YEAR FROM e.hiredate ( + ) ) 
GROUP BY p.year 
ORDER BY p.year
;

Pivot table — это своего рода "реперная", или "опорная", "градуировочная", "калибровочная" таблица, помогающая анализировать данные. Ее можно сделать универсальной, если заполнить числами от 1 до n. Последний SELECT в этом случае придется слегка поправить.

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

(Опорная таблица не обязана быть статичной. Аппарат табличных функций в PL/SQL позволяет построить функцию, способную порождать таблицу из n строк со значениями от 1 до n динамически).