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 Tree:
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 Tree:
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 Tree:
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
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sqlite/between-operator.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics