Here we will learn SQLite Between operator with example and how to use SQLite Between with dates, not operator to get the rows within the specified range with examples.
In SQLite Between operator is used to get the rows or records within the range specified in query statements.
Generally in SQLite, we can use Between operator with Select, Update, Delete statements to get or update or delete records based on the specified range values.
Following is the syntax of using SQLite Between operator with select statement
column_exp BETWEEN val1 AND val2;
If you observe above SQLite Between operator syntax it returns records whose column_exp values between val1 and val2.
In above SQLite Between operator syntax we defined some of the properties that are
column_exp - It may be the column or expression that you want as a result.
val1 & val2 - By using these values we can define the range of values.
We will see how to use SQLite Between operator with SELECT statement for that first create one table called emp_master table and insert some records like as shown below.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER references dept_master);
INSERT INTO emp_master
values (1,'Honey','Patel',10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,12);
Now we will see how to use SQLite Between operator with Select statements for that write SQLite query like as shown following.
sqlite>SELECT * FROM emp_master WHERE emp_id BETWEEN 1 AND 3;
When we run above SQLite Between operator query it will return employees whose emp_id between 1 and 3 like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
We will see how to use SQLite Between operator with Dates to get records within the particular date range. Following is the example of using SQLite Between operator with dates in Select statement.
SELECT * FROM emp_master
WHERE joined_date BETWEEN '2016-08-01' AND '2016-10-07';
The above SQLite Between operator with Dates query will return all the employees whose joining date between '2016-08-01' and '2016-10-07'.
In SQLite, if we use Not with Between operator then it will return the records which are not in the defined range.
Following is the example of using SQLite Between operator with Not in Select statement to get the records that are not in the defined range.
sqlite> SELECT * FROM emp_master WHERE emp_id NOT BETWEEN 1 AND 3;
The above SQLite Between operator query will return employee records whose emp_id not in between 1 and 3 like as shown following.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
4 Jagruti Viras 9500 2
This is how we can use SQLite Between operator to get records within the specified range based on our requirements.