Tests whether a value falls within a specified range. Returns the values of the field that meet the condition.
a BETWEEN b AND c is equivalent to a >= b AND a <= c. Both bounds are inclusive.
Syntax
<a> [NOT] BETWEEN <b> AND <c>Parameters
| Parameter | Description |
|---|---|
a |
Required. The field to test. |
b |
Required. The lower bound of the range. Must be the same data type as a. |
c |
Required. The upper bound of the range. Must be the same data type as a. |
Return value
Returns the values of field a that fall in or fall out of the range between b and c:
-
When
BETWEENis used, rows whereais within the range[b, c](inclusive) are returned. -
When
NOT BETWEENis used, rows whereais outside the range are returned. -
If
a,b, orcis NULL, null is returned.
Example
The following example queries the emp table to find employees whose salary (sal) is between 1000 and 1500 (inclusive).
Sample data in the emp table:
empno,ename,job,mgr,hiredate,sal,comm,deptno
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10Run the following query:
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 1500;Result:
+-------+--------+----------+------+---------------------+--------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+---------------------+--------+--------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0 | 1400.0 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.0 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.0 | NULL | 10 |
| 7956 | TEBAGE | CLERK | 7748 | 1982-12-30 00:00:00 | 1300.0 | NULL | 10 |
+-------+--------+----------+------+---------------------+--------+--------+--------+The query returns rows where sal >= 1000 AND sal <= 1500.
Related functions
For other comparison and filtering functions, see Other functions.