Некоторые примеры составления запросов. Дополнительные сведения об аналитических функциях

Декартово произведение

Декартово произведение — одна из отправных операций над отношениями в реляционной модели. В SQL возникает или при отсутствии фразы для отбора WHERE вообще или же при отсутствии во фразе WHERE сравнения полей строк таблиц друг с другом.

Пример:

SELECT ename, empno, dname
FROM   emp, dept
;

Чаще всего (но не всегда) не имеет прикладного смысла и по этой причине является ошибкой приложения. Сложность в том, что СУБД не считает декартово произведение ошибкой и никогда программисту о его прикладной ошибке не сообщит. (Определенная подвижка произошла в Oracle версии 10, где администратор БД получил возможность анализировать группы запросов, в том числе на предмет наличия декартовых произведений.)

Отличительная особенность декартова произведения — вероятный большой объем результата. Однако он может складываться дополнительной обработкой. Сравните, например, выдачу названий отделов и числа работающих сотрудников (при том, что один из запросов намеренно построен бессодержательно):

SELECT   dname, COUNT ( empno ) 
FROM     emp, dept
WHERE    dept.deptno = emp.deptno ( + )
GROUP BY dname
;
SELECT   dname, COUNT ( empno ) 
FROM     emp, dept
GROUP BY dname
;

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

Примечательно, что при систематическом использовании программистом синтаксиса SQL-92 для записи соединения (с версии 9 Oracle) появление подобных просчетов невозможно, так как этот синтаксис требует указания сравнения значений разных столбцов друг с другом (если, конечно, это не NATURAL INNER JOIN). На следующий запрос Oracle ответит сообщением о синтаксической ошибке:

SELECT dname, COUNT ( empno )
FROM   emp RIGHT JOIN dept
GROUP BY dname
;

А настоять на декартовом соединении, когда оно-таки нужно, позволит специальная операция CROSS JOIN. Следующий запрос выполнится без ошибки:

SELECT dname, COUNT ( empno )
FROM   emp CROSS JOIN dept
GROUP BY dname
;

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

Ловушка условия с отрицанием NOT

Понимание NOT в SQL не всегда интуитивно с точки зрения обыденной логики. Далее приводится пример расхожей ошибки неопытного программиста.

"Выдать отделы, где есть клерки":

SELECT DISTINCT
  deptno 
FROM
  emp 
WHERE
  job = 'CLERK'
;

"Выдать отделы, где нет клерков" (неправильно):

SELECT DISTINCT
  deptno 
FROM
  emp 
WHERE
  NOT job = 'CLERK'
;

"Выдать отделы, где нет клерков" (почти правильно):

SELECT DISTINCT
  deptno 
FROM
  emp
WHERE
  deptno NOT IN ( SELECT deptno FROM emp WHERE job = 'CLERK' )
;

"Выдать отделы, где нет клерков" (совсем правильно):

SELECT
  deptno 
FROM
  dept
WHERE
  deptno NOT IN ( SELECT deptno FROM emp WHERE job = 'CLERK' )
;

Обратите внимание, что от простого обращения запроса запись на SQL существенно преобразилась и даже поменялась таблица перебора с EMP на DEPT!

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