SQLite Between Operator

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.

SQLite Between Operator

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.

Syntax of SQLite Between Operator

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.

SQLite Between Operator Example

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

SQLite Between with Dates

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'.

SQLite Between with Not Operator

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.

 

sqliteSELECT 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.

Topics Covered