BETWEEN
Ïðåäèêàò BETWEEN çàäàåò äèàïàçîí çíà÷åíèé, äëÿ êîòîðîãî âûðàæåíèå ïðèíèìàåò çíà÷åíèå true. Ðàçðåøåíî òàêæå èñïîëüçîâàòü êîíñòðóêöèþ NOT BETWEEN.
SELECT first_name, last_name, salary
FROM employee
WHERE salary BETWEEN 20000 AND 30000
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ,
ãîäîâàÿ çàðïëàòà êîòîðûõ
áîëüøå 20000 è ìåíüøå 30000
FIRST_NAME LAST_NAME SALARY
=============== ========== ===============
Ann Bennet 22935.00
Kelly Brown 27000.00
Òîò æå çàïðîñ ñ èñïîëüçîâàíèåì îïåðàòîðîâ ñðàâíåíèÿ áóäåò âûãëÿäåòü ñëåäóþùèì îáðàçîì:
SELECT first_name, last_name, salary
FROM employee
WHERE salary >= 20000
AND salary <= 30000 ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ,
ãîäîâàÿ çàðïëàòà êîòîðûõ
áîëüøå 20000 è ìåíüøå 30000
FIRST_NAME LAST_NAME SALARY
=============== ========== ===============
Ann Bennet 22935.00
Kelly Brown 27000.00
Çàïðîñ ñ ïðåäèêàòîì BETWEEN ìîæåò èìåòü ñëåäóþùèé âèä:
SELECT first_name, last_name, salary
FROM employee
WHERE last_name BETWEEN "Nelson" AND "Osborne"
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ,
ôàìèëèè êîòîðûõ íà÷èíàþòñÿ
ñ “Nelson”
è çàêàí÷èâàþòñÿ “Osborne”
FIRST_NAME LAST_NAME SALARY
=============== =============== ================
Robert Nelson 105900.00
Carol Nordstrom 42742.50
Sue Anne O'Brien 31275.00
Pierre Osborne 110000.00
Çíà÷åíèÿ, îïðåäåëÿþùèå íèæíèé è âåðõíèé äèàïàçîíû, ìîãóò íå ÿâëÿòüñÿ ðåàëüíûìè âåëè÷èíàìè èç áàçû äàííûõ. È ýòî î÷åíü óäîáíî - âåäü ìû íå âñåãäà ìîæåì óêàçàòü òî÷íûå çíà÷åíèÿ äèàïàçîíîâ!
SELECT first_name, last_name, salary
FROM employee
WHERE last_name BETWEEN "Nel" AND "Osb"
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ,
ôàìèëèè êîòîðûõ íàõîäÿòñÿ
ìåæäó “Nel” è “Osb”
FIRST_NAME LAST_NAME SALARY
=============== =============== ================
Robert Nelson 105900.00
Carol Nordstrom 42742.50
Sue Anne O'Brien 31275.00
 äàííîì ïðèìåðå çíà÷åíèé “Nel” è “Osb” â áàçå äàííûõ íåò. Îäíàêî, âñå ñîòðóäíèêè, âõîäÿùèå â äèàïàçîí, â íèæíåé ÷àñòè êîòîðîãî íà÷àëî ôàìèëèé ñîâïàäàåò ñ “Nel” (ò.å. âûïîëíÿåòñÿ óñëîâèå “áîëüøå èëè ðàâíî”), à â âåðõíåé ÷àñòè ôàìèëèÿ íå áîëåå “Osb” (ò.å. âûïîëíÿåòñÿ óñëîâèå “ìåíüøå èëè ðàâíî” - à èìåííî “O”, “Os”, “Osb”), ïîïàäóò â âûáîðêó. Îòìåòèì, ÷òî ïðè âûáîðêå ñ èñïîëüçîâàíèåì ïðåäèêàòà BETWEEN ïîëå, íà êîòîðîå íàêëàäûâàåòñÿ äèàïàçîí, ñ÷èòàåòñÿ óïîðÿäî÷åííûì ïî âîçðàñòàíèþ.
Ïðåäèêàò BETWEEN ñ îòðèöàíèåì NOT (NOT BETWEEN) ïîçâîëÿåò ïîëó÷èòü âûáîðêó çàïèñåé, óêàçàííûå ïîëÿ êîòîðûõ èìåþò çíà÷åíèÿ ìåíüøå íèæíåé ãðàíèöû è áîëüøå âåðõíåé ãðàíèöû.
SELECT first_name, last_name, hire_date
FROM employee
WHERE hire_date NOT BETWEEN "1-JAN-1989" AND "31-DEC-1993" ïîëó÷èòü ñïèñîê ñàìûõ “ñòàðûõ”
è ñàìûõ “ìîëîäûõ” (ïî âðåìåíè
ïîñòóïëåíèÿ íà ðàáîòó)
ñîòðóäíèêîâ
FIRST_NAME LAST_NAME HIRE_DATE
=============== ================ ===========
Robert Nelson 28-DEC-1988
Bruce Young 28-DEC-1988
Pierre Osborne 3-JAN-1994
John Montgomery 30-MAR-1994
Mark Guckenheimer 2-MAY-1994
