SQL BETWEEN Operator

In SQL, the BETWEEN operator with the WHERE clause selects values within a given range.

Example

-- select rows where the amount is between 200 and 600

SELECT * 
FROM Orders
WHERE amount BETWEEN 200 AND 600;

Here, the SQL command selects all columns from the Orders table where the amount is between 200 and 600 (including 200 and 600).


SQL BETWEEN Syntax

SELECT column1, column2, ...
FROM table
WHERE column BETWEEN value1 AND value2;

Here,

  • column1, column2, ... are the columns you want to filter
  • table is the name of the table
  • column is the name of the column where we want to specify a range of values
  • BETWEEN is an operator used to specify a range of values for the column
  • value1 and value2 are the lower and upper bounds of the range

Example: SQL BETWEEN

SELECT item, amount
FROM Orders
WHERE amount BETWEEN 300 AND 500;

Here, we selected item and amount columns from Orders that have amounts between 300 and 500 (including 300 and 500).

SQL BETWEEN Operator
Example: SQL BETWEEN Operator

SQL NOT BETWEEN Operator

The NOT BETWEEN operator is used to exclude the rows that match the values in the range. It returns all the rows except the excluded rows.

Let's look at an example.

-- exclude rows with amount between 300 and 500

SELECT item, amount
FROM Orders
WHERE amount NOT BETWEEN 300 AND 500;

Here, we selected all the items from Orders except the rows that have amounts between 300 and 500 (300 and 500 are also excluded).

How to use NOT BETWEEN Operator in SQL
Example: SQL NOT BETWEEN Operator

SQL BETWEEN Operator with Text

The BETWEEN operator also works with text data.

-- select rows where items begin with letters between 'I' and 'L' inclusive
-- this includes all items starting with 'I', 'J', 'K', and any items starting with 'L'

SELECT item, amount
FROM Orders
WHERE item BETWEEN 'I' AND 'L';

Here, the SQL command selects all orders where the item names begin with letters between I and L.

How to use BETWEEN Operator With Text in SQL
Example: SQL BETWEEN Operator With Text

Generally, the words starting with the endpoint (L) are not selected.

If you need to include all the words that start with L as well, we can use ~.

-- select rows where items begin with letters between 'I' and 'L'
-- include all items beginning with 'L' followed by other characters

SELECT item, amount
FROM Orders
WHERE item BETWEEN 'I' AND 'L~';

SQL BETWEEN Dates

In SQL, we can also use BETWEEN to filter data between two dates.

Let's look at an example.

-- get the records of those teams
-- who registered between given dates

SELECT *
FROM Teams
WHERE registered BETWEEN '2021-01-01' AND '2022-11-01';

Here, we selected the teams who registered between 2021-01-01 and 2022-11-01.


Also Read:

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges