Фільтрація та пагінація запитів

Застосунку майже ніколи не потрібні всі рядки таблиці одночасно. Каталог має показати активні курси потрібної категорії, пошук - знайти назви за фрагментом, а сторінка результатів - повернути лише невелику впорядковану частину. Для цього SQL поєднує фільтрацію, сортування й обмеження кількості рядків.

Цілі лекції

Після опрацювання матеріалу ви зможете:

складати умови WHERE з операторами порівняння;
правильно поєднувати умови через AND, OR і NOT;
застосовувати BETWEEN, IN, IS NULL, LIKE та ILIKE;
сортувати результат через ORDER BY ASC і DESC;
створювати сталий порядок за допомогою додаткового поля сортування;
ділити впорядкований результат на сторінки через LIMIT і OFFSET;
пояснювати базові обмеження пагінації зі зміщенням.

Передумови

Потрібно знати будову таблиці, базовий синтаксис SELECT і призначення INSERT. Приклади розраховані на PostgreSQL і виконуються незалежно від інших таблиць.

Навчальний набір даних

Усі приклади використовують один каталог онлайн-курсів. Виконайте скрипт у навчальній базі PostgreSQL:

DROP TABLE IF EXISTS online_courses;

CREATE TABLE online_courses (
    id integer PRIMARY KEY,
    code varchar(6) NOT NULL UNIQUE,
    title varchar(100) NOT NULL,
    category varchar(30) NOT NULL,
    level varchar(20) NOT NULL,
    price numeric(8, 2) NOT NULL,
    seats_available integer NOT NULL,
    published_on date NOT NULL,
    mentor varchar(80),
    is_active boolean NOT NULL
);

INSERT INTO online_courses
    (id, code, title, category, level, price, seats_available,
     published_on, mentor, is_active)
VALUES
    (1,  'DB-101', 'Основи SQL',                 'SQL',        'початковий',    600.00, 12, '2026-01-15', 'Олена Коваль',    TRUE),
    (2,  'DB-102', 'SQL для аналітики',          'SQL',        'середній',      800.00,  0, '2026-02-01', 'Ігор Мельник',    TRUE),
    (3,  'DB-103', 'PostgreSQL: перші кроки',    'PostgreSQL', 'початковий',    600.00,  7, '2026-02-10', 'Олена Коваль',    TRUE),
    (4,  'DB-104', 'PostgreSQL для розробників', 'PostgreSQL', 'середній',     1000.00,  3, '2026-03-05', 'Марія Левченко',  TRUE),
    (5,  'DB-105', 'Проєктування схем даних',    'Design',     'середній',      900.00,  9, '2025-11-20', 'Андрій Бондар',    TRUE),
    (6,  'DB-106', 'Нормалізація без страху',    'Design',     'початковий',    500.00, 15, '2026-01-22', NULL,               TRUE),
    (7,  'DB-107', 'Індекси PostgreSQL',         'PostgreSQL', 'просунутий',   1200.00,  4, '2026-04-12', 'Марія Левченко',  TRUE),
    (8,  'DB-108', 'Транзакції та ACID',         'SQL',        'просунутий',   1200.00,  2, '2026-04-20', NULL,               TRUE),
    (9,  'DB-109', 'SQL-практикум',              'SQL',        'початковий',    800.00,  8, '2025-12-12', 'Ігор Мельник',    FALSE),
    (10, 'DB-110', 'Безпека баз даних',          'Security',   'середній',     1000.00,  5, '2026-05-02', 'Наталія Сова',    TRUE),
    (11, 'DB-111', 'Резервне копіювання',        'Operations', 'середній',      900.00,  0, '2026-05-15', NULL,               TRUE),
    (12, 'DB-112', 'MongoDB: вступ',              'NoSQL',      'початковий',    600.00, 11, '2026-06-01', 'Андрій Бондар',    FALSE),
    (13, 'DB-113', 'Redis для вебзастосунків',   'NoSQL',      'середній',      900.00,  6, '2026-06-10', 'Наталія Сова',    TRUE),
    (14, 'DB-114', 'SQL і якість даних',         'SQL',        'середній',      800.00, 10, '2026-03-18', 'Олена Коваль',    TRUE),
    (15, 'DB-115', 'Оптимізація SQL-запитів',    'SQL',        'просунутий',   1200.00,  1, '2026-06-20', 'Марія Левченко',  TRUE);

Перевірити початковий вміст можна так:

SELECT *
FROM online_courses
ORDER BY id;

1. Фільтрація через `WHERE`

WHERE залишає в результаті лише ті рядки, для яких умова має істинне значення. Умова записується після FROM:

SELECT id, title, price
FROM online_courses
WHERE price < 800;

Оператори порівняння:

Оператор	Значення	Приклад
`=`	дорівнює	`category = 'SQL'`
`<>` або `!=`	не дорівнює	`level <> 'початковий'`
`<`	менше	`price < 800`
`>`	більше	`seats_available > 0`
`<=`	менше або дорівнює	`price <= 800`
`>=`	більше або дорівнює	`published_on >= '2026-04-01'`

