Всю прошлую тему под ногами путалась Матча-латте без цены: вставала первой в сортировке, просила NULLS LAST. В «Пенке» таких пропусков много: у части гостей нет телефона, часть заказов сделали гости без карты. Этот урок про то, как SQL хранит «не знаю» и почему с этим «не знаю» нельзя обращаться как с обычным значением.
Пустая клетка в анкете
Гость заполняет анкету для карты лояльности. Строку «телефон» он оставил пустой. Что это значит? Может, телефона нет. Может, он не захотел его давать. Может, просто забыл. Мы не знаем.
Теперь вопрос: у Пётра и у Светы, которые оба не написали телефон, одинаковые телефоны? Честный ответ: неизвестно. Может, одинаковые, может, разные, может, их нет вовсе.
В SQL такая пустая клетка называется NULL. Это не ноль и не пустая строка, а отсутствие значения. И главное правило NULL повторяет логику анкеты: если хоть что-то неизвестно, ответ тоже неизвестен. Подробнее о том, зачем базам понадобилось такое значение, рассказывает урок теории про NULL и целостность.
NULL в учебной базе
sqlite> SELECT name, phone FROM customers;
╭────────┬──────────────────╮
│ name │ phone │
╞════════╪══════════════════╡
│ Маша │ +7 900 111-22-33 │
│ Пётр │ NULL │
│ Лена │ +7 900 444-55-66 │
│ Дима │ +7 900 777-88-99 │
│ Света │ NULL │
│ Кирилл │ +7 900 000-11-22 │
╰────────┴──────────────────╯Оболочка sqlite3 пишет на месте NULL слово NULL без кавычек. А если в ячейке лежит строка из четырёх букв N, U, L, L? Её оболочка покажет в кавычках, чтобы ты не перепутал:
sqlite> SELECT NULL AS absent, 'NULL' AS word, '' AS empty;
╭────────┬────────┬───────╮
│ absent │ word │ empty │
╞════════╪════════╪═══════╡
│ NULL │ 'NULL' │ │
╰────────┴────────┴───────╯Три разные вещи: отсутствие значения, слово «NULL» и пустая строка. Пустая строка это значение, просто длиной ноль.
Почему = NULL не работает
Найдём гостей без телефона. Первое, что приходит в голову:
sqlite> SELECT name FROM customers WHERE phone = NULL;Ни одной строки и ни одной ошибки. Запрос выглядит правильным и молча врёт. Посмотрим, что дают сравнения с NULL:
sqlite> SELECT NULL = NULL, NULL <> NULL, 1 = NULL, NULL + 5, 'кофе' || NULL;
╭─────────────┬──────────────┬──────────┬──────────┬────────────────╮
│ NULL = NULL │ NULL <> NULL │ 1 = NULL │ NULL + 5 │ 'кофе' || NULL │
╞═════════════╪══════════════╪══════════╪══════════╪════════════════╡
│ NULL │ NULL │ NULL │ NULL │ NULL │
╰─────────────┴──────────────┴──────────┴──────────┴────────────────╯Всё NULL. Равен ли неизвестный телефон другому неизвестному? Неизвестно. Сколько будет «неизвестно плюс пять»? Неизвестно. Даже склейка строки с NULL даёт NULL. А WHERE оставляет только строки, где условие истинно. «Неизвестно» не истина, поэтому строка отбрасывается.
IS NULL и IS NOT NULL
Для проверки на пустоту в SQL есть отдельный оператор IS NULL. Он всегда отвечает «да» или «нет»:
sqlite> SELECT name FROM customers WHERE phone IS NULL;
╭───────╮
│ name │
╞═══════╡
│ Пётр │
│ Света │
╰───────╯Обратная проверка IS NOT NULL оставляет гостей, у которых телефон есть:
sqlite> SELECT name, phone FROM customers WHERE phone IS NOT NULL;
╭────────┬──────────────────╮
│ name │ phone │
╞════════╪══════════════════╡
│ Маша │ +7 900 111-22-33 │
│ Лена │ +7 900 444-55-66 │
│ Дима │ +7 900 777-88-99 │
│ Кирилл │ +7 900 000-11-22 │
╰────────┴──────────────────╯Те же проверки работают в любой таблице. Заказы гостей без карты:
sqlite> SELECT id, customer_id FROM orders WHERE customer_id IS NULL;
╭────┬─────────────╮
│ id │ customer_id │
╞════╪═════════════╡
│ 2 │ NULL │
│ 6 │ NULL │
│ 11 │ NULL │
╰────┴─────────────╯В SQLite есть и короткая форма a IS b: это сравнение, которое считает два NULL равными. WHERE price IS 130 найдёт Чай с чабрецом так же, как = 130, а WHERE price IS NULL найдёт Матча-латте. В других базах вместо этого пишут IS NOT DISTINCT FROM.
Три значения логики
Раз у сравнений бывает три ответа (истина, ложь, неизвестно), логике тоже пришлось расшириться. Её называют трёхзначной. Правила выводятся из здравого смысла: если ответ уже ясен без неизвестной части, база его даёт.
sqlite> SELECT NULL AND 0, NULL AND 1, NULL OR 1, NULL OR 0, NOT NULL;
╭────────────┬────────────┬───────────┬───────────┬──────────╮
│ NULL AND 0 │ NULL AND 1 │ NULL OR 1 │ NULL OR 0 │ NOT NULL │
╞════════════╪════════════╪═══════════╪═══════════╪══════════╡
│ 0 │ NULL │ 1 │ NULL │ NULL │
╰────────────┴────────────┴───────────┴───────────┴──────────╯«Неизвестно и ложь» это ложь при любом неизвестном. «Неизвестно или истина» это истина. А «неизвестно и истина» по-прежнему неизвестно.
Отсюда неочевидное следствие. Условие «дешевле 150 или от 150 и дороже» кажется охватывающим все напитки:
sqlite> SELECT name, price FROM drinks WHERE price < 150 OR price >= 150;
╭────────────────┬───────╮
│ name │ price │
╞════════════════╪═══════╡
│ Эспрессо │ 120 │
│ Американо │ 150 │
│ Капучино │ 190 │
│ Латте │ 210 │
│ Раф │ 240 │
│ Какао │ 180 │
│ Чай с чабрецом │ 130 │
│ Тархун │ 200 │
╰────────────────┴───────╯Восемь напитков из девяти. Матча-латте выпала: для неё обе части дают «неизвестно». Это классический вопрос на собеседовании, его подробно разбирает урок про трёхзначную логику.
coalesce и ifnull: подставить значение
Иногда пропуск надо не найти, а заменить. Функция coalesce возвращает первый аргумент, который не NULL:
sqlite> SELECT name, coalesce(phone, 'нет телефона') AS phone FROM customers;
╭────────┬──────────────────╮
│ name │ phone │
╞════════╪══════════════════╡
│ Маша │ +7 900 111-22-33 │
│ Пётр │ нет телефона │
│ Лена │ +7 900 444-55-66 │
│ Дима │ +7 900 777-88-99 │
│ Света │ нет телефона │
│ Кирилл │ +7 900 000-11-22 │
╰────────┴──────────────────╯ifnull(a, b) делает то же для двух аргументов. Подставлять ноль вместо неизвестной цены надо осторожно: в отчёте Матча-латте станет бесплатной.
sqlite> SELECT name, ifnull(price, 0) AS price, coalesce(price, 0) * 2 AS double FROM drinks WHERE category = 'чай';
╭────────────────┬───────┬────────╮
│ name │ price │ double │
╞════════════════╪═══════╪════════╡
│ Чай с чабрецом │ 130 │ 260 │
│ Матча-латте │ 0 │ 0 │
╰────────────────┴───────┴────────╯coalesce меняет только ответ запроса, данные в таблице остаются прежними.
.nullvalue: как показать NULL
Если хочется, чтобы NULL бросался в глаза, оболочке можно сказать, какой текст печатать на его месте. Это команда оболочки, а не SQL, и она действует до конца сеанса:
sqlite> .nullvalue (нет)
sqlite> SELECT name, price FROM drinks WHERE category = 'чай';
╭────────────────┬───────╮
│ name │ price │
╞════════════════╪═══════╡
│ Чай с чабрецом │ 130 │
│ Матча-латте │ (нет) │
╰────────────────┴───────╯В песочнице каждый запуск это новый сеанс sqlite3, поэтому в следующем запуске снова будет NULL.
Попробуй сам
Песочница ищет пропуски в трёх таблицах и подставляет понятные подписи. Попробуй заменить IS NULL на = NULL и посмотри, что станет с ответом.
Проверь себя
Задание
Дальше
Пока мы только читали данные. В следующем уроке учимся их менять: добавлять, исправлять и удалять строки.
домашка