🔍 BETWEEN в SQL: удобный инструмент фильтрации, но есть нюансы

В SQL оператор BETWEEN используется для фильтрации значений в заданном диапазоне, включая границы этого диапазона. Синтаксис прост, но за ним скрываются важные детали, особенно при работе с датами и временем.

Общий синтаксис:

SELECT ...
FROM ...
WHERE столбец BETWEEN значение_1 AND значение_2;

Это полностью эквивалентно:

WHERE столбец >= значение_1 AND столбец <= значение_2

Границы включаются! Это главный момент, который необходимо помнить.

Пример с числами:

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

Этот запрос вернёт все товары, цена которых от 100 до 500 включительно.
То же самое, что:

WHERE price >= 100 AND price <= 500

Обратите внимание: порядок имеет значение!
Если перепутать границы — результат будет пустым:

WHERE price BETWEEN 500 AND 100  
--  Вернёт 0 строк

Пример с датами:

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

На первый взгляд запрос безопасен, но всё зависит от типа данных order_date
— Если это DATE, то всё хорошо: вернутся все заказы от 1 января до 31 декабря 2024 года включительно.
— Если это DATETIME или TIMESTAMP, то 31 декабря попадут только записи с временем 00:00:00. То есть, большая часть заказов за 31 декабря может быть пропущена!

Как правильно писать условия для DATETIME.
Используйте открытый верхний предел:

WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01'

Такой стиль абсолютно безопасен и широко применяется в профессиональной практике, особенно в BI и аналитике.

Что ещё важно?
1. Типы данных должны быть совместимы

WHERE product_id BETWEEN 'a' AND 'z'  -- OK для строк

BETWEEN работает и с текстами (сравнение лексикографическое), и с датами, и с числами.

2. NOT BETWEEN тоже существует!

WHERE age NOT BETWEEN 18 AND 60;

Возвращает все строки, где значение меньше 18 или больше 60.

BETWEEN и NULL:
Если значение в проверяемом столбце — NULL, то условие BETWEEN вернёт не TRUE, а UNKNOWN, что эквивалентно тому, что строка не попадёт в результат.

SELECT * FROM employees
WHERE commission_pct BETWEEN 0.1 AND 0.3;  -- NULL значения исключаются

Чтобы явно включить такие строки, добавьте отдельное условие:

WHERE (commission_pct BETWEEN 0.1 AND 0.3 OR commission_pct IS NULL)

Итого:
1. BETWEEN включает границы, то есть условие BETWEEN 10 AND 20 вернёт и 10, и 20.
2. При работе с типами DATETIME следует быть особенно осторожным: BETWEEN '2024-01-01' AND '2024-12-31' не захватит все записи за 31 декабря, если в них указано время.
3. BETWEEN чувствителен к порядку значений: если нижняя граница больше верхней — результат будет пустой.
4. Не забывайте про NULL: записи с NULL в проверяемом столбце не попадут в выборку.
5. Альтернативный и более безопасный способ — использовать условия вида >= ... AND < ..., особенно при работе с временными диапазонами.

В целом, BETWEEN — удобный инструмент, если применять его правильно с учётом особенностей данных, с которыми вы работаете.

#BETWEEN #SQL

📱 Подписаться на канал | Курс автора по SQL DDL

🔗 Типы связей в реляционных БД: что, зачем и как?

Связи между таблицами — основа реляционных баз данных. Именно они позволяют создавать сложные модели данных. В теории баз данных тип связи (relationship type) — это формализованное описание ассоциации между двумя или более сущностями, указывающее, сколько экземпляров одной сущности может быть связано с экземплярами другой.

Я часто встречал базовые задания на изменение типа связи к оптимальному на собеседованиях для системных аналитиков. Разберёмся, какие бывают типы связей и как они реализуются.

1. Один к одному (1:1)
Каждая запись в таблице A соответствует ровно одной записи в таблице B и наоборот.

Пример:
Таблица users — содержит пользователей
Таблица passports — содержит паспортные данные
Каждому пользователю соответствует один паспорт, и каждый паспорт — одному пользователю.

Как реализуется:
Вторичная таблица (passports) содержит внешний ключ на users. id
Иногда связи 1:1 выделяются в отдельную таблицу для повышения безопасности или для разделения редко используемых данных

users            passports
+---------+      +------------+
| id (PK)   <--- |user_id (PK,FK)
| name           |passport_number 
+---------+      +------------+

Связь: users. id (PK) — 1:1 — passports.user_id (PK, FK)

2. Один ко многим (1:N)
Каждая запись в таблице A может соответствовать нескольким записям в таблице B, но каждая запись в таблице B соответствует только одной записи в A.

Пример:
Таблица departments — отделы
Таблица employees — сотрудники
Один отдел включает многих сотрудников, но каждый сотрудник относится к одному отделу.

Как реализуется:
Во второй таблице (employees) создаётся внешний ключ department_id, указывающий на departments. id первой таблицы. Это — самый распространённый тип связи.

departments         employees
+----------+        +-----------------+
| id (PK)    <----- | id (PK)         
| name              | name            
+----------+        | department_id(FK)
                    +-----------------+

Связь: departments. id (PK) — 1:N — employees.department_id (FK)

3. Многие ко многим (N:M)
Каждая запись в таблице A может соответствовать нескольким записям в таблице B, и наоборот.

Пример:
Таблица students — студенты
Таблица courses — курсы
Один студент записан на много курсов, и каждый курс включает много студентов.

Как реализуется:
1. Создаётся связующая таблица (например, student_course)
2. Она содержит два внешних ключа: student_id и course_id
3. Иногда добавляют дополнительные атрибуты

students      student_courses     
+---------+   +--------------+   
| id (PK)     | student_id(FK)   
| name        | course_id(FK)    
+---------+   +--------------+  

courses
+----------+
| id (PK) 
| noscript 
+----------+    

Связи:
1. students. id — 1:N — student_courses.student_id
2. courses. id — 1:N — student_courses.course_id

Коротко о реализации типов связей:
1. Один к одному — внешний ключ + уникальность
2. Один ко многим — внешний ключ в "многой" таблице
3. Многие ко многим — связующая таблица с 2 FK

Почему это важно?
Правильное определение и реализация связей:
1. Обеспечивает целостность данных
2. Обязательно для нормализации данных и устранения дублирования
4. Необходимо для поддержки бизнес-логики и целевого использования данных БД
5. Улучшает производительность (меньше дублирования, легче кэшировать);
6. Делает базу пригодной к росту (easy to scale).

Таким образом, связи в реляционной БД — это основа структурированной, надёжной и масштабируемой системы хранения данных.

#Ключи_и_связи_между_таблицами #SQL

📱 Подписаться на канал | Курс автора по SQL DDL

Сегодня столкнулся с гениальным описанием ошибки от СУБД.

Ок, понял 🗿

👩‍💻 Материализованные представления в SQL — погружаемся глубже в теорию

Привет, ранее уже базово затрагивал темы обычных представлений и материализованных, однако, сегодня словил желание остановится на MView по подробнее.

🧊 Что такое материализованное представление?
Материализованное представление (Materialized View) — это реальный объект базы данных, содержащий предрасчитанные данные.

В отличие от обычного VIEW, здесь:
➖Результат запроса сохраняется как отдельная таблица;
➖При чтении SELECT возвращает готовые, быстро читаемые данные;
➖Данные не обновляются автоматически (кроме некоторых случаев, например Oracle или с триггерами);
➖В основном требует явного ручного обновления — REFRESH.

🧊 Архитектурно материализованное представление — это:
➖ Текст SQL-запроса,
➖ Физическая таблица,
➖ Метаданные для обновления

🧊 Ключевые особенности
➖ Сохраняет данные — Да
➖ Обновляется автоматически — Нет (нужно REFRESH), за исключением случаев, которые рассмотрим ниже.
➖ Быстрое выполнение SELECT — Да
➖ Требует места на диске — Да

🧊 Общий синтаксис для создания материализованного представления:

CREATE MATERIALIZED VIEW имя_представления AS
SELECT ...
FROM ...
[WHERE ...]
[GROUP BY ...]
[ORDER BY ...];

Дополнительно можно указать:
WITH NO DATA — представление создаётся, но не заполняется (нужно REFRESH).
WITH DATA (по умолчанию) — данные вычисляются сразу при создании.

🧊 Допустим, у нас есть таблица orders, в которой хранятся заказы клиентов. Она может содержать такие поля:

-----------------------------
id            SERIAL
order_date    TIMESTAMP
customer_id   INT
amount        NUMERIC

