{"id":114,"date":"2016-03-23T07:55:55","date_gmt":"2016-03-23T07:55:55","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=114"},"modified":"2025-02-04T07:21:30","modified_gmt":"2025-02-04T14:21:30","slug":"sql-between","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-between\/","title":{"rendered":"SQL BETWEEN Operator"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL BETWEEN<\/code> operator to check if a value falls within a specific range.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQL BETWEEN operator #<\/a><\/h2>\r\n\r\n\r\n\r\n

The BETWEEN<\/code> operator is one of the logical operators in SQL<\/a>. The BETWEEN<\/code> operator checks if a value is within a range of values.<\/p>\r\n\r\n\r\n\r\n

Here’s the syntax of the BETWEEN<\/code> operator:<\/p>\r\n\r\n\r\n

expression BETWEEN low AND high;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The BETWEEN<\/code> operator returns true<\/code> if the expression<\/code> is greater than or equal to ( >=<\/code>) the low value and less than or equal to ( <=<\/code>) the high value.<\/p>\r\n\r\n\r\n\r\n
Technically, the BETWEEN<\/code> is equivalent to the following expression that uses the greater than or equal to (>=<\/code>) and less than or equal to (<=<\/code>) operators:<\/p>\r\n\r\n\r\n
expression >= low AND expression <= high<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
NOT BETWEEN #<\/a><\/h3>\r\n\r\n\r\n\r\n
To negate the result of the\u00a0BETWEEN<\/code> operator, you use the NOT<\/a><\/code> operator:<\/p>\r\n\r\n\r\n
expression NOT BETWEEN low AND high<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The NOT BETWEEN<\/code> returns true<\/code> if the expression<\/code> is less than low<\/code> or greater than high<\/code>;\u00a0otherwise, it returns false<\/code>.<\/p>\r\n\r\n\r\n\r\n
expression < low OR expression > high<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQL BETWEEN operator examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
We’ll use the employees<\/code> table from the sample database<\/a> to illustrate how the BETWEEN<\/code> operator works.<\/p>\r\n\r\n\r\n\r\n
\"SQL<\/figure>\r\n\r\n\r\n\r\n
Selecting employees within a salary range #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following query uses the BETWEEN<\/code> operator to find employees with the salaries between 2,500<\/code> and 2,900<\/code>:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  employee_id,\r\n  first_name,\r\n  last_name,\r\n  salary\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  salary BETWEEN<\/span> 2500<\/span> AND<\/span> 2900<\/span>\r\nORDER<\/span> BY<\/span>\r\n  salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 employee_id | first_name |  last_name  | salary\r\n-------------+------------+-------------+---------\r\n         119 | Karen      | Colmenares  | 2500.00\r\n         118 | Guy        | Himuro      | 2600.00\r\n         126 | Irene      | Mikkilineni | 2700.00\r\n         117 | Sigal      | Tobias      | 2800.00\r\n         116 | Shelli     | Baida       | 2900.00<\/code><\/span><\/pre>\r\n\r\n\r\n
Notice that the result set includes the employees whose salaries are 2,500<\/code> and 2,900<\/code>.<\/p>\r\n\r\n\r\n\r\n
The following query returns the same result set as the above query. However, it uses comparison operators greater than or equal to (>=) and less than or equal to (<=) instead:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  employee_id,\r\n  first_name,\r\n  last_name,\r\n  salary\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  salary >= 2500<\/span>\r\n  AND<\/span> salary <= 2900<\/span>\r\nORDER<\/span> BY<\/span>\r\n  salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Using SQL NOT BETWEEN example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following statement uses the NOT BETWEEN<\/code> operator to find all\u00a0employees whose salaries are not in the range of 2,500<\/code> and 2,900<\/code>:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  employee_id,\r\n  first_name,\r\n  last_name,\r\n  salary\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  salary NOT<\/span> BETWEEN<\/span> 2500<\/span> AND<\/span> 2900<\/span>\r\nORDER<\/span> BY<\/span>\r\n  salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 employee_id | first_name  | last_name  |  salary\r\n-------------+-------------+------------+----------\r\n         115 | Alexander   | Khoo       |  3100.00\r\n         193 | Britney     | Everett    |  3900.00\r\n         192 | Sarah       | Bell       |  4000.00\r\n         107 | Diana       | Lorentz    |  4200.00\r\n         200 | Jennifer    | Whalen     |  4400.00\r\n...<\/code><\/span><\/pre>\r\n\r\n\r\n
Using SQL BETWEEN operator with a date ranges #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following example uses the BETWEEN<\/code> operator to find all employees who joined the company between January 1, 1999<\/code>, and December 31, 2000<\/code>:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  first_name,\r\n  last_name,\r\n  hire_date\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  hire_date BETWEEN<\/span> '1999-01-01'<\/span> AND<\/span> '2000-12-31'<\/span>\r\nORDER<\/span> BY<\/span>\r\n  hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 first_name | last_name  | hire_date\r\n------------+------------+------------\r\n Diana      | Lorentz    | 1999-02-07\r\n Kimberely  | Grant      | 1999-05-24\r\n Karen      | Colmenares | 1999-08-10\r\n Luis       | Popp       | 1999-12-07\r\n Charles    | Johnson    | 2000-01-04<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following example uses the NOT BETWEEN<\/code> operator to find employees joined the company before January 1, 1994<\/code> or after December 31, 1999<\/code>:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  first_name,\r\n  last_name,\r\n  hire_date\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  hire_date NOT<\/span> BETWEEN<\/span> '1994-01-01'<\/span> AND<\/span> '1999-12-31'<\/span>\r\nORDER<\/span> BY<\/span>\r\n  hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 first_name | last_name | hire_date\r\n------------+-----------+------------\r\n Steven     | King      | 1987-06-17\r\n Jennifer   | Whalen    | 1987-09-17\r\n Neena      | Kochhar   | 1989-09-21\r\n Alexander  | Hunold    | 1990-01-03\r\n Bruce      | Ernst     | 1991-05-21\r\n Lex        | De Haan   | 1993-01-13\r\n Charles    | Johnson   | 2000-01-04<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Using SQL BETWEEN operator with a function example #<\/a><\/h3>\r\n\r\n\r\n\r\n
The following example uses the BETWEEN<\/code> operator with the EXTRACT<\/code> function to find employees who joined the company between 1999 and 2000:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  first_name,\r\n  last_name,\r\n  EXTRACT(YEAR<\/span>  FROM<\/span> hire_date) joined_year\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  EXTRACT(YEAR<\/span>  FROM<\/span> hire_date) BETWEEN<\/span> 1999<\/span> AND<\/span> 2000<\/span>\r\nORDER<\/span> BY<\/span>\r\n  hire_date;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 first_name | last_name  | joined_year\r\n------------+------------+-------------\r\n Diana      | Lorentz    |        1999\r\n Kimberely  | Grant      |        1999\r\n Karen      | Colmenares |        1999\r\n Luis       | Popp       |        1999\r\n Charles    | Johnson    |        2000<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this example:<\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
In practice, you often use the BETWEEN<\/code> and NOT BETWEEN<\/code> operator in the WHERE<\/a><\/code> clause of the SELECT<\/a><\/code> to select rows whose values of a column are within a specific range.<\/p>\r\n\r\n\r\n\r\n
Like the BETWEEN<\/code> operator, you can rewrite the NOT BETWEEN<\/code> operator using the less than (<<\/code>) and greater than (><\/code>) operators with the OR<\/a><\/code> operator as follows:<\/p>\r\n\r\n\r\n
To compare a value with an exclusive range, you can use the comparison operators<\/a> less than (<<\/code>) and greater than ( ><\/code>).<\/p>\r\n\r\n\r\n\r\n