Summary -

In this topic, we described about the BETWEEN Condition with detailed example.

BETWEEN condition is used to validate a group of values in WHERE clause. By using BETWEEN condition, we can get the matched rows that satisfies the specified range. BETWEEN condition is used in SELECT, UPDATE, DELETE statements.

SELECT Syntax –

SELECT column1, column2, …, columnN 
FROM table_name
[WHERE column_name BETWEEN valuef AND valuet]; 

UPDATE Syntax –

UPDATE table_name
SET column1 = value1, column2 = value2, …, columnN = valueN
[WHERE column_name BETWEEN valuef AND valuet]; 

DELETE Syntax –

DELETE FROM table_name
[WHERE column_name BETWEEN valuef AND valuet]; 

  • column1, column2, …, columnN - Specifies the column names from table.
  • table_name – Specifies the name of the table.
  • column_name - Specifies the column that used to perform BETWEEN operation.
  • valuef, valuet – Specifies the values that used to set range.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch rows using BETWEEN clause.

Requirement1 – Fetch all details of employees whose emp_id BETWEEN 004 and 007. The query was as follows –

SELECT * FROM employee_details WHERE emp_id BETWEEN 004 AND 007; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

Requirement2 - Fetch all details of employees whose salary is in BETWEEN 15000 AND 25000. The query was as follows –

SELECT * FROM employee_details WHERE salary BETWEEN 15000 AND 25000; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

Scenario – Fetch rows using NOT BETWEEN clause.

Requirement – Fetch all details of employees whose emp_id NOT BETWEEN 004 and 007. The query was as follows –

SELECT * FROM employee_details WHERE emp_id NOT BETWEEN 004 AND 007;

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – UPDATE rows using BETWEEN clause.

Requirement – Update joining date as "2019-07-11" for the employees whose emp_id BETWEEN 004 and 007. The query was as follows –

UPDATE employee_details
SET date_of_hire = "2019-07-11" 
WHERE emp_id BETWEEN 004 AND 007; 

By executing above query, we can update joining date as "2019-07-11" for the employees whose emp_id BETWEEN 004 and 007. The table data after updation is as follows -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-07-11 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-07-11 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Delete rows using BETWEEN clause.

Requirement – delete employee details whose salary is in BETWEEN 20000 and 15000. The query was as follows –

DELETE employee_details
WHERE salary BETWEEN 15000 AND 20000; 

By executing above query, we can delete the employee details whose salary is in between 20000 and 15000. The table data after updates is as follows -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000