Between...AND, NOT Between...AND

BETWEEN AND operator retrieves the column value in a specified range. The range is inclusive, which means the values at both ends of the range are included.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                      ENAME VARCHAR2(10),
                      HIREDATE DATE);

INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD',  TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));


SQL> SELECT *
  2  FROM emp
  3  WHERE empno BETWEEN 1 AND 3;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
         1 SMITH      17-DEC-80
         2 ALLEN      20-FEB-81
         3 WARD       22-FEB-81

SQL>

NOT BETWEEN reverses the BETWEEN operator.


SQL> SELECT *
  2  FROM emp
  3  WHERE empno NOT BETWEEN 1 AND 3;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
         4 JONES      02-APR-81
         5 MARTIN     28-SEP-81

SQL>
Home »
Oracle »
Select » 

Where:
  1. Where clause
  2. dual table
  3. Comparison Operators
  4. ANY
  5. ALL
  6. LIKE, NOT LIKE
  7. IN, NOT IN
  8. Between...AND, NOT Between...AND
  9. IS NULL, IS NOT NULL
  10. Logical operator AND, OR, NOT
  11. Operator Precedence for AND, OR, NOT
Related: