SQL: BETWEEN vs <= i> =

W SQL Server 2000 i 2005:

• jaka jest różnica między tymi dwoma WHEREklauzulami?
• którego powinienem używać w jakich scenariuszach?

Zapytanie 1:

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'

Zapytanie 2:

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate >='10/15/2009'
  AND EventDate <='10/18/2009'

(Edycja: pierwotnie brakowało drugiej Eventdate, więc zapytanie było błędne pod względem składniowym)

Są identyczne: BETWEENto skrót dla dłuższej składni w pytaniu.

Użyj alternatywnej dłuższej składni, gdzie BETWEENnie działa, np

Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/18/2009'

(Uwaga <raczej niż <=w drugim stanie).

Oni są tacy sami.

Jedną rzeczą, na którą należy uważać, jest to, że jeśli używasz tego przeciwko DATETIME, data końcowa będzie zgodna z początkiem dnia:

<= 20/10/2009

to nie to samo co:

<= 20/10/2009 23:59:59

(to byłoby dopasować przeciwko <= 20/10/2009 00:00:00.000)

Chociaż BETWEENjest łatwy do odczytania i utrzymania, rzadko polecam jego użycie, ponieważ jest to przedział zamknięty i jak wspomniano wcześniej może to być problem z datami - nawet bez składników czasowych.

Na przykład, gdy mamy do czynienia z danymi miesięcznymi, często porównuje się daty BETWEEN first AND last, ale w praktyce zwykle jest to łatwiejsze do zapisania dt >= first AND dt < next-first(co również rozwiązuje kwestię części czasu) - ponieważ określenie lastzwykle jest o jeden krok dłuższe niż ustalenie next-first(odejmując dzień) .

Ponadto inną przeszkodą jest to, że dolne i górne granice muszą być określone we właściwej kolejności (tj BETWEEN low AND high.).

Zwykle nie ma różnicy - BETWEENsłowo kluczowe nie jest obsługiwane na wszystkich platformach RDBMS, ale jeśli tak jest, oba zapytania powinny być identyczne.

Ponieważ są identyczne, tak naprawdę nie ma różnicy pod względem szybkości ani czegokolwiek innego - użyj tego, który wydaje ci się bardziej naturalny.

Jak wspomnieli @marc_s, @Cloud i in. są w zasadzie takie same dla zamkniętego zakresu.

Jednak wszelkie ułamkowe wartości czasu mogą powodować problemy z zamkniętym zakresem (większy lub równy i mniejszy lub równy ) w przeciwieństwie do półotwartego zakresu (większy lub równy i mniejszy niż ) z wartością końcową po ostatnia możliwa chwila.

Aby uniknąć sytuacji, w której zapytanie powinno zostać przepisane jako:

SELECT EventId, EventName
  FROM EventMaster
 WHERE (EventDate >= '2009-10-15' AND
        EventDate <  '2009-10-19')    /* <<<== 19th, not 18th */

Ponieważ BETWEENnie działa w półotwartych odstępach czasu, zawsze uważnie przyglądam się każdemu zapytaniu o datę / godzinę, które go używa, ponieważ prawdopodobnie jest to błąd.

Mam pewne preferencje, BETWEENponieważ od razu daje czytelnikowi do zrozumienia, że sprawdzasz jedno pole dla zakresu . Jest to szczególnie ważne, jeśli masz podobne nazwy pól w tabeli.

Jeśli, powiedzmy, nasza tabela ma zarówno a, jak transactiondatei a transitiondate, jeśli czytam

transactiondate between ...

Wiem od razu, że oba końce testu są przeciwko tej jednej dziedzinie.

Jeśli czytam

transactiondate>='2009-04-17' and transactiondate<='2009-04-22'

Muszę poświęcić dodatkową chwilę, aby upewnić się, że oba pola są takie same.

Ponadto, gdy zapytanie jest z czasem edytowane, niechlujny programista może rozdzielić te dwa pola. Widziałem wiele zapytań, które mówią coś w rodzaju

where transactiondate>='2009-04-17'
  and salestype='A'
  and customernumber=customer.idnumber
  and transactiondate<='2009-04-22'

