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

Условный оператор IN с явно перечисляемым множеством

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

Пример:

SELECT ename FROM emp WHERE job IN ( 'MANAGER', 'ANALYST' ); 

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

Выражение

  E1 IN ( E2, E3, E4, … )

равносильно

  ( E1 = E2 ) OR ( E1 = E3 ) OR ( E1 = E4 ) OR …

Следствия:

1. Оператор действует на значениях всех типов, допускающих сравнения на равенство (числовых, символьных, временных, в некоторых случаях объектных).
2. Если E1 будет NULL, результат будет NULL.

Оператор IN технологически подобен BETWEEN: он ничего нового в SQL содержательно не привносит, имеет те же общие выгоды перед равносильной более традиционной формулировкой и равным образом требует осторожности при обращении с отсутствующими значениями.

Выражение

E1 NOT IN ( E2, E3, E4, … )

равносильно

  NOT ( E1 IN ( E2, E3, E4, … ) )

равносильно

  ( E1 <> E2 ) AND ( E1 <> E3 ) AND ( E1 <> E4 ) AND …

Элементами сравнения могут выступать списки значений:

SELECT ename FROM emp 
WHERE  ( job, sal ) IN ( ( 'ANALYST', 3000 ), ( 'MANAGER', sal ) )
;

(Аналитики с зарплатами 3000 и все менеджеры).

Еще пример:

SELECT 'ok' FROM dual
WHERE  ( 1, 2, 3 ) IN ( ( 1, 2, 3 ), ( 5, 6, 7 ) )
;

Условный оператор IN с множеством, получаемым из БД

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

"Выдать сотрудников, работающих в Нью-Йорке или Бостоне":

SELECT ename FROM emp WHERE deptno IN ( 10, 40 );

Однако если мы не помним номера отделов Нью-Йорке и Бостоне, запрос естественно сформулировать иначе:

SELECT ename 
FROM   emp 
WHERE  deptno IN ( SELECT deptno
                   FROM   dept
                   WHERE  loc IN ( 'NEW YORK', 'BOSTON' )
                 )
;

"Выдать сотрудников, имеющих те же должности и руководство, что у работающих в Далласе":

SELECT ename 
FROM   emp
WHERE  ( job, mgr ) IN ( SELECT job, mgr
                         FROM   emp
                         WHERE  deptno IN ( SELECT deptno
                                            FROM   dept
                                            WHERE  loc = 'DALLAS'
                       )                  ) 
;

Выражение

  C IN ( S )

равносильно

  ( C = v1 ) OR ( C = v2 ) OR ... OR ( C = vn ) OR FALSE

Следствия:

• Если подзапрос для множества значений не выдает результата, условие отбора становится FALSE.
• Если C будет NULL, а подзапрос возвращает хотя бы одно значение, результат будет NULL.

Выражение

  C NOT IN ( S )

равносильно

  ( C <> v1 ) AND ( C <> v2 ) AND ... AND ( C <> vn ) AND TRUE

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