Будь-який запит, створюваний для робіт в БД, спрощує допуск до потрібної інформації. В попередньому записі я говорив про загальних операторах умов. У цьому ж записі я поговорю про операторів, які дозволять створювати запити, здатні видати більш детальну інформацію, що цікавить, яку в той же, запитами з операторами AND, OR не так просто знайти.
Одним із спеціальних операторів є IN. даний оператор дозволяє задавати необхідний діапазон відображення потрібної інформації. Повернемося до даних по дожник
Debtors
| Num | Month | Year | Sname | City | Address | Debt |
| 0001 | Липень | 2012 | Іванов | Ставрополь | Ставропольська, 1 | 50000 |
| 0002 | грудень | 2019 | Кононов | Татарка | Заміська, 254 | 684068 |
| 0003 | Травень | 2013 | Ямшін | Михайловск | Сільська, 48 | 165840 |
| 0004 | Серпень | 2012 | дебати | Ставрополь | Центральна, 16 | 46580 |
| ... | ... | ... | ... | ... | ... | ... |
| 9564 | Березень | 2015 | Уліева | Дьомін | Міжнародна, 156 | 435089 |
| 9565 | Жовтень | 2012 | Павлова | Ставрополь | Вокзальна, 37 | 68059 |
| 9566 | січень | 2012 | Урюпа | Михайловск | Фонтанна, 19 | 51238 |
| 9567 | Листопад | 2017 | Вальетов | Татарка | Виїзна, 65 | 789654 |
Припустимо, необхідно вибрати всіх боржників міста Ставрополь або Татарка. За аналогією з попереднім записом, потрібно було б використовувати запит
SELECT *
FROM Debtors
WHERE City \u003d "Ставрополь"
OR City \u003d "Татарка";
Перш за все виходить громіздкий код. З використанням спеціальних операторів, можна отримати більш компактний код.
SELECT *
FROM Debtors
WHERE City IN ( "Ставрополь", "Татарка");
результатом буде
Простежимо логіку програми. З ключовими словами SELECT, FROM і WHERE. А ось далі з'являється оператор IN. Він задає програмі послідовність дій - необхідно переглянути інформацію БД, що містить в стовпці "City". А для відображення потрібно вибрати дані "Ставрополь" і "Татарка".
Розгляну приклад, в якому потрібно зробити відбір за певними сумами боргу.
SELECT *
FROM Debtors
WHERE Debt IN (435089, 789654, 684068);
Результатом буде наступне
Тобто оператор IN переглядає всю БД на наявність зазначених параметрів відбору інформації.
Інша працювати з використанням іншого спеціального оператора BETWEEN. якщо оператор IN розглядав інформацію з виключно зазначеними параметрами, то оператор BETWEEN - між певними діапазонами. Однак, не слід проводити аналогію між перекладом з англійської даного оператора і його дійсним призначенням. Якщо вказувати BETWEEN 1 AND 5, то це не означає, що справжньою будуть числа 2, 3 і 4. Даний оператор просто сприймається SQL як якесь значення, яке може знаходиться серед інших значень. На прикладі це буде виглядати наступним чином.
SELECT *
FROM Debtors
WHERE Debts BETWEEN 30000 AND 100000;
Результатом буде
Тобто SQL сприйняв оператор BETWEEN як будь-яке значення, що знаходиться в діапазоні від 30000 до 100000 по стовпчику "Debts".
Крім завдання приблизних діапазонів в цифровому вираженні, можна задавати алфавітні діапазони, в яких відображається інформація, що містить перші літери із зазначеного діапазону. Але, тут є один цікавий момент. Створимо наступний запит
SELECT *
FROM Debtors
WHERE Sname BETWEEN "І" AND "П";
Тоді з'являться такі дані
Закономірне питання: "А чому зі списку випали боржники, з прізвищем Прені та Павлова? Адже перші літери їх прізвищ входять в зазначений діапазон! "Букви входять, а прізвища - ні. Це пов'язано з тим, що мова SQL в подібного роду запитах, сприймає тільки ту довжину пошукових рядків, Які задані. Іншими словами, довжина рядка "П" в запиті становить один символ, а довжина рядка "дебатів" і "Павлова" в базі даних - п'ять і сім відповідно. А ось прізвище " Іванів "потрапляє в діапазон, оскільки діапазон починається з І, Як почала, довжиною від одного символу.
У цій статті ми розглянемо логічний оператор BETWEEN мови T-SQL, Ви дізнаєтеся, що це за оператор і як його використовувати. А також ми, звичайно ж, розберемо приклади SQL запитів із застосуванням оператора BETWEEN.
Оператор BETWEEN в мові T-SQL
BETWEEN - це логічний оператор мови T-SQL, який визначає діапазон для перевірки. Іншими словами, за допомогою BETWEEN ми можемо перевірити, чи входить значення в певний діапазон.
синтаксис
test_expression [NOT] BETWEEN begin_expression AND end_expression
опис аргументів
- test_expression - вираз, яке підлягає перевірці на приналежність до діапазону в межах від begin_expression до end_expression;
- begin_expression - вираз, що характеризує початку діапазону;
- end_expression - вираз, що характеризує кінець діапазону.
Всі вирази повинні мати однаковий тип даних.
Як результат оператор BETWEEN повертає значення (Boolean):
- TRUE - якщо значення аргументу test_expression більше або дорівнює значенню begin_expression, при цьому менше або дорівнює значенню end_expression, тобто test_expression входить в діапазон від begin_expression до end_expression;
- FALSE - якщо значення аргументу test_expression не входить в діапазон від begin_expression до end_expression.
За допомогою ключового слова NOT ми можемо інвертувати результат предиката, іншими словами, NOT BETWEEN означає, що ми хочемо перевірити значення на предмет того, що воно не входить в заданий діапазон.
В даному випадку оператор NOT BETWEEN поверне TRUE, якщо значення аргументу test_expression не входить в діапазон від begin_expression до end_expression.
У разі якщо в BETWEEN хоча б один з виразів буде містити значення NULL, результат буде не визначений.
Замітка! Нагадую, що необхідно завжди пам'ятати про те, що існують такі значення як NULL (це не порожньо і не 0, це відсутність значення), які обробляється SQL сервером по-особливому, результат SQL запита за участю NULL-значень може бути неочевидний і непередбачуваний.
Замітка! Якщо Ви початківець програміст і хочете вивчити мову T-SQL з нуля, то рекомендую почитати мою книгу «Шлях програміста T-SQL», в ній я детально розповідаю про основи мови T-SQL, а також приділяю чимало уваги і просунутим конструкціям.
Вихідні дані для прикладів
Як сервер у мене виступає Microsoft SQL Server 2016 Express. Для прикладу давайте уявимо, що у нас є таблиця TestTable, і в ній містяться такі дані ( перелік товарів із зазначенням ціни). Рядки в таблицю я додаю за допомогою інструкції INSERT INTO.
Створення таблиці CREATE TABLE TestTable (IDENTITY (1,1) NOT NULL, (100) NOT NULL, NULL) GO --Добавленіе рядків в таблицю INSERT INTO TestTable (ProductName, Price) VALUES ( " Системний блок", 300), (" Монітор ", 200), (" Клавіатура ", 100), (" Миша ", 50), (" Принтер ", 200), (" Сканер ", 150), (" Телефон ", 250), ( "Планшет", 300) GO --Виборка даних SELECT * FROM TestTable
Приклад використання оператора BETWEEN в секції WHERE
Давайте напишемо SQL запит, який виведе нас усі товари, ціна яких знаходиться в діапазоні від 100 до 200 рублів включно.
Умова із застосуванням оператора BETWEEN SELECT ProductID, ProductName, Price FROM TestTable WHERE Price BETWEEN 100 AND 200
Даний запит ми могли б написати і без використання оператора BETWEEN, наприклад, наступний SQL запит абсолютно еквівалентний.
Умова із застосуванням операторів порівняння SELECT ProductID, ProductName, Price FROM TestTable WHERE Price\u003e \u003d 100 AND Price<= 200
Як бачимо, результат однаковий, але у випадку з BETWEEN умова виглядає більш наочно і зрозуміло, до того ж вираз для перевірки (Price) ми записали всього один раз, у випадку з операторами порівняння два.
Приклад використання оператора BETWEEN в умовній конструкції IF
Оператор BETWEEN можна використовувати не тільки в секції WHERE, але і в інших конструкціях мови T-SQL, наприклад, в умовній конструкції IF. У наступному прикладі ми перевіримо змінну @TestVar на предмет того, чи входить значення цієї змінної в діапазон від 1 до 10 і, якщо входить, виконаємо потрібне нам дії, я для прикладу просто пошлю запит SELECT.
DECLARE @TestVar INT \u003d 5 IF @TestVar BETWEEN 1 AND 10 SELECT "Мінлива @TestVar знаходиться в діапазоні від 1 до 10" AS [Результат]
Приклад використання оператора NOT BETWEEN
Зараз давайте напишемо запит із застосуванням ключового слова NOT, наприклад, нам потрібно вивести всі товари, ціна яких не входить в діапазон від 100 до 200 рублів ( еквівалентний приклад з використанням операторів порівняння я також додаю).
Умова із застосуванням оператора NOT BETWEEN SELECT ProductID, ProductName, Price FROM TestTable WHERE Price NOT BETWEEN 100 AND 200 --Условіе із застосуванням операторів порівняння SELECT ProductID, ProductName, Price FROM TestTable WHERE Price< 100 OR Price > 200
В даному випадку нам вивелися усі товари, ціна яких менше 100 або більше 200 рублів.
У мові T-SQL крім оператора BETWEEN існують і інші логічні оператори, наприклад, оператор EXISTS, який в деяких випадках буває дуже корисний, його ми також розглядали на цьому сайті.
У мене на це все, пока!
Оператор BETWEEN виконує булеву перевірку значення на відповідність діапазону значень. Оператор повертає TRUE, якщо значення входить в діапазон, і FALSE, якщо значення не входить в діапазон. Якщо будь-яке значення діапазону одно NULL (невідомо), то результат буде NULL.
Оператор EXISTS семантично еквівалентний оператору ANY / SOME.
Синтаксис SQL 2003
SELECT * WHERE вираз BETWEEN ніжняя_граніца AND верхняя_граніцаКлючові слова
WHERE вираз
Перевіряє скалярний вираз (наприклад, стовпець) на відповідність діапазону значень, що лежать між верхней_граніцей і ніжней_граніцей. BETWEEN нижня _граніца AND верхняя_граніца
Порівнює вираз з ніжней_граніцей і верхней_граніцей. Порівняння включає крайні значення, тобто це все одно що «де вираз й [не] більше або дорівнює ніжней_граніце і менше або дорівнює верхней_граніцей\u003e.
Загальні правила
Оператор BETWEEN використовується для перевірки вираження на відповідність діапазону значень. Оператор BETWEEN може використовуватися з будь-яким типом даних, за винятком BLOB, CLOB, NCLOB, REF і ARRAY.
Наприклад, нам потрібно побачити номери творів (title_id), для яких обсяги продажів з початку року (ytd_sales) лежать в діапазоні від 10000 до 20000.
SELECT title_id FROM titles WHERE ytcLsales BETWEEN 10000 AND 20000
Оператор BETWEEN включає межі діапазону. В даному випадку в результат будуть включені значення 10000 і 20000. Якщо вам потрібно провести пошук, не включаючи межі діапазону, ви повинні використовувати символи «більше» (\u003e) і менше (<).
SELECT title_id FROM titles WHERE ytd.sales\u003e 10000 AND ytd_sales< 20000
Оператор NOT дозволяє проводити пошук за межами діапазону, зазначеного в операторі BETWEEN. Так, ви можете знайти номери всіх творів (title_id), які публікувалися не в 2003 році.
Деякі програмісти дуже вибагливе ставляться до того, як в пропозиціях WHERE використовується ключове слово AND. Щоб незнайомий з кодом людина не подумав, що оператор AND, який використовується в операторі BETWEEN, є логічним оператором, ви можете укласти все пропозицію BETWEEN в дужки.
SELECT title_id FROM titles WHERE (ytd_sales BETWEEN "10000 AND 20000) AND pubdate\u003e \u003d" 1991-06-12 00: 00: 00.000 "
Відмінності в реалізаціях на різних платформах
Всі платформи підтримують оператор BETWEEN в тому вигляді, як це описано вище.
При роботі з реляційними СУБД, в яких дані зберігаються в табличному вигляді, користувачі часто стикаються із завданням вибірки значень, що входять (не входять) в певний діапазон. Мова SQL дозволяє задати безліч, якому має (не повинно) належати значення різними варіантами - оператором In, оператором Like, комбінацією умов більше - менше, а також оператором SQL Between. Опис і приклади в даній статті будуть присвячені останнім варіантом.
Оператор «Між» в SQL: синтаксис, обмеження
Дослівно оператор between SQL так і перекладається - «між». Його використання дозволяє задати обмеження «Від і До» до конкретного поля, і якщо чергове значення потрапить в діапазон, то предикат прийме значення «Істина», і значення потрапить в підсумкову вибірку.
Синтаксис у оператора гранично простий:
Where t1.n between 0 and 7 |
Як бачимо, після ключового слова between необхідно вказати значення нижньої межі діапазону, потім AND і значення верхньої межі.
Перерахуємо, з якими типами даних може працювати оператор between SQL:
- З числами - цілими і дробовими.
- З датами.
- З текстом.
У даного оператора between SQL є певні особливості. Познайомимося з ними:
- При роботі з числами і датами значення обмежень «Від і До» включаються до вибірки.
- Значення нижньої межі діапазону має бути менше значення верхньої межі, інакше не буде виведено нічого, адже умова логічно не вірно. Особливо уважним треба бути, коли замість конкретних значень в умова включаються змінні.
При роботі з текстом значення верхньої межі діапазону не буде включено до вибірки, якщо воно не зазначено гранично точно. У наступних розділах розглянемо цю особливість докладніше.
Вибірка чисел і дат в певному діапазоні
Підготуємо таблицю з даними по менеджерам, які працюють в організації. Таблиця буде мати наступну структуру:
ім'я поля | Тип даних | опис |
Унікальний ідентифікатор співробітника |
||
текстовий | Прізвище співробітника |
|
текстовий | ім'я співробітника |
|
По батькові | текстовий | По батькові співробітника |
текстовий | Пол співробітника (М / Ж) |
|
Дата прийому | Дата час | Дата прийому співробітника на роботу |
Чісло_детей | числовий | Кількість дітей у співробітника |
Заповнимо таблицю такими даними:
код | Прізвище | ім'я | По батькові | Підлога | Дата прийому | Чісло_детей |
Александрова | Миколаївна | |||||
Степанович | ||||||
Виноградов | Павлович | |||||
Олександр | Борисович | |||||
Вишняков | Олександрович | |||||
Тропніков | Сергійович | |||||
перлів | Васильович | |||||
Костянтинівна | ||||||
Миколайович |
Складемо between, який допоможе нам вибрати всіх співробітників, що мають 2 або 3 дитини:
Результатом стане три рядки з даними по співробітниках з прізвищами Шумилин, Тропніков і Авдєєва.
Тепер виберемо співробітників, прийнятих з 1 січня 2005 року по 31 грудня 2016 року. Слід зазначити, що різні СУБД по-різному дозволяють записувати в умови дати. У більшості випадків дату просто примусово приводять до вигляду день-місяць-рік (або як зручніше) і записують в одинарні або В СУБД дату укладають в знак «#». Виконаємо приклад якраз на її основі:
SELECT Менеджери. *, Менеджери.Дата_пріема FROM Менеджери WHERE Менеджери. Дата_пріема Between # 1/1/2005 # And # 31/12/2016 # |
Результатом стануть п'ять співробітників, прийнятих на роботу в зазначений період включно.
Робота в between з рядками
Дуже часте завдання, яку доводиться вирішувати при роботі з прізвищами співробітників, - це необхідність вибрати тільки тих, чиї прізвища починаються на певну букву. Спробуємо і ми виконати запит і вибрати співробітників, чиї прізвища починаються на прізвища з А до В:
Результат наступний:
Як бачимо, двоє співробітників, що мають прізвище на букву В, до списку не потрапили. З чим це пов'язано? Справа в тому, яким саме чином оператор порівнює рядки нерівної довжини. Рядок «В» коротше рядки «Виноградів» і доповнюється пробілами. Але при сортуванні за алфавітом прогалини виявляться випереджаючими символами, і прізвище в вибірку не потрапить. Різні СУБД по-різному пропонують вирішувати дану проблему, Але найчастіше найпростіше для надійності вказувати наступну букву алфавіту в діапазоні:
При виконанні даного запиту результат нас повністю задовольнить.
Такий нюанс існує тільки при роботі з символьними даними, однак він показує, що при роботі навіть з такими простими операторами, як between, треба бути уважними.
Визначає, чи потрапляє значення виразу в зазначений інтервал. Даний оператор можна використовувати в інструкціях SQL.
синтаксис
вираз [Not] Betweenзначення1Andзначення2
синтаксис оператора Between ... And включає в себе наступні компоненти:
зауваження
Якщо значення компонента вираз знаходиться між значеніем1 і значеніем2 (Включно), оператор Between ... And повертає значення True; в іншому випадку повертається значення False. включення логічного оператора Not призводить до перевірки протилежної умови (що передбачає, що компонент вираз знаходиться поза інтервалу, визначеного компонентами значення1 і значення2).
За допомогою Between ... And можна визначити, чи потрапляє значення поля в зазначений числовий діапазон. У прикладі нижче визначається, чи був замовлення відправлено за адресою з поштовим індексом із заданого діапазону. Якщо поштовий індекс знаходиться в діапазоні між 98 101 і 98199, функція IIf повертає Local (Місцевий). В іншому випадку вона повертає значення Nonlocal (Не місцевий).
SELECT IIf (PostalCode Between 98101 And 98199, "Local", "Nonlocal") FROM Publishers
якщо вираз, значення1 або значення2 має значення Null, Between ... And повертає значення Null.
Оскільки символи узагальнення, наприклад зірочка (*), вважаються літералами, їх не можна використовувати в операторі Between ... And. Наприклад, не можна використовувати вирази виду 980 * і 989 *, щоб знайти всі індекси, що починаються з чисел в інтервалі від 980 до 989. Існує два способи вирішення цього завдання. Можна додати в запит вираз, яке передає оператору Between ... And перші три символи текстового поля. Інший варіант - додати до нижньої і верхньої меж перевіряється інтервалу додаткові цифри, в даному випадку - від 98000 до 98999 або від 98000 до 98999-9999, якщо використовуються розширені поштові індекси (У нижніх індексів необхідно опустити -0000, так як в противному випадку буде пропущений індекс 98000, якщо в одних індексах є розширення, а в інших немає).
