SQLLab

Глава 4 из 28

4. Запросы: SELECT, фильтры, сортировка, NULL

Почти всё, что вы делаете с базой, — это SELECT. В этой главе разберём его основные части и самую коварную особенность SQL: NULL. Именно на NULL ошибаются чаще всего, потому что он ведёт себя не так, как «пустое значение» в языках программирования.

Данные для примеров: 8 клиентов и 12 заказов. Скрипт создания лежит в конце главы, примеры запускаются кнопкой «Выполнить». У двух клиентов нет заказов, у двух не указан город, а у части клиентов есть пригласивший.

Порядок записи и порядок выполнения

Запрос пишут в одном порядке, а выполняет база в другом:

код
SELECT   ...   -- 5. что показать
FROM     ...   -- 1. откуда брать строки
WHERE    ...   -- 2. какие строки оставить
GROUP BY ...   -- 3. как сгруппировать
HAVING   ...   -- 4. какие группы оставить
ORDER BY ...   -- 6. как отсортировать
LIMIT    ...   -- 7. сколько вернуть

Из этого следует то, что часто удивляет новичков: псевдоним, заданный в SELECT, в WHERE ещё не существует. Он пригоден только в ORDER BY.

Выбор колонок и выражения

В SELECT можно не только перечислять колонки, но и вычислять новые значения. Псевдоним задаётся словом AS:

sql
SELECT id,
       amount,
       amount * 0.2 AS vat,
       upper(status) AS status_up
FROM orders
WHERE id <= 3
ORDER BY id;
код
 id |  amount |   vat   | status_up
----+---------+---------+-----------
  1 | 1500.00 | 300.000 | PAID
  2 |  320.50 |  64.100 | PAID
  3 |   99.90 |  19.980 | PAID

В рабочих запросах не используйте SELECT *. Список колонок защищает от неожиданностей, когда в таблицу добавят новую колонку, и не гоняет по сети лишние данные.

WHERE: условия

Основные операторы: =, <> (не равно), <, >, <=, >=. Для диапазонов, списков и шаблонов есть сокращения.

sql
SELECT id, customer_id, status, amount
FROM orders
WHERE status IN ('paid', 'shipped')
  AND amount BETWEEN 500 AND 2000
ORDER BY id;
код
 id | customer_id |  status |  amount
----+-------------+---------+---------
  1 |           1 | paid    | 1500.00
  8 |           5 | shipped |  640.00
 10 |           1 | shipped |  990.00

BETWEEN 500 AND 2000 включает обе границы: это amount >= 500 AND amount <= 2000. Для дат и времени на границах BETWEEN часто даёт ошибку на единицу, поэтому там надёжнее писать >= начало AND < конец.

Поиск по тексту делают через LIKE (знак % — любое количество символов, _ — один символ) и ILIKE (то же без учёта регистра):

sql
SELECT id, name FROM customers WHERE name ILIKE '%а' ORDER BY id;
код
 id |  name
----+-------
  1 | Анна
  3 | Вера
  7 | Жанна

Учтите, что условие LIKE '%текст' с процентом в начале не использует обычный индекс: база просматривает всю таблицу.

Приоритет AND и OR

У AND приоритет выше, чем у OR. Запрос без скобок читается не так, как кажется:

sql
SELECT id, status, amount
FROM orders
WHERE status = 'new' OR status = 'paid' AND amount > 1000
ORDER BY id;
код
 id | status |  amount
----+--------+---------
  1 | paid   | 1500.00
  5 | new    |  800.00
  6 | paid   | 2300.00
  9 | new    |   75.50

Условие понимается как status = 'new' OR (status = 'paid' AND amount > 1000). Заказы со статусом new попадут в результат при любой сумме. Если вы хотели другое, расставьте скобки явно:

sql
SELECT id, status, amount
FROM orders
WHERE (status = 'new' OR status = 'paid') AND amount > 1000
ORDER BY id;
код
 id | status |  amount
----+--------+---------
  1 | paid   | 1500.00
  6 | paid   | 2300.00

Сортировка и ограничение выдачи

Сортировка по нескольким колонкам: сначала по первой, при равенстве по второй. Направление задаётся для каждой колонки отдельно.

sql
SELECT id, status, amount
FROM orders
ORDER BY status ASC, amount DESC;
код
 id |   status  |  amount
----+-----------+---------
  4 | cancelled |  450.00
 12 | cancelled |   60.00
  5 | new       |  800.00
  9 | new       |   75.50
  6 | paid      | 2300.00
  1 | paid      | 1500.00
  2 | paid      |  320.50
 11 | paid      |  210.00
  7 | paid      |  120.00
  3 | paid      |   99.90
 10 | shipped   |  990.00
  8 | shipped   |  640.00

Без ORDER BY порядок строк не определён. Он может совпасть с порядком вставки, а может и нет, и меняется после UPDATE и очистки. Если порядок важен, пишите ORDER BY всегда.

