SQL BETWEEN Operator

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

SELECT * 
FROM Orders
WHERE amount BETWEEN 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 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).

SQL BETWEEN Operator with Text

The BETWEEN operator also works with text data.

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

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.

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:

• SQL AND, OR, and NOT Operators
• SQL LIKE
• SQL IN and NOT IN