Специальные операторы в условиях

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

Оператор `IN`

В прошлом уроке вы выполняли задание, в котором отбирали записи по трём городам. А если городов станет больше — например шесть?

SQL
SELECT total,
       customer_id
FROM invoice
WHERE billing_city = 'Dublin'
  OR billing_city = 'London'
  OR billing_city = 'Paris'
  OR billing_city = 'Boston'
  OR billing_city = 'Berlin'
  OR billing_city = 'Stuttgart'; 

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

SQL
SELECT total,
       customer_id
FROM invoice
WHERE billing_city IN ('Dublin',
                       'London',
                       'Paris',
                       'Boston',
                       'Berlin',
                       'Stuttgart'); 

Как вы думаете, какие данные выгрузит такой запрос?

SQL
SELECT total,
       customer_id
FROM invoice
WHERE customer_id IN (3,
                      '23',
                      '36'); 

Запрос ничего не выгрузит, ведь так записывать условия нельзя. Появится сообщение об ошибке.

Условие выглядит нелогичным, но оно сработает.

Условие сработает для записей, в которых указан customer_id, равный 3. Будут выгружены поля total и customer_id.

Условие сработает для записей, в которых указан customer_id, равный 3, 23 и 36. Будут выгружены поля total и customer_id.

Тип данных в условии и в сравниваемом поле может не совпадать. Поле customer_id — числовое, и числа в этом поле будут сравниваться со всеми значениями из условия. Даже если они представлены строками.

Оператор `LIKE`

Если названия городов указаны без ошибок, отфильтровать нужные данные не так сложно. А если поле хранит адреса электронной почты, как отобрать данные по домену? Перечисление всех адресов с нужным доменом займёт много времени.

В SQL можно получить срез не только по конкретному значению, но и по шаблону. Для этого используют оператор LIKE, а с помощью знака % показывают, какую позицию в строке занимает шаблон.

Обратите внимание, что оператор LIKE ищет не слова, а символы в строке. Если шаблон короткий, результат не всегда может быть ожидаемым.

Выражение	Что попадёт	Пример кода	Результат
'text%'	Значения, которые начинаются с text	WHERE quotes LIKE 'кот%'	'кот просил еды' 'котелок не варит'
'%text'	Значения, которые заканчиваются на text	WHERE quotes LIKE '%кот'	'куда пропал кот' 'двойной апперкот'
'%text%'	Значения, в которых text занимает любую позицию	WHERE quotes LIKE '%кот%'	'кота зовут василий' 'они танцевали котильон'
'te%xt'	Значения, которые начинаются на te и заканчиваются на xt	WHERE quotes LIKE 'ко%т'	'кот играет' 'консультант'

Поиск по шаблону учитывает и регистр, поэтому условие WHERE quotes LIKE 'Кот%' найдёт строку 'Коту подарили миску', а строку 'коту подарили рыбу' оставит без внимания.

Отобрать записи, в которых, напротив, нет конкретных символов, можно конструкцией NOT LIKE. Шаблоны для поиска нужно указывать по тем же принципам, что и для оператора LIKE. Например, условие WHERE quotes NOT LIKE 'кот%' исключит строку 'кот играет с собакой'.

Таблица playlist_track содержит поле track_id, его тип данных — integer. Как вы думаете, что выгрузит такой запрос?

SQL
SELECT *
FROM playlist_track
WHERE track_id LIKE 43; 

Запрос ничего не выгрузит, ведь шаблон должен быть строкой.

Оператор LIKE можно использовать только для строк, а для чисел он не подходит.

Запрос выгрузит все записи, в которых track_id равен 43.

Как найти записи, в которых значение поля track_id равно 2436 и 5437?

SQL
SELECT *
FROM playlist_track
WHERE track_id LIKE '43'; 

SQL
SELECT *
FROM playlist_track
WHERE CAST(track_id AS varchar) LIKE '%43%'; 

Сначала нужно изменить тип столбца на символьный, а затем искать записи по шаблону '%43%'.

SQL
SELECT *
FROM playlist_track
WHERE track_id LIKE '%43%'; 

Если сравнить значения числового поля track_id с шаблоном '43', найти нужные значения не получится. Сначала нужно изменить тип столбца на символьный, а затем искать записи по шаблону '%43%'.

Оператор `BETWEEN`

Теперь можно искать не только по конкретному значению, но и по шаблону. Другая распространённая задача — найти значения из диапазона. Для этого используют оператор BETWEEN. Границы диапазона указывают так: WHERE ключевое слово BETWEEN начало диапазона AND конец диапазона. Начало и конец диапазона включены в проверку условием.

Такой запрос выгрузит из таблицы invoice все записи, в которых идентификаторы покупателей находятся в диапазоне от 23 до 48 включительно:

SQL
SELECT *
FROM invoice
WHERE customer_id BETWEEN 23 AND 48; 

Оператор BETWEEN можно использовать, чтобы задать промежуток времени.

Нужно отобрать фамилии сотрудников, родившихся в период с 1 января 1970 года по 31 декабря 1970 года. Поможет оператор BETWEEN:

SQL
SELECT last_name
FROM staff
WHERE birth_date BETWEEN '1970-01-01' AND '1970-12-31'; 

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

Специальные операторы в условиях

Оператор IN

Оператор LIKE

Оператор BETWEEN

Оператор `IN`

Оператор `LIKE`

Оператор `BETWEEN`