BETWEEN - This statement is used in the WHERE clause to select data between two values. These may include: test, numbers and dates.

Syntax SQL BETWEEN

1
2
3
4

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Example 1

There is a table "Persons":

So, now we want to select all persons whose names are located between "Hansen" and "Pettersen" from the table above. Note that sampling will occur in alphabetical order.

For this we use this query:

1
2
3

SELECT * FROM Persons
WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'

Query Result:

- Note: The BETWEEN operator works differently in different databases.

In some databases, the person with the names of "Hansen" and "Pettersen" will not be displayed when receiving the result, because the BETWEEN operator returns only the values ​​between those in the query.

In some databases, the person with the names of "Hansen" and "Pettersen" will be displayed when receiving the result.

In other databases, the person with the name "Hansen" will be on the list, but "Pettersen" will not (as in the example above), because the operator chooses between two fields, but the result shows only the first specified value without the other.

Tip: Before using the BETWEEN operator in the database, you need to check how the database responds to the requests of this type.

Example 2

So, now we want to select all persons whose names are not located between "Hansen" and "Pettersen" from table "Persons".

1
2
3

SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'

Query Result: