w3resource

SQLite Between operator

Introduction

The SQLite BETWEEN operator tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression. The operator returns 1 when the search value present within the range otherwise returns 0.

Syntax:

SELECT [column_name... | expression ]
FROM [table_name]
WHERE [NOT] BETWEEN value_from AND value_to;

Parameters:

Name Description
column_name Name of the column of the table.
expression Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of an SQLite query that compare values against other values or perform arithmetic calculations.
table_name Name of the table.
value_from,value_to Starting value and ending value.

Example: SQLite Between operator

Sample table: agents


To get data of all columns from the 'agents' table with the following condition -

1. percentage of commission for the 'agents' should be within .12 to .14,

the following SQLite statement can be used :

SELECT agent_code, agent_name, commission 
FROM agents 
WHERE commission BETWEEN .12 AND .14;

Here is the result.

AGENT_CODE  AGENT_NAME                                COMMISSION
----------  ----------------------------------------  ----------
A003        Alex                                      0.13
A008        Alford                                    0.12
A010        Santakumar                                0.14
A012        Lucida                                    0.12
A005        Anderson                                  0.13
A001        Subbarao                                  0.14

Example : SQLite Between operator with IN

In the following example, we have discussed SQLite BETWEEN operator using IN operator in the select statement.

Sample table: customer


To get data of all columns from the 'customer' table with following conditions -

1. 'agent_code' must be within 'A003' and 'A008',

2. but 'agent_code' 'A004', 'A007' and 'A005' should not appear,

The following SQLite statement can be used:

SELECT agent_code, cust_code, cust_name, cust_city 
FROM customer 
WHERE (agent_code BETWEEN 'A003' AND 'A008')  
AND NOT agent_code IN ('A004','A007','A005');

Here is the result.

AGENT_CODE  CUST_CODE   CUST_NAME   CUST_CITY
----------  ----------  ----------  -----------------------------------
A003        C00013      Holmes      London
A008        C00001      Micheal     New York
A008        C00020      Albert      New York
A006        C00024      Cook        London
A003        C00015      Stuart      London
A008        C00002      Bolt        New York
A006        C00023      Karl        London

Example : SQLite Between operator with text value

In the following example, we have discussed SQLite BETWEEN operator using text value in the select statement.

Sample table: customer


To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -

1.'cust_name' must begin with any of the letters between 'A' and 'I',

the following SQLite statement can be used:

SELECT cust_code, cust_name, cust_city, working_area 
FROM customer 
WHERE cust_name BETWEEN 'A' AND 'I';

Relational Algebra Expression:

Relational Algebra Expression: SQLite Between operator with text value.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Between  operator with text value.

Here is the result.

CUST_CODE   CUST_NAME   CUST_CITY                            WORKING_AREA
----------  ----------  -----------------------------------  ------------
C00013      Holmes      London                               London
C00020      Albert      New York                             New York
C00024      Cook        London                               London
C00002      Bolt        New York                             New York
C00018      Fleming     Brisban                              Brisban
C00022      Avinash     Mumbai                               Mumbai
C00010      Charles     Hampshair                            Hampshair

Example : SQLite Between operator with NOT operator

In the following example, we have discussed SQLite BETWEEN operator using 'NOT' operator in a select statement.

Sample table: customer


To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -

1. 'cust_name' must not begin with any of the letters  between 'K' and 'Y' ,

the following, SQLite statement can be used :

SELECT cust_code, cust_name, working_area 
FROM customer 
WHERE cust_name NOT BETWEEN 'K' AND 'Y';

Relational Algebra Expression:

Relational Algebra Expression: SQLite Between  operator with  NOT operator.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Between  operator with  NOT operator.

Here is the result.

CUST_CODE   CUST_NAME   WORKING_AREA
----------  ----------  ------------
C00013      Holmes      London
C00020      Albert      New York
C00024      Cook        London
C00002      Bolt        New York
C00018      Fleming     Brisban
C00021      Jacks       Brisban
C00019      Yearannaid  Chennai
C00022      Avinash     Mumbai
C00010      Charles     Hampshair

Example: SQLite Between operator with NOT on date value

In the following we have discussed SQLite BETWEEN operator using boolean 'NOT' operator on date value in a select statement.

Sample table : orders


To get data of 'ord_num',  'ord_amount',  'advance_amount',  'ord_date', 'cust_code' and  'agent_code' from the 'orders' table with following condition -

1. 'ord_date' must be a date before '15-Feb-08' or after '30-Jul-08',

the following SQLites statement can be used :

SELECT ord_num, ord_amount, ord_date, cust_code, agent_code 
FROM orders 
WHERE ord_date NOT BETWEEN '2008-08-02' AND '2008-30-02';

Relational Algebra Expression:

Relational Algebra Expression: SQLite Between  operator with  NOT on date value.

Relational Algebra Tree:

Relational Algebra Tree: SQLite Between  operator with  NOT on date value.

Here is the result.

ORD_NUM     ORD_AMOUNT  ORD_DATE    CUST_CODE   AGENT_CODE
----------  ----------  ----------  ----------  ----------
200100      1000        2008-01-08  C00015      A003
200110      3000        2008-04-15  C00019      A010
200112      2000        2008-05-30  C00016      A007
200113      4000        2008-06-10  C00022      A002
200102      2000        2008-05-25  C00012      A012
200118      500         2008-07-20  C00023      A006
200130      2500        2008-07-30  C00025      A011
200115      2000        2008-02-08  C00013      A013
200108      4000        2008-02-15  C00008      A004
200103      1500        2008-05-15  C00021      A005
200105      2500        2008-07-18  C00025      A011
200109      3500        2008-07-30  C00011      A010
200101      3000        2008-07-15  C00001      A008
200111      1000        2008-07-10  C00020      A008
200104      1500        2008-03-13  C00006      A004
200106      2500        2008-04-20  C00005      A002
200120      500         2008-07-20  C00009      A002
200116      500         2008-07-13  C00010      A009
200124      500         2008-06-20  C00017      A007
200126      500         2008-06-24  C00022      A002
200129      2500        2008-07-20  C00024      A006
200127      2500        2008-07-20  C00015      A003
200128      3500        2008-07-20  C00009      A002
200133      1200        2008-06-29  C00009      A002

Previous: LIKE and GLOB Operators
Next: IN and NOT IN Operator



Follow us on Facebook and Twitter for latest update.