Orasql.ru

Appearance

IN, NOT IN ​

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

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

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

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

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

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

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

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

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

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

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

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

Почему в выборку не попали макароны с сыром и овощной салат? Ответ прост

  • строка "МАКАРОНЫ С СЫРОМ" не идентична строке "Макароны с сыром", как и строка "ОВОЩНОЙ САЛАТ" не идентична строке "ОВОЩНОЙ салат".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат получился немного не таким, как хотелось бы. Чтобы понять, почему не было получено никаких данных, рассмотрим, как работает данный запрос:

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

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

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