SQLite | Query Data | Compare Against a Range of Values (BETWEEN Clause)

Tags:

The BETWEEN clause tests whether a column value falls within a specified range. This article explains how to use it in a conditional expression. For WHERE basics, see Set Query Conditions (WHERE Clause).

Conditions Using BETWEEN

BETWEEN includes both boundary values.

SELECT column_name, ... FROM table_name WHERE column_name BETWEEN value1 AND value2;

This is equivalent to:

SELECT column_name, ... FROM table_name WHERE column_name >= value1 AND column_name <= value2;

Use NOT BETWEEN to return values outside the range.

SELECT column_name, ... FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;

Create a table and insert sample data.

create table user (name text, old integer, address text);
insert into user values ('devkuma', 39, 'Seoul');
insert into user values ('kimkc', 34, 'Busan');
insert into user values ('araikuma', 26, 'Seoul');
insert into user values ('happykuma', 19, 'Seoul');
insert into user values ('mykuma', 27, 'Daejeon');
insert into user values ('yourkuma', 28, 'Gwangju');
insert into user values ('raccoon', 31, 'Busan');

Without a condition, all rows are returned.

sqlite> select * from user;
devkuma     39  Seoul
kimkc       34  Busan
araikuma    26  Seoul
happykuma   19  Seoul
mykuma      27  Daejeon
yourkuma    28  Gwangju
raccoon     31  Busan

The following query returns rows whose old value is between 20 and 30, inclusive.

select * from user where old between 20 and 30;

araikuma    26  Seoul
mykuma      27  Daejeon
yourkuma    28  Gwangju

Adding NOT returns the rows outside that range.

select * from user where old not between 20 and 30;

devkuma     39  Seoul
kimkc       34  Busan
happykuma   19  Seoul
raccoon     31  Busan