Лекция 5: Выборка данных. Общее построение предложения SELECT и фразы FROM и WHERE

Кванторы ANY и ALL при сравнении с элементами множества значений

Сравнение в условном выражении значения с множеством значений синтаксически невозможно, однако простановка кванторов ANY и ALL перед указанием множества такую возможность открывает. Как и в случае с оператором IN, множество может быть перечислено явно, а может выбираться из БД подзапросом; точно так же допустимо сравнение списков, а не отдельных значений.

Квантор ALL в операторе сравнения приведет к истинному результату, если значение справа от знака сравнения истинно сравнивается со всеми элементами множества.

Примеры:

"Выдать сотрудников, у которых зарплата не 1000 и не 1500":

SELECT ename 
FROM   emp
WHERE  sal <> ALL ( 1000, 1500 )
;

Все участники такого сравнения, как и в случае с оператором IN, могут быть составными выражениями.

"Выдать самых старых сотрудников":

SELECT ename 
FROM   emp
WHERE  hiredate <= ALL ( SELECT hiredate FROM emp )
;

Выражение

E <= ALL ( S )

равносильно

( E <= v1 ) AND ( E <= v2 ) AND ... AND ( E <= vn ) AND TRUE

Упражнение. Что будет:

• в результате, если подзапрос не вернет данных?
• если одно из vi будет NULL?
• если E будет NULL?

Квантор ANY в операторе сравнения приведет к истинному результату, если значение справа от знака сравнения истинно сравнивается хотя бы с одним элементом множества.

Примеры:

"Выдать сотрудников, у которых зарплата 1000 или 1500":

SELECT ename 
FROM   emp
WHERE  sal = ANY ( 1000, 1500 )
;

"Выдать сотрудников, нанятых позже кого-нибудь из отдела 20":

SELECT ename 
FROM   emp
WHERE  hiredate > ANY ( SELECT hiredate FROM emp WHERE deptno = 20 )
;

(Выдаст частью и самих сотрудников отдела 20).

Выражение

  E > ANY ( S )

равносильно

  ( E > v1 ) OR ( E > v2 ) OR ... OR ( E > vn ) OR FALSE

Упражнение. Сформулируйте последствия наличия подобной равносильной формулировки.

Сравнения с применением кванторов ANY и ALL добавляют программисту удобств в формулировании условных выражений, но теоретически привносят в SQL избыточность. Обратите внимание, что = ANY равносильно IN, а <> ALL равносильно NOT IN, но иногда один из этих двух видов оформления лучше передает смысл запроса. Сравните, например, две равносильных (с точностью до плана выполнения) формулировки запроса на выдачу наиболее высокооплачиваемых сотрудников в каждом отделе:

SELECT ename, sal, deptno 
FROM   emp
WHERE  ( sal, deptno ) IN ( SELECT   MAX ( sal ), deptno 
                            FROM     emp
                            GROUP BY deptno )
;
SELECT ename, sal, deptno 
FROM   emp
WHERE  ( sal, deptno ) = ANY ( SELECT   MAX ( sal ), deptno 
                               FROM     emp 
                               GROUP BY deptno )
;

Для лучшего восприятия текста запроса программистом, для которого английский язык — неродной, ключевому слову ANY как квантору дан синтаксический синоним: слово SOME. Например, = ANY — то же самое, что = SOME:

SELECT ename, sal, deptno 
FROM   emp
WHERE  ( sal, deptno ) = SOME ( SELECT   MAX ( sal ), deptno 
                                FROM     emp 
                                GROUP BY deptno )
;

Условный оператор EXISTS

Оператор EXISTS применяется к (произвольному) подзапросу с целью определить, возвращает ли в текущем состоянии БД этот подзапрос по меньшей мере одну строку.

Пример. Пусть надо "выдать отделы, в которых есть сотрудники":

SELECT dname 
FROM   dept
WHERE  deptno IN ( SELECT deptno FROM emp )
;
То же самое на SQL можно спросить иначе:
SELECT dname 
FROM   dept
WHERE  EXISTS ( SELECT * FROM emp WHERE deptno = dept.deptno )
;

Это очередной пример избыточности SQL: запросы с IN ( SELECT … ) и с EXISTS часто взаимозаменяемы.

Выражение для EXISTS, иходя из своего смысла, всегда возвращает только TRUE или FALSE и никогда не приведет к NULL.

В последнем примере подзапрос обладает одной особенностью. Он связанный ("коррелированный"), то есть содержит обращение к значению, взятому из охватывающего запроса (DEPT.DEPTNO). От этого схема выполнения подзапроса на каждой строке таблицы DEPT будет

(1) SELECT * FROM emp WHERE deptno = 10
(2) SELECT * FROM emp WHERE deptno = 20
...

Это может вызвать подозрения в чудовищной неэффективности оператора EXISTS, когда он применен к связанному подзапросу. Хотя тема оптимизации запросов не входит в круг задач настоящего материала, нелишне заметить, что СУБД не обязана фактически выполнять все новые подзапросы при выборе очередной строки из DEPT с такой прямолинейностью. Оптимизатор запросов Oracle достаточно разумен, чтобы суметь предложить нередко менее затратную технику обработки. В этом случае он может переформулировать запрос, заменив EXISTS на IN (в то же время в конкретных обстоятельствах подобная замена не всегда приведет к ускорению вычислений).

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

Псевдостолбец ROWNUM и особенности его использования

Выполнение фразы WHERE может сопровождаться порождением значений для "псевдостолбца" ROWNUM. Эта фактически системная функция без параметров выдает номер строки в результате того SQL-запроса, где он фигурирует.

Пример:

SQL> SELECT ROWNUM, ename FROM emp WHERE sal > 1500;
    ROWNUM ENAME
---------- ----------
         1 ALLEN
         2 JONES
         3 BLAKE
         4 CLARK
         5 SCOTT
         6 KING
         7 FORD

В логической последовательности обработки предложения SELECT очередное значение для ROWNUM порождается СУБД при отборе очередной строки фразой WHERE и становится доступным для всех последующих шагов обработки. Поэтому на ROWNUM можно сослаться в самой фразе WHERE и всех далее исполняемых, например:

SELECT ROWNUM, ename, sal FROM emp WHERE ROWNUM <= 4;

Причем несмотря на то, что формально допустимы любые сравнения, смысл приобретают лишь сравнения вида

ROWNUM = 1
ROWNUM < n
ROWNUM <= n

Еще одно следствие состоит в том, что наличие в окончательном ответе значений ROWNUM не предполагает автоматического перечисления их в каком-нибудь порядке. Последний запрос выдаст не более четырех (формально произвольных) строк о сотрудниках с указанием зарплаты. Повторение этого запроса теоретически может вернуть те же строки, но в ином порядке. К тому же порядок можно сбить искусственно:

SQL> SELECT ROWNUM, ename, sal FROM emp WHERE ROWNUM <= 4
  2  ORDER BY sal;
    ROWNUM ENAME             SAL
---------- ---------- ----------
         1 SMITH             800
         3 WARD             1250
         2 ALLEN            1600
         4 JONES            2975

Мы не получим перечисления четырех наиболее оплачиваемых сотрудников.

Псевдостолбец ROWNUM используется в Oracle не для моделирования данных приложения, а в технических целях при составлении запросов к БД. Например, он находит применение в запросах типа "первая N-ка", "выдать первые N строк" по заданному критерию. Другой пример — это добавление в условное выражение формулировки AND ROWNUM <= 1, используемое для искусственного сужения объема выдачи до максимума одной строки. О применении этого метода в построении скалярных подзапросов говорилось выше.