LIMIT ограничивает число строк, а OFFSET пропускает первые. Вместе они дают постраничный вывод:

sql
SELECT id, amount FROM orders ORDER BY amount DESC LIMIT 3 OFFSET 3;
код
 id | amount
----+--------
  5 | 800.00
  8 | 640.00
  4 | 450.00

Это четвёртая, пятая и шестая по величине суммы. Но OFFSET на больших значениях медленный: чтобы пропустить 100 000 строк, база сначала их находит и отбрасывает. Для длинных лент и каталогов применяют «keyset pagination»: запоминают последнее значение и просят WHERE amount < последнее.

Если LIMIT стоит без ORDER BY, вы получите произвольные строки, и при повторном запуске они могут оказаться другими.

DISTINCT: убрать повторы

sql
SELECT DISTINCT status FROM orders ORDER BY status;
код
   status
-----------
 cancelled
 new
 paid
 shipped

DISTINCT сравнивает строки целиком, по всем выбранным колонкам. Часто его добавляют, чтобы «починить» дубли после JOIN, и это маскирует ошибку в запросе. Если дубли появились неожиданно, разберитесь, откуда они взялись.

NULL: неизвестное значение

NULL — это не ноль, не пустая строка и не «ничего». Это «значение неизвестно». Отсюда главное правило: любое сравнение с NULL даёт не истину и не ложь, а NULL.

sql
SELECT NULL = NULL AS equal,
       NULL <> 1 AS not_equal,
       1 = NULL AS one_equals_null;
код
 equal | not_equal | one_equals_null
-------+-----------+-----------------
       |           |

WHERE оставляет только строки, где условие истинно. Строки с NULL в условии отбрасываются. Поэтому попытка найти клиентов без города записью = NULL не сработает:

sql
SELECT id, name, city FROM customers WHERE city = NULL;
код
 id | name | city
----+------+------
(0 rows)

Запрос возвращает пустой результат. Для проверки есть специальные операторы:

sql
SELECT id, name, city FROM customers WHERE city IS NULL ORDER BY id;
код
 id |  name | city
----+-------+------
  4 | Глеб  |
  8 | Игорь |

Обратная проверка — IS NOT NULL.

Условие «не равно» тоже теряет NULL

Выберем клиентов не из Москвы:

sql
SELECT id, name, city FROM customers WHERE city <> 'Москва' ORDER BY id;
код
 id |  name |  city
----+-------+--------
  2 | Борис | Казань
  5 | Дарья | Казань
  7 | Жанна | Тверь

В результате нет клиентов с неизвестным городом: условие NULL <> 'Москва' — это NULL, а не истина. Если «не из Москвы» должно включать и тех, у кого города нет, учитывайте NULL явно:

sql
SELECT id, name, city
FROM customers
WHERE city IS DISTINCT FROM 'Москва'
ORDER BY id;
код
 id |  name |  city
----+-------+--------
  2 | Борис | Казань
  4 | Глеб  |
  5 | Дарья | Казань
  7 | Жанна | Тверь
  8 | Игорь |

Оператор IS DISTINCT FROM сравнивает как обычное «не равно», но считает NULL обычным значением. Парный оператор — IS NOT DISTINCT FROM.

NOT IN и NULL: самая опасная ловушка

У клиентов есть колонка referred_by: кто пригласил клиента, а у тех, кто пришёл сам, она пустая. Найдём клиентов, которые никого не пригласили. Сначала запрос через NOT IN, где NULL из подзапроса мы убрали руками:

sql
SELECT id, name
FROM customers
WHERE id NOT IN (SELECT referred_by FROM customers WHERE referred_by IS NOT NULL)
ORDER BY id;
код
 id |  name
----+-------
  3 | Вера
  4 | Глеб
  5 | Дарья
  6 | Егор
  7 | Жанна
  8 | Игорь

Теперь тот же запрос без фильтра. Он выглядит проще, но в списке из подзапроса оказываются NULL:

sql
SELECT id, name
FROM customers
WHERE id NOT IN (SELECT referred_by FROM customers)
ORDER BY id;
код
 id | name
----+------
(0 rows)

Второй запрос вернул ноль строк, хотя те, кто никого не приглашал, явно есть. Причина в том, что id NOT IN (1, 2, NULL) раскрывается в id <> 1 AND id <> 2 AND id <> NULL, а последнее условие неизвестно, поэтому всё выражение никогда не станет истинным. Одна строка с NULL в подзапросе обнуляет весь результат, и никакой ошибки вы не увидите.

Безопасная замена — NOT EXISTS, он NULL не боится:

sql
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM customers r WHERE r.referred_by = c.id)
ORDER BY c.id;
код
 id |  name
----+-------
  3 | Вера
  4 | Глеб
  5 | Дарья
  6 | Егор
  7 | Жанна
  8 | Игорь