Текстові й датовані значення записують в одинарних лапках. Числа та логічні значення TRUE і FALSE лапок не потребують.

SELECT code, title, published_on
FROM online_courses
WHERE is_active = TRUE;

Для логічного стовпця PostgreSQL також допускає коротку форму WHERE is_active, але явне порівняння на початку легше читати.

2. Складені умови: `AND`, `OR`, `NOT`

AND вимагає виконання всіх поєднаних умов:

SELECT title, price, seats_available
FROM online_courses
WHERE is_active = TRUE
  AND price <= 800
  AND seats_available > 0;

OR вимагає виконання хоча б однієї умови:

SELECT title, category
FROM online_courses
WHERE category = 'SQL'
   OR category = 'PostgreSQL';

NOT заперечує умову:

SELECT title, level
FROM online_courses
WHERE NOT level = 'просунутий';

Пріоритет операторів

SQL обчислює логічні оператори в такому порядку:

NOT;
AND;
OR.

Тому цей запит не означає, що вільні місця обов'язкові для обох категорій:

SELECT title, category, seats_available
FROM online_courses
WHERE category = 'SQL'
   OR category = 'PostgreSQL'
  AND seats_available > 0;

Через вищий пріоритет AND він читається як SQL OR (PostgreSQL AND seats_available > 0). Курс «SQL для аналітики» із нульовою кількістю місць також потрапить до результату.

Якщо місця потрібні для обох категорій, використайте дужки:

SELECT title, category, seats_available
FROM online_courses
WHERE (category = 'SQL' OR category = 'PostgreSQL')
  AND seats_available > 0;

Дужки не лише змінюють порядок обчислення, а й документують задум автора запиту.

3. Діапазони та списки: `BETWEEN` і `IN`

BETWEEN перевіряє належність до включного діапазону: обидві межі входять до нього.

SELECT title, price
FROM online_courses
WHERE price BETWEEN 800 AND 1000;

Ця умова еквівалентна price >= 800 AND price <= 1000. Значення 800 і 1000 будуть вибрані.

Діапазон можна застосувати й до дат:

SELECT title, published_on
FROM online_courses
WHERE published_on BETWEEN '2026-04-01' AND '2026-05-31';

IN перевіряє, чи дорівнює значення одному з елементів списку:

SELECT title, category
FROM online_courses
WHERE category IN ('SQL', 'PostgreSQL', 'Design');

Це коротший і зрозуміліший запис трьох порівнянь, з'єднаних OR. Заперечення записують як NOT BETWEEN і NOT IN:

SELECT title, level
FROM online_courses
WHERE level NOT IN ('початковий', 'середній');

4. Відсутнє значення та `IS NULL`

NULL означає, що значення відсутнє або невідоме. Це не число 0, не порожній рядок і не текст 'NULL'.

Порівняння mentor = NULL не повертає потрібних рядків, бо невідоме значення не можна звичайним порівнянням визнати рівним іншому невідомому значенню. Використовуйте спеціальні перевірки:

SELECT title, mentor
FROM online_courses
WHERE mentor IS NULL;

SELECT title, mentor
FROM online_courses
WHERE mentor IS NOT NULL;

Ця особливість впливає і на заперечення. Умова mentor <> 'Олена Коваль' не включає рядки з NULL: для них результат порівняння невідомий, а WHERE залишає лише істинні умови. Якщо потрібно включити непризначених наставників, напишіть це явно:

SELECT title, mentor
FROM online_courses
WHERE mentor <> 'Олена Коваль'
   OR mentor IS NULL;

5. Пошук за шаблоном: `LIKE` та `ILIKE`

LIKE порівнює текст із шаблоном. У PostgreSQL він враховує регістр літер. ILIKE працює аналогічно, але не враховує регістр.

У шаблонах діють два спеціальні символи:

% відповідає будь-якій послідовності символів, навіть порожній;
_ відповідає рівно одному довільному символу.

Знайти назви, що починаються зі слова PostgreSQL:

SELECT title
FROM online_courses
WHERE title LIKE 'PostgreSQL%';

Знайти слово sql у будь-якій частині назви незалежно від регістру:

SELECT title
FROM online_courses
WHERE title ILIKE '%sql%';

Знайти коди DB-101 - DB-109, де останню позицію займає рівно один символ:

SELECT code, title
FROM online_courses
WHERE code LIKE 'DB-10_';

Шаблон 'SQL' без % або _ фактично вимагає повного збігу. Поширена помилка - записати title = '%SQL%': оператор = не трактує % як шаблон і шукатиме буквальний текст із відсотками.

6. Сортування через `ORDER BY`

Без ORDER BY СКБД не гарантує порядок рядків. Те, що невелика таблиця сьогодні випадково повернулася за id, не є правилом, на яке можна покладатися.

ASC задає зростання і є типовим напрямком:

SELECT id, title, price
FROM online_courses
ORDER BY price ASC;

DESC задає спадання:

SELECT id, title, published_on
FROM online_courses
ORDER BY published_on DESC;

Можна використовувати кілька ключів. Наступний ключ застосовується, коли значення попереднього однакові:

SELECT id, title, price
FROM online_courses
ORDER BY price ASC, title ASC;

Детерміноване сортування

Для пагінації важливо, щоб кожні два рядки можна було однозначно впорядкувати. Якщо сортувати лише за price, курси з однаковою ціною можуть мінятися місцями між виконаннями. Додайте унікальний вторинний ключ, наприклад id:

SELECT id, title, price
FROM online_courses
ORDER BY price ASC, id ASC;

Тепер ціна визначає основний порядок, а id стабільно розв'язує нічиї. Напрямки можуть відрізнятися:

SELECT id, title, published_on
FROM online_courses
ORDER BY published_on DESC, id ASC;

7. `LIMIT` і `OFFSET`

LIMIT визначає максимальну кількість рядків у результаті:

SELECT id, title, price
FROM online_courses
ORDER BY price ASC, id ASC
LIMIT 4;

OFFSET пропускає задану кількість рядків. Друга сторінка по чотири записи пропускає перші чотири:

SELECT id, title, price
FROM online_courses
ORDER BY price ASC, id ASC
LIMIT 4 OFFSET 4;

Для номера сторінки page, який починається з 1, і розміру page_size:

OFFSET = (page - 1) * page_size

Отже, третя сторінка по чотири рядки має OFFSET = (3 - 1) * 4 = 8:

SELECT id, title, price
FROM online_courses
ORDER BY price ASC, id ASC
LIMIT 4 OFFSET 8;

Спочатку формується відфільтрований і впорядкований результат, а потім застосовуються OFFSET і LIMIT. Тому кожна сторінка має повторювати ті самі WHERE та ORDER BY.

Базові обмеження пагінації зі зміщенням

Зміни між запитами. Якщо перед відкриттям другої сторінки додали або видалили рядок на початку списку, користувач може побачити повтор або пропустити запис.
Велике зміщення. Для OFFSET 100000 СКБД зазвичай усе одно повинна знайти й пропустити багато попередніх рядків. Глибокі сторінки можуть працювати повільно.
Несталий порядок. Без ORDER BY сторінки не мають надійного змісту. Без унікального додаткового ключа рядки з однаковим основним значенням можуть переходити між сторінками.

Для невеликих каталогів і переходу за номерами сторінок LIMIT/OFFSET є простим рішенням. Для великих списків, що часто змінюються, застосовують пагінацію за останнім побаченим ключем, але її детальна реалізація виходить за межі цієї лекції.

8. Повний запит

Потрібно показати другу сторінку активних курсів категорій SQL і PostgreSQL, які мають місця та коштують від 600 до 1000 грн. На сторінці по три курси; спочатку новіші, а за однакової дати - з меншим id.

SELECT id, code, title, category, price, published_on
FROM online_courses
WHERE is_active = TRUE
  AND category IN ('SQL', 'PostgreSQL')
  AND seats_available > 0
  AND price BETWEEN 600 AND 1000
ORDER BY published_on DESC, id ASC
LIMIT 3 OFFSET 3;

Частини запиту виконують різні ролі: WHERE визначає допустимі рядки, ORDER BY створює сталу послідовність, OFFSET пропускає першу сторінку, а LIMIT залишає не більш як три записи.

Практичні завдання

Завдання 1. Базовий фільтр

Виберіть id, title і price активних курсів початкового рівня, які коштують не більше ніж 600.00. Упорядкуйте результат за назвою від А до Я.

Завдання 2. Умова з `NULL` і шаблоном

Виберіть code, title і mentor для курсів, у назві яких є SQL незалежно від регістру та для яких наставника не призначено. Поясніть, чому умова mentor = NULL не підходить.

Завдання 3. Сторінка каталогу

Сформуйте другу сторінку по три записи для активних курсів категорій SQL, PostgreSQL або Design, що мають вільні місця. Сортуйте спочатку за ціною за спаданням, потім за id за зростанням. Виведіть id, title, category, price і seats_available.

Підсумок

WHERE залишає рядки, для яких умова істинна.
Оператори мають пріоритет NOT, потім AND, потім OR; дужки явно передають задум.
BETWEEN включає обидві межі, а IN перевіряє належність до списку.
NULL перевіряють через IS NULL або IS NOT NULL, а не через = чи <>.
LIKE і ILIKE працюють із шаблонами: % означає будь-яку послідовність, _ - рівно один символ.
ORDER BY є єдиним способом вимагати порядок; для пагінації потрібен унікальний вторинний ключ.
LIMIT обмежує кількість рядків, OFFSET пропускає попередні рядки.
Пагінація зі зміщенням проста, але може давати зміщення сторінок під час змін і сповільнюватися на великих OFFSET.

Фільтрація та пагінація запитів

Цілі лекції

Передумови

Навчальний набір даних

1. Фільтрація через WHERE

2. Складені умови: AND, OR, NOT

Пріоритет операторів

3. Діапазони та списки: BETWEEN і IN

4. Відсутнє значення та IS NULL

5. Пошук за шаблоном: LIKE та ILIKE

6. Сортування через ORDER BY