Jeśli spróbują tego z BETWEEN, oczywiście, będzie to błąd składniowy i szybko naprawiony.

Myślę, że jedyną różnicą jest ilość cukru składniowego w każdym zapytaniu. BETWEEN to po prostu sprytny sposób powiedzenia dokładnie tego samego, co drugie zapytanie.

Mogą istnieć pewne specyficzne różnice w RDBMS, których nie jestem świadomy, ale tak naprawdę nie sądzę.

Logicznie rzecz biorąc, nie ma żadnej różnicy. Jeśli chodzi o wydajność, zazwyczaj w większości systemów DBMS nie ma żadnej różnicy.

Zobacz tę doskonałą blogu od Aaron Bertrand o których warto zmienić format string i jak wartości graniczne są obsługiwane w zapytaniach zakres dat.

Zastrzeżenie: wszystko poniżej jest tylko anegdotyczne i zaczerpnięte bezpośrednio z mojego osobistego doświadczenia. Każdy, kto czuje się na siłach, aby przeprowadzić bardziej rygorystyczną analizę empiryczną, może ją przeprowadzić i głosować przeciw. Zdaję sobie również sprawę, że SQL jest językiem deklaratywnym i nie powinieneś zastanawiać się, JAK przetwarzany jest twój kod, kiedy go piszesz, ale ponieważ cenię swój czas, tak.

Istnieje nieskończona liczba logicznie równoważnych instrukcji, ale rozważę trzy (ish).

Przypadek 1: Dwa porównania w standardowej kolejności (ustalona kolejność oceny)

A> = MinBound AND A <= MaxBound

Przypadek 2: cukier syntaktyczny (kolejność oceny nie jest wybierana przez autora)

POMIĘDZY MinBound I MaxBound

Przypadek 3: Dwa porównania w uporządkowanej kolejności (kolejność oceny wybrana podczas pisania)

A> = MinBound AND A <= MaxBound

Lub

A <= MaxBound AND A> = MinBound

Z mojego doświadczenia wynika, że ​​przypadek 1 i przypadek 2 nie mają żadnych spójnych ani zauważalnych różnic w wydajności, ponieważ nie znają zestawu danych.

Jednak przypadek 3 może znacznie skrócić czas wykonywania. W szczególności, jeśli pracujesz z dużym zestawem danych i zdarza się, że masz pewną heurystyczną wiedzę na temat tego, czy A jest bardziej prawdopodobne, że będzie większe niż MaxBound, czy mniejsze niż MinBound , możesz znacznie poprawić czasy wykonywania, używając przypadku 3 i porządkując porównania odpowiednio.

Jednym z moich przypadków użycia jest odpytywanie dużego historycznego zbioru danych z nieindeksowanymi datami dla rekordów w określonym przedziale czasu. Pisząc zapytanie, będę wiedział, czy istnieje więcej danych PRZED określonym interwałem lub PO określonym interwale i mogę odpowiednio uporządkować moje porównania. Czas wykonania został skrócony nawet o połowę w zależności od rozmiaru zbioru danych, złożoności zapytania i liczby rekordów przefiltrowanych przez pierwsze porównanie.

W tym scenariuszu col BETWEEN ... AND ...i col <= ... and col >= ...są równoważne.

SQL Standard definiuje również predykat T461 Symmetric BETWEEN :

 <between predicate part 2> ::=
 [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ]
 <row value predicand> AND <row value predicand>

Transact-SQL nie obsługuje tej funkcji.

BETWEENwymaga sortowania wartości. Na przykład:

SELECT 1 WHERE 3 BETWEEN 10 AND 1
-- no rows

<=>

SELECT 1 WHERE 3 >= 10 AND 3 <= 1
-- no rows

Z drugiej strony:

SELECT 1 WHERE 3 BETWEEN SYMMETRIC 1 AND 10;
-- 1

SELECT 1 WHERE 3 BETWEEN SYMMETRIC 10 AND 1
-- 1

Działa dokładnie tak, jak normalnie, BETWEENale po posortowaniu wartości porównawczych.

db <> fiddle demo