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

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

< Лекция 13 || Лекция 14: 12345

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

Декартово произведение — одна из отправных операций над отношениями в реляционной модели. В 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. Предложите другую формулировку прямого запроса, более близкую по духу обратному.

< Лекция 13 || Лекция 14: 12345
Ярослав Прозоров
Ярослав Прозоров

В лекции № 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'));

И сколько строк он все таки вернет