IN: список вместо цепочки ИЛИ
В прошлом уроке отбор строился из сравнений и связок. Этого хватает на всё, но некоторые вопросы записываются так длинно, что в них начинают ошибаться. Для четырёх самых частых в языке есть короткие слова, и первое из них — IN.
SELECT name, city
FROM customers
WHERE city IN ('Москва', 'Казань', 'Сочи')
Семь строк: четверо москвичей, двое казанцев и одна покупательница из Сочи. То же самое можно написать тремя условиями через OR, и результат будет ровно тот же.
Зачем тогда IN. Во-первых, его труднее испортить. Цепочка OR внутри условия, где есть ещё и AND, требует скобок, и это та самая ошибка, которой был посвящён прошлый урок. В списке скобки стоят сами.
Во-вторых, список читается с одного взгляда. Три города через OR — это три строки, которые надо прочесть до конца, чтобы убедиться, что все они про город. Список из трёх значений виден сразу.
Значения в списке пишутся по обычным правилам: текст в одинарных кавычках, числа без кавычек. Смешивать их в одном списке бессмысленно — колонка-то одна.
SELECT id, status
FROM orders
WHERE status IN ('new', 'paid')
Четыре заказа: два новых и два оплаченных. А если нужно наоборот — «все, кроме этих», — есть NOT IN:
SELECT id, status
FROM orders
WHERE status NOT IN ('done', 'cancelled')
Шесть заказов: те, что ещё в работе. NOT IN читается как «ни одно из перечисленного», и с ним связана ловушка, ради которой написана четвёртая глава этого урока, — но до неё ещё две.
Пустой список в IN писать нельзя — IN () база не примет. Звучит как мелочь, но в коде, который собирает список из переменной, это самая частая поломка: сегодня в списке три города, завтра ноль, и запрос перестаёт разбираться вовсе.
Две мелочи, которые сберегут время. Первая: список сравнивается с одной колонкой, той, что стоит слева. Написать «город или имя из списка» одним IN нельзя — это два разных условия, и соединяются они связкой, как обычно.
Вторая: длинный список — признак того, что данные просятся в таблицу. Десять значений в списке читаются, сто — уже нет, а тысяча в части баз просто не примется. Когда список становится длинным, его берут не из текста запроса, а из другого запроса, — и этому посвящён отдельный урок про подзапросы.
BETWEEN: диапазон с обеими границами
Второе короткое слово — BETWEEN, «в промежутке». Оно заменяет пару условий с двумя знаками.
SELECT title, price FROM products WHERE price BETWEEN 1000 AND 5000
Шесть товаров: мышь, чайник, рюкзак, лампа и две книги. То же самое — price >= 1000 AND price <= 5000, знак в знак.
Главное, что надо запомнить про BETWEEN: обе границы входят в диапазон. Товар ровно за тысячу попадёт в ответ, и товар ровно за пять тысяч — тоже. По-английски это читается как «между», и от слова «между» ждут, что края не считаются; в SQL они считаются.
Второе: порядок границ важен. Сначала меньшая, потом большая. BETWEEN 5000 AND 1000 не ошибка — база честно поймёт это как «больше или равно пяти тысячам и меньше или равно тысяче» и вернёт пустоту. Ни красной строчки, ни предупреждения: просто ноль строк там, где вы ждали шесть.
Работает BETWEEN не только с числами. С датами:
SELECT id, order_date FROM orders WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31'
Четыре мартовских заказа. Даты в нашей базе хранятся текстом в формате «год-месяц-день» — и именно поэтому сравнение работает: у такого текста алфавитный порядок совпадает с порядком времени.
И с текстом тоже, хотя это редкость: name BETWEEN 'А' AND 'Г' отберёт имена, начинающиеся с первых букв алфавита. Пользы немного, но помнить стоит: BETWEEN работает везде, где работают знаки сравнения.
У BETWEEN есть и отрицание — NOT BETWEEN: «вне этого отрезка, включая ничего из концов». price NOT BETWEEN 1000 AND 5000 оставит девять товаров: те, что дешевле тысячи, и те, что дороже пяти.
Из этого равенства следует и то, когда BETWEEN не подходит. Он умеет только «от и до включительно», а полуоткрытый промежуток — «с первого марта и до первого апреля, но апрель уже не считаем» — им не записать. Приходится возвращаться к двум условиям, и это не поражение, а нормальный выбор: короткая запись хороша ровно до тех пор, пока говорит то, что нужно.
И ещё: границы не обязаны быть числами в запросе. Слева и справа могут стоять колонки той же строки, и тогда получается условие вида «цена в строке заказа попадает между минимальной и максимальной ценой товара». Пользуются этим нечасто, но знать полезно: перед вами обычное сравнение, а не особая конструкция для констант.
LIKE: поиск по образцу
Третье слово — LIKE. Оно сравнивает текст не целиком, а с образцом, в котором есть подстановочные знаки.
| знак | что значит | образец | что подойдёт |
|---|---|---|---|
| % | сколько угодно любых знаков, в том числе ни одного | 'Книга%' | «Книга «SQL с нуля»» |
| _ | ровно один любой знак | 'p___' | «paid» |
SELECT title FROM products WHERE title LIKE 'Книга%'
Две строки — обе книги. Образец читается так: сначала слово «Книга», дальше что угодно. Если бы мы написали 'Книга' без процента, LIKE сравнил бы текст целиком и не нашёл ничего: названий, состоящих ровно из слова «Книга», в базе нет.
Процент можно ставить с обеих сторон — тогда получится «содержит»:
SELECT title FROM products WHERE title LIKE '%код%'
Одна строка: «Книга «Чистый код»». Это самый частый способ искать по куску слова, и он же самый дорогой — о цене будет сказано в конце главы.
Подчёркивание нужно реже, но незаменимо, когда важна длина. status LIKE 'p___' найдёт статусы из четырёх знаков, начинающиеся на «p», — то есть paid, но не pending, если бы такой был.
И последнее про LIKE: что делать, если сам знак процента нужно найти в тексте. Для этого образцу назначают знак экранирования:
WHERE title LIKE '%50!%%' ESCAPE '!'
Здесь ! объявлен служебным, и !% означает «настоящий знак процента», а последний % остаётся подстановочным. Нужно это редко — в скидках, номерах и артикулах, — но, встретив такую запись в чужом запросе, вы теперь знаете, что она значит.
И ещё одно, чего от LIKE ждут напрасно: он не ищет по смыслу и не исправляет опечатки. Образец '%книг%' не найдёт «Книга», если база различает регистр, а «кнга» не найдёт ничего вовсе. Настоящий поиск по тексту — с учётом словоформ и опечаток — это отдельные средства базы, и в уровень 1 они не входят.
Полезно помнить и границу между LIKE и знаком «равно». Образец без единого подстановочного знака — это обычное равенство, записанное длиннее: title LIKE 'Мышь Light' и title = 'Мышь Light' — одно и то же. Значит, и выбирать между ними не нужно: LIKE берут тогда, когда в образце есть процент или подчёркивание, и не берут, когда их нет.
И последнее наблюдение, которое пригодится в работе: LIKE — это первый инструмент разведки по грязным данным. Города, введённые руками, обычно записаны десятком способов, и увидеть это можно одним образцом «содержит»: находится и «Москва», и «г. Москва», и «МОСКВА». Отбор по знаку «равно» нашёл бы только один из вариантов и молча пропустил остальные.
IS NULL: пустота — третье состояние
И четвёртое слово — самое важное в уроке. В прошлый раз мы видели, что строка с незаполненной клеткой выпадает из любого сравнения. Теперь разберёмся почему и что с этим делать.
Дело в том, что в SQL не два состояния условия, а три: истина, ложь и пустота.
Сравнение с пустотой даёт не «ложь», а снова пустоту: база не утверждает, что возраст не равен тридцати, — она говорит, что ответить нечем. А в ответ попадают только те строки, для которых условие истинно. Не «не ложно», а именно истинно.
Отсюда и правило: у пустоты нельзя спросить «равна ли ты пустоте». WHERE age = NULL не ошибка — это условие, которое никогда не истинно, и оно вернёт ноль строк всегда, даже когда пустые клетки в колонке есть. Для проверки существует отдельная запись:
SELECT name, city FROM customers WHERE age IS NULL
Одна строка: у покупательницы из Сочи возраст не указан. И обратное — IS NOT NULL — даст остальные одиннадцать.
Теперь обещанная ловушка NOT IN. Она выглядит невинно и портит отчёты целиком, а не по строчке.
-- вернёт ноль строк, хотя таких покупателей одиннадцать SELECT name FROM customers WHERE age NOT IN (28, NULL)
Почему так. NOT IN разворачивается в цепочку «не равно этому и не равно тому». Сравнение с пустотой даёт пустоту, а «истина и пустота» — это снова пустота. Условие не истинно ни для одной строки, и ответ пуст.
В написанном руками списке пустоты обычно нет. Но список часто приходит из другого запроса — «все, кроме тех, кто уже заказывал», — и если среди пришедших значений окажется хоть одна незаполненная клетка, отчёт опустеет целиком. Не ошибётся на строку, а вернёт ноль. Подзапросам посвящён отдельный урок, и там это правило повторяется ещё раз — оно того стоит.
А у IN пустота в списке ведёт себя мягче: она просто никому не подойдёт, и остальные значения продолжат работать. Несимметрично и потому неожиданно — но так устроена трёхзначная логика.
Обратное слово — IS NOT NULL: «клетка заполнена». Пишется так же и читается так же; знака «не равно» тут нет, потому что сравнения с пустотой не существует вовсе.
Стоит сразу развести две вещи, которые путают постоянно. Пустая клетка и пустая строка — разное. Незаполненная клетка это отсутствие значения, а пустая строка ('') — значение, просто нулевой длины: её видно как значение, она проходит сравнения и не находится проверкой IS NULL. Система, запрещающая одно, обычно разрешает другое, поэтому требование «поле обязательно» проверяют обоими условиями сразу.
И ещё одно, чего от пустоты не ждут: в ответе запроса она выглядит как ничто. Ни слова, ни знака — просто ничего в клетке, ровно как у пустой строки и как у текста из одних пробелов. Отличить их глазами нельзя, и спорить о том, «пусто там или не заполнено», бессмысленно: это выясняется запросом, а не взглядом на выгрузку.