IN, NOT IN

Вхождение в набор данных. IN

Условие IN позволяет ответить на следующий вопрос: "Входит ли ( IN ) значение в заданный набор данных?".

Следующий пример вернет все блюда, рейтинг которых равен 320 либо 270:

select d.*
from dishes d
where d.rating IN (320, 270)

NAME	PRICE	RATING
Макароны с сыром	20.56	320
Чай с лимоном	1.34	270
Свиная отбивная	30.5	320

Использовать можно любые типы, не только числа:

select d.*
from dishes d
where d.name IN
    ('Макароны с сыром', 'Овощной салат', 'Борщ')

NAME	PRICE	RATING
Макароны с сыром	20.56	320
Борщ	10	130
Овощной салат	5.7	-

Следует помнить, что при сравнении строк учитывается регистр, т.е. cледующий запрос:

select d.*
from dishes d
where d.name IN
    ('Макароны с сыром', 'ОВОЩНОЙ салат', 'БОРЩ')

Не вернет строки с овощным салатом и борщом:

NAME	PRICE	RATING
Макароны с сыром	20.56	320

Можно попробовать поправить ситуацию и воспользоваться уже знакомой функцией UPPER. Напомним, что эта функция приводит строку к верхнему регистру:

select d.*
from dishes d
where UPPER(d.name) IN
    ('Макароны с сыром', 'ОВОЩНОЙ салат', 'БОРЩ')

NAME	PRICE	RATING
Борщ	10	130

Итак, следующим запросом мы фактически сказали БД: "Покажи нам все строки из таблицы dishes, в которых наименование, написанное большими буквами, будет равно либо "Макароны с сыром", либо "ОВОЩНОЙ салат", либо "БОРЩ".

Как можно получить все три интересующих нас блюда, не переживая за то, что регистры строк (а здесь достаточно несовпадения и хотя бы в одном символе) в таблице dishes не совпадут с регистрами строк, которые мы перечисляем в выражении IN?

Ответ прост - привести к верхнему/нижнему регистру как строки в таблице, так и строки в выражении IN.

Следующий запрос выдаст список всех интересующих нас блюд:

select d.*
from dishes d
where UPPER(d.name) IN (
    upper('Макароны с сыром'),
    upper('ОВОЩНОЙ салат'),
    upper('БОРЩ')
)

NAME	PRICE	RATING
Макароны с сыром	20.56	320
Борщ	10	130
Овощной салат	5.7	-

Отсутствие в наборе данных. NOT IN

Условие NOT IN выполняет функцию, противоположную выражению IN: убедиться, что значение не входит в указанный набор данных.

Пример: нам требуется получить список блюд, за исключением чая с молоком и овощного салата:

select *
from dishes
where name not in ('Овощной салат', 'Чай с молоком')

Получим следующий результат:

NAME	PRICE	RATING
Макароны с сыром	20.56	320
Борщ	10	130
Чай с лимоном	1.34	270
Свиная отбивная	30.5	320

Для понимания того, как работает конструкция NOT IN, лучше рассматривать приведенный пример как следующий, эквивалентный запрос:

select *
from dishes
where name <> 'Овощной салат'
and name <> 'Чай с молоком'

При использовании NOT IN, проверяемое значение будет поочередно сравнено с каждым из значений, перечисленных в скобках после NOT IN, и если хотя бы одно сравнение не будет истинным, то все условие будет считаться ложным.

WARNING

Если в списке значений NOT IN будет присутствовать хотя бы одно NULL-значение, то условие будет ложным для всех обрабатываемых строк, даже тех, где проверяемое значение является NULL.

Рассмотрим это на примере.

Предположим, мы хотим получить список блюд, рейтинг которых не 320 и не NULL. Для этого мы написали следующий запрос:

select *
from dishes
where rating not in (320, null)

no data found

Рассмотрим, как работает эквивалентный запрос:

select *
from dishes
where rating <> 320
and rating <> null

Теперь все должно быть более понятным. Причина кроется в выражении rating <> null. Сравнение с NULL всегда дает ложный результат, а так как используется логическое И(and), то и результат всего выражения WHERE будет ложным.

Поэтому, используя NOT IN, всегда следует убедиться в отсутствии null-значений.