Perché SQL BETWEEN è compreso piuttosto che semiaperto?

Penso che l'inclusione di BETWEEN sia più intuitiva (e apparentemente, così pure i progettisti SQL) di un intervallo semiaperto. Ad esempio, se dico "Scegli un numero compreso tra 1 e 10", la maggior parte delle persone includerà i numeri 1 e 10. L'intervallo aperto è in realtà particolarmente confuso per i non sviluppatori perché è asimmetrico. SQL viene utilizzato occasionalmente dai non programmatori per creare query semplici, e semantica semiaperta sarebbe stata molto più confusa per loro.

DOMANDA: Perché SQL BETWEEN è compreso?

RISPOSTA: Poiché i progettisti del linguaggio SQL hanno preso una decisione di progettazione scadente, in quanto non sono riusciti a fornire la sintassi che consentirebbe agli sviluppatori di specificare quale delle 4 varianti di BETWEEN (chiuso, semi-aperto-sinistro, semiaperto-destra o aperti) preferirebbero.

RACCOMANDAZIONE: a meno che / fino a quando lo standard SQL non venga modificato, non utilizzare BETWEEN per date / orari. Prendi invece l'abitudine di codificare i confronti della gamma DATE come condizioni indipendenti sui limiti iniziale e finale della gamma BETWEEN. Questo è un po 'prolisso, ma ti lascerà condizioni di scrittura intuitive (quindi meno probabilità di essere bacato) e chiaro agli ottimizzatori del database, consentendo di determinare i piani di esecuzione ottimali e gli indici da utilizzare.

Ad esempio, se la tua query accetta una specifica di giorno di input e deve restituire tutti i record che cadevano in quella data, avresti codice come:

• WHERE DATE_FIELD >= :dt AND DATE_FIELD < :dt+1

Provare a scrivere la logica utilizzando BETWEEN rischia problemi di prestazioni e / o codice buggato. Tre passi falsi comuni:

1) WHERE DATE_FIELD BETWEEN :dt AND :dt+1

Questo è quasi certamente un bug: l'utente si aspetta di vedere solo i record per una data specifica, tuttavia un giorno si concluderà con un rapporto contenente i record delle 12:00 del giorno successivo.

2) WHERE TRUNC(DATE_FIELD) = :dt

Fornisce risposta corretta, ma l'applicazione della funzione a DATE_FIELD renderà inutili la maggior parte dell'indicizzazione / statistica (anche se a volte gli amministratori di database cercheranno di aiutare aggiungendo indici basati sulle funzioni ai campi della data, continuando a bruciare man ore e spazio su disco e aggiungendo sovraccarico delle operazioni di IUD sul tavolo)

3) WHERE EVENT_DATE BETWEEN :dt AND :dt + 1-1/24/60/60

Tom Kyte, guru straordinario di Oracle, raccomanda questa soluzione meno elegante (IMO). Funziona benissimo finché non passi tutto il giorno a trovare "1-1 / 24/06/60" in una query che restituisce risultati incompleti ... o finché non lo utilizzi accidentalmente su un campo TIMESTAMP. Inoltre, è un po 'proprietario; compatibile con il tipo di dati DATE di Oracle (che tiene traccia del secondo), ma deve essere adattato alla precisione DATE / TIME dei diversi prodotti del database.

SOLUZIONE: Petizione del comitato ANSI SQL per migliorare le specifiche del linguaggio SQL modificando la sintassi BETWEEN per supportare la specifica di alternative al valore predefinito CLOSED / INCLUSIVE. Qualcosa del genere farebbe il trucco:

expr1 BETWEEN expr2 [ INCL[USIVE] | EXCL[USIVE] ] AND expr3 [ INCL[USIVE] | EXCL[USIVE] ]

Considera quanto è facile esprimere WHERE DATE_FIELD BETWEEN :dt INCLUSIVE AND :dt+1 EXCLUSIVE (o solo WHERE DATE_FIELD BETWEEN :dt AND :dt+1 EXCL )

Forse ANSI SQL: 2015?

Sia inclusivo ( a <= x <= b ) che esclusivo ( a < x < b ) sono quasi uguali, quindi quando si eseguivano gli standard si doveva semplicemente sceglierne uno. "Between" in inglese comune è tipicamente inclusivo, e un'istruzione SQL ha lo scopo di leggere simile a una frase inglese, quindi inclusiva è stata una scelta sensata.

L'operatore non si chiama ∩[a,b) , si chiama BETWEEN , quindi è considerevolmente più appropriato che la sua semantica sia quella della frase inglese "è compresa tra" di quelle della matematica predicato "è in intervallo semi-aperto".