COALESCE: подставить значение вместо NULL

COALESCE(a, b, ...) возвращает первое значение, которое не NULL:

sql
SELECT id, name, COALESCE(city, 'не указан') AS city_label
FROM customers
ORDER BY id;
код
 id |  name | city_label
----+-------+------------
  1 | Анна  | Москва
  2 | Борис | Казань
  3 | Вера  | Москва
  4 | Глеб  | не указан
  5 | Дарья | Казань
  6 | Егор  | Москва
  7 | Жанна | Тверь
  8 | Игорь | не указан

Сортировка и NULL

В PostgreSQL при сортировке по возрастанию NULL считается самым большим значением и идёт в конце, а при убывании оказывается первым. Это легко поменять:

sql
SELECT id, name, city FROM customers ORDER BY city DESC NULLS LAST, id;
код
 id |  name |  city
----+-------+--------
  7 | Жанна | Тверь
  1 | Анна  | Москва
  3 | Вера  | Москва
  6 | Егор  | Москва
  2 | Борис | Казань
  5 | Дарья | Казань
  4 | Глеб  |
  8 | Игорь |

CASE: условная логика внутри запроса

CASE работает как if/else и возвращает значение:

sql
SELECT id, amount,
       CASE
           WHEN amount >= 1000 THEN 'крупный'
           WHEN amount >= 300  THEN 'средний'
           ELSE 'мелкий'
       END AS size
FROM orders
ORDER BY id;
код
 id |  amount |   size
----+---------+---------
  1 | 1500.00 | крупный
  2 |  320.50 | средний
  3 |   99.90 | мелкий
  4 |  450.00 | средний
  5 |  800.00 | средний
  6 | 2300.00 | крупный
  7 |  120.00 | мелкий
  8 |  640.00 | средний
  9 |   75.50 | мелкий
 10 |  990.00 | средний
 11 |  210.00 | мелкий
 12 |   60.00 | мелкий

Условия проверяются сверху вниз, срабатывает первое подходящее. Без ELSE для неподходящих строк вернётся NULL.

Частые ошибки

  • Писать = NULL и <> NULL. Эти условия никогда не истинны. Используйте IS NULL, IS NOT NULL, IS DISTINCT FROM.
  • Не замечать, что <> и NOT IN отбрасывают NULL. Строки с неизвестным значением в результате пропадают без ошибки.
  • Использовать NOT IN с подзапросом по колонке, где бывают NULL. Один NULL обнуляет результат. Берите NOT EXISTS.
  • Забывать скобки вокруг OR вместе с AND. AND связывается сильнее и меняет смысл условия.
  • LIMIT без ORDER BY. Вернутся произвольные строки.
  • Лечить дубли через DISTINCT. Это скрывает проблему в запросе, а не решает её.
  • Использовать SELECT * в приложении. Структура таблицы изменится, а запрос поведёт себя иначе.

Выборка, сортировка и работа с NULL разобраны на заданиях в курсе «SQL с нуля».

Данные для примеров

sql
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text, referred_by int);
INSERT INTO customers VALUES
  (1, 'Анна', 'Москва', NULL), (2, 'Борис', 'Казань', 1), (3, 'Вера', 'Москва', 1), (4, 'Глеб', NULL, NULL),
  (5, 'Дарья', 'Казань', 2), (6, 'Егор', 'Москва', NULL), (7, 'Жанна', 'Тверь', NULL), (8, 'Игорь', NULL, NULL);

CREATE TABLE orders (
  id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers (id),
  status text NOT NULL, amount numeric(10, 2) NOT NULL,
  created_at date NOT NULL
);
INSERT INTO orders VALUES
  (1,  1, 'paid',      1500.00, '2026-09-03'),
  (2,  1, 'paid',       320.50, '2026-09-15'),
  (3,  2, 'paid',        99.90, '2026-09-20'),
  (4,  2, 'cancelled',  450.00, '2026-09-21'),
  (5,  3, 'new',        800.00, '2026-10-01'),
  (6,  3, 'paid',      2300.00, '2026-10-02'),
  (7,  5, 'paid',       120.00, '2026-10-03'),
  (8,  5, 'shipped',    640.00, '2026-10-05'),
  (9,  6, 'new',         75.50, '2026-10-07'),
  (10, 1, 'shipped',    990.00, '2026-10-09'),
  (11, 4, 'paid',       210.00, '2026-10-10'),
  (12, 6, 'cancelled',   60.00, '2026-10-11');

Откройте остальные главы

Ещё 20 глав: внутреннее устройство, оптимизация, администрирование. Покупка разовая, доступ навсегда.

илиОткрыть с подпиской Pro
НазадСоздание таблиц: ключи, ограничения, значения по умолчаниюДальшеJOIN и агрегаты: GROUP BY, HAVING, FILTER