Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7046 / 737 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 8:

Соединения таблиц в предложении 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 динамически).

Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001