When working with relational DBMSs in which data is stored in a table form, users often face the task of fetching values that are (not included) in a certain range. The SQL language allows you to specify a set that should (should not) belong to a value by various options - the In operator, the Like operator, a combination of more or less conditions, and also the SQL Between statement. The description and examples in this article will be devoted to the last option.
Between statement in SQL: syntax, limitations
Literally, the between SQL statement translates as “between.” Its use allows you to set the “From and To” restriction to a specific field, and if the next value falls into the range, the predicate will take the value “True”, and the value will fall into the final selection.
The syntax of the operator is extremely simple:
Select * From table t1 Where t1.n between 0 and 7 |
As you can see, after the between keyword, you must specify the value of the lower border of the range, then AND and the value of the upper border.
Let's list what types of data the between SQL operator can work with:
- With numbers - integer and fractional.
- With dates.
- With text.
This between SQL statement has certain features. Get to know them:
- When working with numbers and dates, “From and To” constraint values are included in the selection.
- The value of the lower boundary of the range must be less than the value of the upper boundary, otherwise nothing will be displayed, because the condition is logically not true. Particular care must be taken when variables are included in the condition instead of specific values.
When working with text, the value of the upper boundary of the range will not be included in the selection, if it is not specified very accurately. In the following sections, we consider this feature in more detail.
Selection of numbers and dates in a certain range
We will prepare a table with data on managers working in the organization. The table will have the following structure:
Field name | Data type | Description |
The code | Counter | Employee Unique Identifier |
Surname | Text | Employee surname |
Name | Text | Employee Name |
middle name | Text | Patronymic of the employee |
Floor | Text | Employee Sex (M / F) |
Date of receipt | Date Time | Employee hiring date |
Child_number | Numerical | Number of children per employee |
Fill the table with the following data:
The code | Surname | Name | middle name | Floor | Date of receipt | Child_number |
1 | Alexandrova | Irina | Nikolaevna | F | 05/01/2014 | 1 |
2 | Borovoy | Andrew | Stepanovich | M | 09/21/2013 | 0 |
3 | Vinogradov | Sergei | Pavlovich | M | 06/15/1998 | 1 |
4 | Shumilin | Alexander | Borisovich | M | 12/25/2004 | 2 |
5 | Vishnyakov | Leonid | Alexandrovich | M | 10/09/2007 | 0 |
6 | Tropnikov | Vasiliy | Sergeevich | M | 01/12/2016 | 3 |
7 | Pearls | Nikita | Vasilevich | M | 01/11/2017 | 1 |
8 | Avdeeva | Nika | Konstantinovna | F | 03/31/2001 | 2 |
9 | Yakovlev | Leonid | Nikolaevich | M | 02.16.2009 | 0 |
We will make a sql query between, which will help us choose all the employees who have 2 or 3 children:
SELECT Managers. * FROM Managers WHERE Managers. Child_Number between 2 and 3 |
The result will be three lines with data on employees with the names of Shumilin, Tropnikov and Avdeeva.
Now we select the employees accepted from January 1, 2005 to December 31, 2016. It should be noted that different DBMSs allow writing to date conditions in different ways. In most cases, the date is simply forcibly converted to day-month-year (or whatever is more convenient) and written in single or double quotes. In the MS Access DBMS, the date is enclosed in the “#” sign. Let's execute the example based on it:
SELECT Managers. *, Managers. Reception Date FROM Managers WHERE Managers. Reception Date Between # 1/1/2005 # And # 31/12/2016 # |
The result will be five employees hired during the specified period inclusive.
Next, let's see how between SQL works with strings.
Work in between with strings
A very common task that must be solved when working with the names of employees is the need to select only those whose last names begin with a certain letter. Let’s try and we will fulfill the request and select employees whose surnames begin with surnames from A to B:
SELECT Managers. * FROM Managers WHERE Managers. Last Name between "A" and "B" ORDER BY 2 |
The result is as follows:
The code | Surname | Name | middle name | Floor | Date of receipt | Child_number |
8 | Avdeeva | Nika | Konstantinovna | F | 03/31/2001 | 2 |
1 | Alexandrova | Irina | Nikolaevna | F | 05/01/2014 | 1 |
2 | Borovoy | Andrew | Stepanovich | M | 09/21/2013 | 0 |
As you can see, two employees with a surname with the letter B did not appear on the list. What is the reason for this? The point is exactly how the operator compares strings of unequal length. Line "B" is shorter than the line "Vinogradov" and is supplemented by spaces. But when sorting alphabetically, spaces will be leading characters, and the last name will not fall into the selection. Different DBMSs offer different solutions to this problem, but it is often easiest to specify the following letter of the alphabet in the range for reliability:
SELECT Managers. * FROM Managers WHERE Managers. Last Name between "A" and "G" ORDER BY 2 |
When performing this request, the result will completely satisfy us.
Such a nuance exists only when working with symbolic data, but it shows that when working even with such simple operators as between, you need to be careful.