Як SQL робить текстовий пошук, порівняння

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

В даному випадку, нас цікавить, а чому SQL при порівнянні числа 20 з 200 скаже, що 20 меньше за 200.

select 20>200
>>
FALSE

Але при перевірці текстового значення ’13’ та ‘100’ видасть, що ’13’ більше, цікаво, правда ?!

select '13'>'100'
>>
TRUE

На перших порах здається, що це якась помилка, бо всі знають що 13 меньше за 100, але вся причина в типі значення, а саме в TEXT/STRING/VARCHAR – текстовому типі значення/поля. Тому виникає питання так як воно ж тоді працює?! Давайте спробуємо це поступово розібрати, починаючи з того як взагалі СУБД “двіжок” обробляє такі дії.

Розглянемо оператори в WHERE:

• Порівняння: =, <, >, >=, <=, <>
  Наприклад, знайти продукти дорожчі за 100 одиниць
  SELECT * FROM products WHERE price > 100;
• Логічні оператори: AND, OR, NOT
  Наприклад, знайти продукти, які коштують більше 100, але менше 500
  SELECT * FROM products WHERE price > 100 AND price < 500;
• Шаблони: LIKE для пошуку за шаблоном (наприклад, рядки, що починаються з “A”)
  SELECT * FROM customers WHERE name LIKE 'A%';
• Нульові значення: IS NULL чи IS NOT NULL
SELECT * FROM orders WHERE discount IS NULL;

І в цій статті ми детально розглянемо тільки два порівння з текстовими типами даних, та з цілочисленими.

Як SQL порівнює числа?

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

1. Перевірка типів даних
   Коли SQL зустрічає порівняння, наприклад:
   SELECT 100 > 101;
   СУБД спочатку визначає типи операндів(значень), якщо типи однакові, порівняння виконується без додаткових дій, якщо різні, то можливо виконання самою СУБД, так звоного “неявного приведення”.
   Про неявне приведення, не будемо розглдяати в цій статті, але якщо стисло, деякі СУБД, як приклад MySQL, можуть приводити значення в однаковий тип (тектове значення ‘123’ з порівнянням з числом 120, буде переведено в INT, але так роблять не всі СУБД).
   
2. Виконання порівняння
   SQL порівнює значення чисел у двійковому представленні:
   Наприклад, 100 і 101 у двійковій формі:
   100 = 01100100
   101 = 01100101
   Порівняння виконується побітно, починаючи з найбільш значущого біта (зліва направо).
   Якщо значення одного операнду більше за інше → TRUE.
   Якщо менше → FALSE.
   Якщо рівні → FALSE для операторів <, >, але TRUE для =.
   
   Тобто він буде порівнювати кожен біт від кожного значення(операнда), 0 більше ніж 0 >> так, тоді 1 більше 1 >> так, тоді далі поки не отримуємо FALSE… і доходимо до останього значення 0 та 1 отримуємо FALSE, що і віддає нам як результат.
   
   Тепер перейдемо до текстового порівння.

Як працює пошук текстових значень в SQL?

Пошук текстових значень у SQL базується на лексикографічному (алфавітному) порівнянні. Цей процес визначає, як символи в текстових рядках порівнюються один з одним. Робота з текстовими даними залежить від кількох важливих факторів: кодування символів, налаштувань сортування (collation), довжини рядків і функцій пошуку.

1. Лексикографічне порівняння

• SQL порівнює текстові значення, починаючи з першого символу кожного рядка.
• Порівняння виконується за ASCII-значеннями (або Unicode-значеннями, якщо використовується Unicode-кодування). Приклад ASCII таблиці взятий з lookuptables.com
  
• Наприклад, у кодуванні ASCII:
  • A має значення 65.
  • B має значення 66.
    Таким чином, "A" < "B".

2. Collation (налаштування сортування)

• Collation визначає, як порівнюються та сортуються текстові дані (з урахуванням регістру, мови, акцентів тощо).
• Приклад: у багатьох collation "A" дорівнює "a", але це може змінюватися.

3. Довжина рядків

• Якщо текстові рядки різної довжини, коротший рядок може вважатися “меншим”, якщо довші символи впливають на результат.
• Наприклад:
  "abc" < "abcd", тому що "abc" вичерпується раніше.

4. Кодування символів

• SQL використовує кодування для інтерпретації символів у тексті (наприклад, UTF-8, UTF-16).
• Символи в Unicode порівнюються на основі їхніх значень у таблиці Unicode.

Тобто, у кожного тектстового символа є закріплена своя цифра і вже саме ця цифра порівнюється в СУБД, цей код можливо отримати через вбудовану функцію ASCII().

select ascii('1') code_of_1,
       ascii('a') code_of_a,
       ascii('B') code_of_B
>>
+---------+--+
|code_of_1|49|
+---------+--+
|code_of_a|97|
+---------+--+
|code_of_b|66|
+---------+--+

А тепер хочу ще підкреслити пошук тектових дат, в цілому із-за цього питання і вирішив написати статтю).

Як SQL обробляє текстові дати?

Текстові дати — це дати, представлені як рядки, наприклад, '2024-12-01'. Пошук або порівняння таких дат відбувається за стандартним алгоритмом порівняння рядків, тобто символ за символом зліва направо, відповідно до їхнього числового значення в кодуванні.

Але чому пошук правильний?

Стандартизований формат ISO 8601:
Якщо текстова дата записана у форматі YYYY-MM-DD (рік-місяць-день), то її лексикографічний порядок збігається з хронологічним. Це означає, що порівняння рядків автоматично враховує часову послідовність.
Наприклад:

• '2023-01-01' < '2024-01-01' → TRUE
• '2024-12-01' > '2024-11-30' → TRUE

Посимвольне порівняння:
У цьому форматі порівняння починається з року (найстарший компонент), потім переходить до місяця і дня. Завдяки цьому:

• '2024' > '2023' (рік)
• '12' > '11' (місяць)
• '01' > '30' (день)

Це працює тому, що кожен компонент дати (рік, місяць, день) представлений числом з однаковою кількістю символів.
А якщо дата буде часткова або в іншому форматі?!

Невірний формат дати

Якщо текстові дати представлені не в стандартному форматі (наприклад, DD-MM-YYYY), лексикографічний порядок не збігається з хронологічним:

SELECT '31-12-2024' > '01-01-2024'
>> 
TRUE

Це неправильно, оскільки порівняння починається з 31 та 01, і SQL вважає першу дату більшою. Щоб уникнути цієї проблеми, використовуйте формат YYYY-MM-DD.

Порівняння часткових дат

Якщо дати містять лише частину інформації, наприклад, рік і місяць, SQL все одно виконує коректне порівняння:

SELECT '2024-12' > '2024-01'
>>
TRUE

Однак для точності краще завжди використовувати повний формат.

Текстові дати з різною довжиною

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

SELECT '2024-1-1' > '2024-01-01'
>>
FALSE

SQL порівнює символи і визначає, що '1' менше '0'. Завжди використовуйте нулі для вирівнювання

Надіюсь ця стаття була корисна або якщо виникли якісь додаткові питання, долучайтесь до нашої української спільноти, завжди всі раді!

🚀Долучайтесь до нашої спільноти Telegram:

• Data Life UA
• Group Data Analysis UA
• Group Data Engineering UA

🚀Долучайтесь до нашої спільноти FaceBook:

• Data Life UA

🚀Долучайтесь до нашої спільноти Twiter X:

• X