Quick Reach
Syntax of SQL between operator
The general syntax of the between logical operator is:
Select * from table_name
Where col_name BETWEEN val1 AND val2
Where val1 and val2 can be two numbers, characters or dates.
See a between example
Between with dates example
The Between operator
A few main points about the SQL Between operator are:
- The between is a logical operator that is used in the Where clause to specify a condition.
- The between operator takes two values: minimum and maximum values and search between the two including the min and max values.
- The values in the between operator can be two numbers, two dates or two characters etc.
- e.g. between 10 AND 20; between a AND e; between 1/1/2015 AND 1/6/2015 etc.
- The Between simplifies two AND conditions into one (explained later part of this tutorial)
- The between SQL operator is used with the Where clause.
Examples of between operator
Let us show you examples of between SQL operator with our created database and table. We have a table, tbl_employee to show a few examples of the SQL between operator. The table structure is the same as we created in the Create table chapter and as shown below.
Just to remind you, this is how our example table is created in the test_db:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Create table tbl_employee
(emp_id int,
emp_name varchar(100),
emp_age int,
emp_salary decimal(8,2),
joining_date datetime,
PRIMARY KEY (emp_id)
);
|
Now as we have a table tbl_employee with five columns (emp_id, emp_name, emp_age, emp_salary and joining_date). Also, we insert data by using the insert statement.
Example of between in SQL with numbers
This example shows how to use numbers in the between operator. We will run the SQL query on tbl_employee to return all records where column emp_id values fall between 2 and 4.
The between query will be:
|
1
2
3
|
select * from tbl_employee
Where emp_id between 2 AND 4
|
See Demo Table with between online
Example of between operator with text
This example shows how to use text in the between operator. The query will return all employees data whom names start with the letter in between A and M.
The between query will be:
|
1
2
3
|
select * from tbl_employee
Where emp_name between ‘A’ AND ‘M’
|
See Demo Table with between online
Between with dates example
This example shows how to use dates with the SQL between operator. We will run SQL query on tbl_employee to return all employees data whom joining date is between the given dates:
The between with dates query:
|
1
2
3
|
select * from tbl_employee
Where joining_date between ‘01/05/2008’ AND ‘06/01/2009’
|
Demo table with between using dates
How Between operator simplifies two conditional statements
The between operator simplifies two AND conditions. However, it varies from database to database. Few databases include the given values (min and max) given in the between operator while other do not.
To elaborate further, how between simplifies the two AND conditions see the example below:
The SQL query with two conditional statements:
|
1
2
3
|
select * from tbl_employee
where emp_id >= 2 and emp_id <=4
|
Same query with between operator:
|
1
2
3
|
select * from tbl_employee
Where emp_id between 2 AND 4
|
Both of the above queries will produce the same output as shown in the example online (click the link below).
See Demo tables with Between and ‘AND’
As you can see between produces result just like two AND conditions.
Also see: SQL Where Clause
Leave A Comment?