Таблица обновляется постоянно: пользователи делают заказы, и каждая строка — это один заказ.

Часто требуется строить отчёты:
➖ Сколько заказов было каждый день
➖ Какова средняя сумма заказа за день

Запрос к такой таблице может быть тяжёлым, особенно если в orders миллионы строк. Поэтому создаём материализованное представление — заранее рассчитанный агрегат, чтобы анализировать среднюю сумму заказов по дням:

CREATE MATERIALIZED VIEW daily_avg_orders AS
SELECT
  order_date::date AS day,
  COUNT(*) AS total_orders,
  AVG(amount) AS avg_order_value
FROM orders
GROUP BY day
ORDER BY day;

Что делает это материализованное представление:
После выполнения CREATE MATERIALIZED VIEW — СУБД физически сохраняет результат запроса. Теперь мы можем быстро выполнять:

SELECT * FROM daily_avg_orders;

И получать результат без перерасчёта данных каждый раз.

🛠 Обновление (refresh)
Чтобы MView оставалось актуальным, его нужно обновлять вручную или автоматически.
При ручном обновлении, чтобы обновить данные в представлении после изменений в таблице orders, выполняем:

REFRESH MATERIALIZED VIEW daily_avg_orders;

Если при создании добавили WITH NO DATA — первый REFRESH обязателен.

Можно настроить автообновление через:
➖ Планировщик задач (например, cron);
➖ Расписание в СУБД (например, PostgreSQL pg_cron, Oracle DBMS_SCHEDULER);
➖ Использование триггеров или CDC

⚙️ Индексы и уникальность
Во многих СУБД материализованные представления поддерживают индексы, особенно если нужно обеспечить уникальность:

CREATE UNIQUE INDEX ON имя_представления(ключ);

Для того чтобы представление можно было обновлять инкрементально (а не полностью), часто требуется уникальный ключ.

🖥 Поддержка материализованных представлений в разных СУБД:
➖ PostgreSQL — Полноценная, ручное обновление
➖ Oracle — Продвинутая (есть автообновление, query rewrite)
➖ SQL Server — Аналог: Indexed Views
➖ MySQL — поддержки нет ❌
➖ ClickHouse — Аналог через MATERIALIZED VIEW с INSERT
➖ BigQuery — Поддержка с автообновлением

👾 Где это особенно нужно?
➖ Сложные отчёты (много JOIN, WHERE, GROUP BY) — хранит результат и не пересчитывает каждый раз
➖ BI и дашборды — быстрые ответы
➖ Разгрузка сервера — меньше нагрузки на исходные таблицы
➖ Разделение хранилищ — можно экспортировать MView в другие слои данных
➖ Медленные источники данных — данные приходят редко, но запросы часты.

Так что, материализуемся 🥁

#Views #Materialized_views #SQL

📱 Подписаться на канал | Курс автора по SQL DDL

Привет, нашел прикольную шпаргалку по основным джоинам от ByteByteGo.

Для инфо, JOIN в SQL — это операция, которая позволяет объединять данные из двух или более таблиц по связанному столбцу (обычно по ключу: первичному и внешнему).

Ранее писал посты про базовые джоины, однако, есть еще куча необычных join-операторов, конечно, в обиходе не все они используются часто, но кто знает, куда неофитов могут завести скитания по базе данных 😂

Скоро напишу пост про специфические джоины.
Лайк если надо 👩‍💻

👩‍💻 Специфические JOIN в SQL — тайные знания о соединениях

Итак, давайте разберем необычные виды join, про которые не так часто вспоминают, но они по своему прекрасны. Погнали.

💻 SELF JOIN — соединение таблицы с самой собой
SELF JOIN используется, когда нам нужно сравнить строки внутри одной и той же таблицы.

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

Где полезен:
➖Связи типа «сотрудник – менеджер»
➖Поиск пар с определённым соотношением
➖Сравнение записей между собой (например, временных интервалов)

Обратить внимание:
➖ Обязательно используйте алиасы (e1, e2) — иначе SQL не поймёт, откуда брать данные.
➖ Производительность при больших таблицах может быть тяжёлой без индексов.

💻 CROSS JOIN — декартово произведение
Каждая строка из первой таблицы соединяется со всеми строками второй.

SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

Где полезен:
➖ Генерация всех возможных комбинаций (цвет + размер, дата + товар и т.д.)
➖ Математические переборы (в BI/аналитике)

