BETWEEN в MySQL. Введение
BETWEEN в MySQL — оператор проверки диапазона. Он возвращает TRUE, если выражение находится между двумя границами включительно. Работает с числами, датами, строками и выражениями. Пара NOT BETWEEN отрицает диапазон. Ниже — синтаксис, типовой шаблон и 100 уникальных примеров.
Синтаксис
SELECT *
FROM orders
WHERE total BETWEEN 100 AND 500;SELECT *
FROM users
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';UPDATE invoices
SET status='overdue'
WHERE due_date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();SELECT CASE WHEN age BETWEEN 18 AND 30 THEN 'young' ELSE 'other' END AS age_group
FROM customers;Типовая конструкция
SELECT id, name, total
FROM sales
WHERE total BETWEEN 1000 AND 5000
ORDER BY total DESC;100 примеров
1. Фильтрация по цене от 100 до 500
SELECT id, name, price
FROM products
WHERE price BETWEEN 100 AND 500;2. Выбор сотрудников по возрасту 25–40
SELECT id, full_name, age
FROM employees
WHERE age BETWEEN 25 AND 40;3. Отбор заказов по количеству позиций 5–20
SELECT order_id, item_count
FROM orders
WHERE item_count BETWEEN 5 AND 20;4. Поиск студентов по среднему баллу 3.5–4.5
SELECT student_id, avg_score
FROM grades
WHERE avg_score BETWEEN 3.5 AND 4.5;5. Проверка диапазона скидки 10–30%
SELECT id, discount
FROM promotions
WHERE discount BETWEEN 0.1 AND 0.3;6. Выбор машин по объёму двигателя 1.5–2.5 л
SELECT model, engine_cc
FROM cars
WHERE engine_cc BETWEEN 1500 AND 2500;7. Фильтр по числу посещений 10–100
SELECT user_id, visits
FROM analytics
WHERE visits BETWEEN 10 AND 100;8. Выбор транзакций по сумме 50–200
SELECT txn_id, amount
FROM transactions
WHERE amount BETWEEN 50 AND 200;9. Фильтр по длительности видео 5–30 мин
SELECT id, title, duration_min
FROM videos
WHERE duration_min BETWEEN 5 AND 30;10. Отбор по рейтингу фильмов 7–9
SELECT movie_id, rating
FROM movies
WHERE rating BETWEEN 7 AND 9;11. Выбор товаров по остатку 1–10
SELECT sku, qty
FROM stock
WHERE qty BETWEEN 1 AND 10;12. Фильтр по расстоянию 100–500 км
SELECT route_id, distance_km
FROM routes
WHERE distance_km BETWEEN 100 AND 500;13. Пороговые значения температуры
SELECT sensor_id, temp_c
FROM sensors
WHERE temp_c BETWEEN 18 AND 25;14. Выбор заказов по времени доставки (часы)
SELECT id, delivery_hours
FROM shipments
WHERE delivery_hours BETWEEN 24 AND 72;15. Сегментация покупателей по числу заказов
SELECT customer_id, orders_cnt
FROM agg_customers
WHERE orders_cnt BETWEEN 5 AND 12;16. Заказы за 2025 год
SELECT id, created_at
FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';17. Новые пользователи за 30 дней
SELECT id, username, created_at
FROM users
WHERE created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();18. События текущей недели
SELECT event_id, start_time
FROM events
WHERE start_time BETWEEN DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND DATE_ADD(CURDATE(), INTERVAL (6-WEEKDAY(CURDATE())) DAY);19. Продажи за II квартал 2025
SELECT region, SUM(total) AS amount
FROM sales
WHERE sale_date BETWEEN '2025-04-01' AND '2025-06-30'
GROUP BY region;20. Активность за 24 часа
SELECT user_id, last_seen
FROM sessions
WHERE last_seen BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW();21. Регистрации в сентябре
SELECT campaign, COUNT(*) AS cnt
FROM signups
WHERE signup_date BETWEEN '2025-09-01' AND '2025-09-30'
GROUP BY campaign;22. Рабочие отчёты за неделю
SELECT id, report_date
FROM reports
WHERE report_date BETWEEN '2025-09-15' AND '2025-09-19';23. Удалить логи вне последних 30 дней
DELETE FROM logs
WHERE created_at NOT BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();24. Доставки между 10 и 20 марта
SELECT id, delivery_date
FROM shipments
WHERE delivery_date BETWEEN '2025-03-10' AND '2025-03-20';25. Изменения за 10 дней
SELECT id, updated_at
FROM changes
WHERE updated_at BETWEEN DATE_SUB(NOW(), INTERVAL 10 DAY) AND NOW();Ещё примеры
26. Сеансы за вчера
SELECT user_id, started_at
FROM sessions
WHERE started_at BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE();27. Платежи за прошлую неделю
SELECT id, paid_at
FROM payments
WHERE paid_at BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND NOW();28. Подписки активные в июле
SELECT id, valid_from, valid_till
FROM subscriptions
WHERE '2025-07-15' BETWEEN valid_from AND valid_till;29. Слоты бронирования сегодня
SELECT slot_id, starts_at
FROM booking_slots
WHERE starts_at BETWEEN CONCAT(CURDATE(),' 00:00:00') AND CONCAT(CURDATE(),' 23:59:59');30. Итерации спринта за период
SELECT sprint_id, dt
FROM sprint_calendar
WHERE dt BETWEEN '2025-08-01' AND '2025-08-14';31. Алфавитный диапазон фамилий A–H
SELECT id, last_name
FROM customers
WHERE last_name BETWEEN 'A' AND 'H';32. Названия товаров: apple–mango
SELECT id, name
FROM products
WHERE name BETWEEN 'apple' AND 'mango';33. Домены: a–n
SELECT id, domain
FROM sites
WHERE domain BETWEEN 'a' AND 'n';34. Коды справочника A100–A999
SELECT code
FROM refs
WHERE code BETWEEN 'A100' AND 'A999';35. SKU 1000–1999
SELECT sku, product_name
FROM inventory
WHERE sku BETWEEN '1000' AND '1999';36. Категории: Books–Music
SELECT category_id, category_name
FROM categories
WHERE category_name BETWEEN 'Books' AND 'Music';37. Теги: alpha–omega
SELECT id, tag
FROM tags
WHERE tag BETWEEN 'alpha' AND 'omega';38. Версии ПО 1.0–2.0 (строковое сравнение)
SELECT id, version
FROM releases
WHERE version BETWEEN '1.0' AND '2.0';39. Артикулы B-001–B-999
SELECT id, article
FROM articles
WHERE article BETWEEN 'B-001' AND 'B-999';40. Хеши 0000–0fff
SELECT id, hash
FROM audit
WHERE hash BETWEEN '0000' AND '0fff';41. Рабочее окно 08:00–17:00
SELECT id, time_of_day
FROM metrics
WHERE time_of_day BETWEEN '08:00:00' AND '17:00:00';42. Ночное время 00:00–06:00
SELECT id, ts
FROM events
WHERE TIME(ts) BETWEEN '00:00:00' AND '06:00:00';43. Пиковые часы 18–21
SELECT id, ts
FROM traffic
WHERE HOUR(ts) BETWEEN 18 AND 21;44. Окно доставки сегодня 10–14
SELECT id, delivery_at
FROM shipments
WHERE delivery_at BETWEEN CONCAT(CURDATE(),' 10:00:00') AND CONCAT(CURDATE(),' 14:00:00');45. Заказы в конце месяца
SELECT id, created_at
FROM orders
WHERE DAY(created_at) BETWEEN 25 AND 31;46. Транзакции в первой половине дня
SELECT id, created_at
FROM payments
WHERE HOUR(created_at) BETWEEN 0 AND 11;47. Релизы в 1‑й квартал (номер месяца)
SELECT id, released_at
FROM releases
WHERE MONTH(released_at) BETWEEN 1 AND 3;48. События по минутам 15–45
SELECT id, ts
FROM events
WHERE MINUTE(ts) BETWEEN 15 AND 45;49. Год выпуска 2015–2020
SELECT id, year
FROM products
WHERE year BETWEEN 2015 AND 2020;50. Возраст клиента 18–65
SELECT id, age
FROM clients
WHERE age BETWEEN 18 AND 65;Ещё примеры
51. Группировка по диапазону суммы
SELECT customer_id, SUM(total) AS sum_total
FROM orders
GROUP BY customer_id
HAVING SUM(total) BETWEEN 1000 AND 5000;52. Фильтр по количеству покупок
SELECT product_id, COUNT(*) AS cnt
FROM orders
GROUP BY product_id
HAVING COUNT(*) BETWEEN 10 AND 50;53. Средний чек в диапазоне
SELECT region, AVG(total) AS avg_total
FROM sales
GROUP BY region
HAVING AVG(total) BETWEEN 200 AND 400;54. CASE: классы выручки
SELECT customer_id,
CASE WHEN SUM(total) BETWEEN 0 AND 999 THEN 'low'
WHEN SUM(total) BETWEEN 1000 AND 4999 THEN 'mid'
ELSE 'high' END AS tier
FROM orders
GROUP BY customer_id;55. Доля возвратов в норме
SELECT shop_id,
SUM(CASE WHEN status='returned' THEN 1 ELSE 0 END)/COUNT(*) AS ret_rate
FROM orders
GROUP BY shop_id
HAVING SUM(CASE WHEN status='returned' THEN 1 ELSE 0 END)/COUNT(*) BETWEEN 0.02 AND 0.08;56. Интервал ARPU в сегменте
SELECT segment, AVG(revenue) AS arpu
FROM users
GROUP BY segment
HAVING AVG(revenue) BETWEEN 5 AND 20;57. Активность по дням недели
SELECT user_id, COUNT(*) AS cnt
FROM logins
GROUP BY user_id
HAVING COUNT(*) BETWEEN 5 AND 15;58. Время ответа SLA
SELECT team, AVG(resp_ms) AS r
FROM tickets
GROUP BY team
HAVING AVG(resp_ms) BETWEEN 200 AND 800;59. Количество отзывов в коридоре
SELECT product_id, COUNT(*) AS reviews
FROM reviews
GROUP BY product_id
HAVING COUNT(*) BETWEEN 3 AND 20;60. Температура сенсора по суткам
SELECT DATE(ts) AS d, AVG(temp) AS t
FROM sensors
GROUP BY DATE(ts)
HAVING AVG(temp) BETWEEN 18 AND 25;61. Диапазон от минимума до среднего*1.2
SELECT id, price
FROM products
WHERE price BETWEEN (SELECT MIN(price) FROM products) AND (SELECT AVG(price)*1.2 FROM products);62. Оплата близка к медиане ±10%
SELECT id, amount
FROM payments
WHERE amount BETWEEN (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) FROM payments)*0.9 AND (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) FROM payments)*1.1;63. Баллы относительно среднего по группе
SELECT s.user_id, s.points
FROM scores s
WHERE s.points BETWEEN (SELECT AVG(points) FROM scores WHERE group_id=s.group_id)-10 AND (SELECT AVG(points) FROM scores WHERE group_id=s.group_id)+10;64. Дата в окне квартала вокруг заказа
SELECT o.id, o.created_at
FROM orders o
WHERE o.created_at BETWEEN DATE_SUB((SELECT MAX(created_at) FROM orders), INTERVAL 90 DAY) AND (SELECT MAX(created_at) FROM orders);65. Цена в ценовом коридоре категории
SELECT p.id, p.price
FROM products p
WHERE p.price BETWEEN (SELECT MIN(price) FROM products WHERE category=p.category) AND (SELECT MAX(price) FROM products WHERE category=p.category);66. Количество покупок в коридоре перцентилей
SELECT customer_id, cnt
FROM customer_stats
WHERE cnt BETWEEN (SELECT p10 FROM percentiles) AND (SELECT p90 FROM percentiles);67. Вес посылки в разрешённом диапазоне тарифа
SELECT id, weight
FROM parcels
WHERE weight BETWEEN (SELECT min_w FROM tariffs WHERE id=parcels.tariff_id) AND (SELECT max_w FROM tariffs WHERE id=parcels.tariff_id);68. Время ответа в целевом окне SLA
SELECT ticket_id, resp_ms
FROM ticket_metrics
WHERE resp_ms BETWEEN (SELECT target_ms*0.8 FROM sla WHERE team=ticket_metrics.team) AND (SELECT target_ms*1.2 FROM sla WHERE team=ticket_metrics.team);69. Оценка в окне ±1 от среднего преподавателя
SELECT s.student_id, s.mark
FROM student_marks s
WHERE s.mark BETWEEN (SELECT AVG(mark)-1 FROM student_marks WHERE teacher_id=s.teacher_id) AND (SELECT AVG(mark)+1 FROM student_marks WHERE teacher_id=s.teacher_id);70. Срок доставки по региональным границам
SELECT id, hours
FROM deliveries
WHERE hours BETWEEN (SELECT min_h FROM region_sla WHERE region=deliveries.region) AND (SELECT max_h FROM region_sla WHERE region=deliveries.region);71. JOIN по диапазону дат (факт в календаре)
SELECT f.id, c.dt
FROM facts f
JOIN calendar c ON c.dt BETWEEN f.start_dt AND f.end_dt;72. JOIN по цене в тарифном коридоре
SELECT o.id, t.name
FROM offers o
JOIN tariffs t ON o.price BETWEEN t.min_price AND t.max_price;73. UPDATE статуса по окну просрочки
UPDATE invoices
SET status='overdue'
WHERE due_at BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW();74. DELETE архивных логов вне окна 90 дней
DELETE FROM logs
WHERE created_at NOT BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) AND NOW();75. JOIN события в окне времени визита
SELECT v.id, e.id AS event_id
FROM visits v
JOIN events e ON e.occurred_at BETWEEN v.started_at AND v.ended_at;Ещё примеры
76. JOIN пользователей по возрастным группам
SELECT u.id, g.group_name
FROM users u
JOIN age_groups g ON u.age BETWEEN g.age_from AND g.age_to;77. UPDATE скидки в коридоре цен
UPDATE products
SET discount=0.1
WHERE price BETWEEN 1000 AND 2000;78. DELETE дублей с id в окне
DELETE FROM tmp_import
WHERE row_id BETWEEN 100000 AND 200000;79. JOIN расписание по времени пары
SELECT s.student_id, t.subject
FROM timeslots t
JOIN schedule s ON t.start_time BETWEEN s.start_time AND s.end_time;80. UPDATE флага прайм‑тайма
UPDATE programs
SET prime_time=1
WHERE TIME(starts_at) BETWEEN '19:00:00' AND '22:59:59';81. Маркировка транзакций в среднем коридоре
SELECT id, amount,
CASE WHEN amount BETWEEN AVG(amount) OVER ()*0.8 AND AVG(amount) OVER ()*1.2 THEN 1 ELSE 0 END AS in_band
FROM transactions;82. Фильтр дней по рангу продаж
SELECT d, amt
FROM (
SELECT DATE(created_at) AS d, SUM(total) AS amt,
RANK() OVER (ORDER BY SUM(total) DESC) AS r
FROM orders
GROUP BY DATE(created_at)
) x
WHERE r BETWEEN 1 AND 7;83. Сортировка с кастомным приоритетом и коридором
SELECT id, arpu
FROM users
ORDER BY CASE WHEN arpu BETWEEN 20 AND 100 THEN 0 ELSE 1 END, arpu DESC;84. Индикатор дневного интервала активности
SELECT user_id, ts,
CASE WHEN HOUR(ts) BETWEEN 9 AND 18 THEN 'work' ELSE 'off' END AS slot
FROM events;85. Отбор батчей по id-блокам
SELECT id
FROM big_table
WHERE id BETWEEN 1 AND 100000 OR id BETWEEN 500000 AND 600000;86. Товары со скидкой в коридоре и сортировкой
SELECT id, price, discount
FROM products
WHERE discount BETWEEN 0.05 AND 0.2
ORDER BY price DESC;87. Категории с диапазоном длины названия
SELECT id, name
FROM categories
WHERE CHAR_LENGTH(name) BETWEEN 3 AND 10;88. Сессии длиной 5–15 мин
SELECT user_id, TIMESTAMPDIFF(MINUTE, started_at, ended_at) AS mins
FROM sessions
WHERE TIMESTAMPDIFF(MINUTE, started_at, ended_at) BETWEEN 5 AND 15;89. Дни года 100–200
SELECT id, dt
FROM calendar
WHERE DAYOFYEAR(dt) BETWEEN 100 AND 200;90. Регионы с населением 1–5 млн
SELECT id, name, population
FROM regions
WHERE population BETWEEN 1000000 AND 5000000;Ещё примеры
91. Исключить зарплаты вне 30k–120k
SELECT id, salary
FROM employees
WHERE salary NOT BETWEEN 30000 AND 120000;92. Удалить записи вне 90‑дневного окна
DELETE FROM logs
WHERE created_at NOT BETWEEN DATE_SUB(NOW(), INTERVAL 90 DAY) AND NOW();93. Студенты с возрастом вне 18–25
SELECT id, age
FROM students
WHERE age NOT BETWEEN 18 AND 25;94. Игроки по очкам вне 100–200
SELECT id, points
FROM gamers
WHERE points NOT BETWEEN 100 AND 200;95. Экзамены вне дат семестра
SELECT id, exam_date
FROM exams
WHERE exam_date NOT BETWEEN '2025-01-10' AND '2025-06-10';Ещё примеры
96. Товары с ценой вне ±20% от средней
SELECT id, price
FROM products
WHERE price NOT BETWEEN 0.8*(SELECT AVG(price) FROM products) AND 1.2*(SELECT AVG(price) FROM products);97. Чек‑ин вне рабочего времени
SELECT id, checkin_time
FROM attendance
WHERE checkin_time NOT BETWEEN '09:00:00' AND '18:00:00';98. Счётчики вне нормального коридора
SELECT id, score
FROM tests
WHERE score NOT BETWEEN 50 AND 80;99. Позиции с qty вне 1–10
SELECT id, qty
FROM order_items
WHERE qty NOT BETWEEN 1 AND 10;100. Отчёты вне периода
SELECT id, report_date
FROM reports
WHERE report_date NOT BETWEEN '2025-01-01' AND '2025-03-31';BETWEEN в MySQL. Заключение
BETWEEN и NOT BETWEEN позволяют просто описывать диапазоны чисел, дат и строк. Помните, что границы включительны; при работе с датами захватывайте полный день с помощью времени ’23:59:59′. Используйте CASE для категоризации по диапазонам.
Справка: официальная документация MySQL.