SQL BETWEEN Operator

SQL BETWEEN operator used for fetching within range data. SQL BETWEEN query simply a shorthand way of expressing an inclusive range comparison.

SQL Between operator support only range type value like number, dates, character. But not supporting boolean, string value range.

Syntax

Consider following syntax that help you to understanding BETWEEN clause,

SELECT * FROM table_name
    WHERE column_name 
    BETWEEN lower_bound_value AND upper_bound_value;

When SQL query with BETWEEN clause parse into SQL Buffer, it will automatically expand out into separate comparison clauses.

SELECT * FROM table_name
    WHERE column_name <= lower_bound_value 
    AND column_name >= upper_bound_value;

Example

In this example select all users_info table rows with range between no column value 1 to 5.

SQL> SELECT * FROM users_info WHERE no BETWEEN 1 AND 5;

 NO NAME                  ADDRESS                   CONTACT_NO      BIRTH_DATE
--- --------------------- ------------------------- --------------- -------------
  1 Opal Kole             63 street Ct.             000-444-7847    1984-05-07
  2 Max Miller            41 NEW ROAD.              000-444-8292    1987-07-07
  3 Beccaa Moss           2500 green city.          000-444-7586    1992-01-15
  4 Paul Singh            1343 Prospect St          000-444-7585    1998-07-25
  5 Ken Myer              137 Clay Road         000-444-7528    2002-04-07

SQL BETWEEN clause with DATE

In this example select all users_info table rows with range between birth_date column value '1990-01-01' to '2000-12-31'.

SQL> SELECT * FROM users_info WHERE birth_date BETWEEN '1990-01-01' AND '2000-12-31';

 NO NAME                  ADDRESS                   CONTACT_NO      BIRTH_DATE
--- --------------------- ------------------------- --------------- -------------
  3 Beccaa Moss           2500 green city.          000-444-7586    1992-01-15
  4 Paul Singh            1343 Prospect St          000-444-7585    1998-07-25
  6 Jack Evans            1365 Grove Way            000-444-8401    1993-04-24
  8 Gabe Hee              1220 Dallas Drive         000-444-5028    1992-08-27
  9 Ben Mares             101 Candy Road            000-444-5928    1999-11-24

5 rows selected.

SQL BETWEEN clause with NOT IN

In this example select all users_info table rows with range NOT IN between no column value 1 to 5. Use only NOT keyword instead of NOT IN.

SQL> SELECT * FROM users_info WHERE no NOT BETWEEN 6 AND 10;

 NO NAME                  ADDRESS                   CONTACT_NO      BIRTH_DATE
--- --------------------- ------------------------- --------------- -------------
  1 Opal Kole             63 street Ct.             000-444-7847    1984-05-07
  2 Max Miller            41 NEW ROAD.              000-444-8292    1987-07-07
  3 Beccaa Moss           2500 green city.          000-444-7586    1992-01-15
  4 Paul Singh            1343 Prospect St          000-444-7585    1998-07-25
  5 Ken Myer              137 Clay Road             000-444-7528    2002-04-07

5 rows selected.