Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7046 / 737 | Оценка: 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'));

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