Quick Reach
MySQL Between operator
- MySQL supports different logical operators in queries and Between is one of those operators.
- The Between is used in the Where clause.
- You have to specify two values in the between MySQL operator. A minimum and a maximum value.
- These two values can be characters, dates or numbers.
- For example between 0 AND 10 ; between h AND z ; between 1/1/2012 AND 1/6/2013.
- The returned rows while using the between operator includes minimum and maximum values.
How to use Between operator
The syntax of using the Between operator is:
Select * from table_name
Where col_name BETWEEN val1 AND val2
Where val1 and val2 can be numbers, characters or dates.
Examples of the between operator
In our examples of between operator, we will use our created table, tbl_employee, that contains employees information. The table contains five fields including a primary key as shown below:
|
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)
);
|
A between operator example with numbers
In this example, we are using two numbers in between operator. The query and output of the query can be seen by clicking the link below:
|
1
2
3
|
select * from tbl_employee
Where emp_id between 2 AND 4
|
An example with characters
In this example, we will use two characters in the between clause. The query will return employees data whom names are starting and ending by the given characters:
The between query will be:
|
1
2
3
|
select * from tbl_employee
Where emp_name between ‘A’ AND ‘M’
|
Between with dates example
Now we will use two dates in our query of between operator. The query will return records of employees whom joining date is between two given dates as shown below:
The MySQL between dates query will be:
|
1
2
3
|
SELECT * FROM tbl_employee
WHERE joining_date BETWEEN ‘2008/05/01’ AND ‘2009/06/01’
|
Also see MySQL select
Leave A Comment?