Пример:
Таблица colors:

red
blue

Таблица sizes:

S
M
L

Результат:

color  size
red  S
red  M
red  L
blue  S
blue  M
blue  L

Обратить внимание:
➖ Растёт в геометрической прогрессии — при 100 строках в каждой таблице будет 10 000 строк в результате (100×100 = 10 000 строк)!
➖ Обычно требует явной фильтрации после соединения.

💻 NATURAL JOIN — соединение по совпадающим колонкам
Автоматически соединяет таблицы по колонкам с одинаковыми именами и типами.

SELECT *
FROM orders
NATURAL JOIN customers;

Где полезен:
➖ Быстрые выборки, когда точно знаешь схему таблиц
➖ Временные аналитические задачи или прототипирование

Обратить внимание:
➖ SQL сам решает, по каким колонкам соединять! Если колонок с одинаковыми именами много, можно получить непредсказуемый результат.
➖ Лучше не использовать в проде без явного указания колонок (ON), особенно если схему может кто-то изменить, потому что если структура изменится, все сломается.

💻 ANTI JOIN — найти то, чего нет
Ищем строки в первой таблице, у которых нет пары во второй.
На SQL делается через:

SELECT c.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

ИЛИ:

SELECT c.id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Где полезен:
➖ Найти клиентов без заказов
➖ Найти товары без продаж
➖ Обнаружение "провалов" в связях

Обратить внимание:
➖ Вариант с NOT EXISTS работает быстрее при наличии индекса
➖ Убедитесь, что NULL действительно означает отсутствие связи, а не ошибку в данных

💻 SEMI JOIN — проверка на наличие
Найти строки из одной таблицы, у которых есть хотя бы одна пара в другой таблице, но не вытаскивать её. Забавно, но его синтаксис это JOIN без JOIN:

SELECT c.id
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

В отличие от INNER JOIN, мы не получаем orders.* — только customers.

Где полезен:
➖ Проверка: есть ли связи?
➖ Ускорение выборок, когда нужны только факты "да/нет"

Обратить внимание:
➖ Не получится использовать данные из второй таблицы (orders) в SELECT — она просто фильтрует
➖ Неявный тип соединения, часто путают с INNER JOIN
➖ Не вытаскивает данные из второй таблицы.

❗️ ANTI JOIN и SEMI JOIN — неофициальные типы (в ANSI SQL их нет), реализуются через LEFT JOIN + WHERE, EXISTS и т.п.

💻 Итого:
➖ SELF — Сравнивает записи одной таблицы
➖ CROSS — Сравнивает все на все, юзается для генерации комбинаций
➖ NATURAL — Сравнивает по одинаковым колонкам, используем при уверенности неизменности структуры таблиц или с ON
➖ ANTI — Выбирает всё, что не связано, используем для поиска отсутствий
➖ SEMI — Выбирает всё, что связано, пишется без оператора JOIN

Поддержка:
➖ SELF, CROSS, NATURAL — есть в большинстве СУБД (PostgreSQL, MySQL, Oracle, MS SQL)
➖ SEMI JOIN, ANTI JOIN — не имеют отдельного синтаксиса, реализуются через EXISTS / NOT EXISTS / LEFT JOIN + IS NULL
➖ В PostgreSQL и Oracle EXISTS работает особенно эффективно благодаря индексам.

#Join #SQL

📱 Подписаться на канал
📱Мы в VK
💻 Курс автора по SQL DDL

Пишу пост про SQLi*, до конца недели опубликую. В июле, скорее всего буду много говорить о безопасности БД, методах их защиты, плюс затронем выдачу и управление правами пользователей в СУБД и бэкапы.

*⚠️SQL-инъекция (SQL injection)— это атака, при которой злоумышленник внедряет вредоносный SQL-код через поля ввода, например, на сайте (логин, пароль, поисковая строка), чтобы получить несанкционированный доступ к базе данных.

Привет, вписался в конкурс каналов с качественным авторским контентом. Потом будут голосования за лучшие посты по номинациям среди каналов и все такое — здесь @tg_contest_main. Так что, не удивляйтесь, иногда буду просить вас голосовать))

Ну а вобще, я считаю, круто, что мой канал начинает участвовать в таких штуках, для меня это своеобразный признак оценки моих стараний (как и ваши реакции) 😁