Between SQL: examples, description

When working with relational DBMSs, in which data is stored in a table form, users often face the task of sampling values that enter (not included) in a certain range. The SQL language allows you to specify the set that the value should not (should) belong to by different variants - the In statement, the Like statement, the combination of conditions is more - less, and also the SQL operator Between. The description and examples in this article will be devoted to the last option.

The "Between" operator in SQL: syntax, constraints

The operator between SQL is literally translated "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 be "True" and the value will end up in the final selection.

The syntax of the operator is extremely simple:

As you can see, after the keyword between it is necessary to specify the value of the lower bound of the range, then AND and the value of the upper bound.

Let's list the types of data the SQL operator can work with:

1. With numbers - integer and fractional.
2. With dates.
3. With the text.

The given operator between SQL has certain features. Let's get acquainted with them:

1. When working with numbers and dates, the values of the "From and To" constraints are included in the sample.
2. The value of the lower bound of the range should be less than the value of the upper bound, otherwise nothing will be output, 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 bound of the range will not be included in the selection, unless it is specified with extreme accuracy. In the following sections, we will consider this feature in more detail.

The selection of numbers and dates in a certain range

Prepare a table with data on managers working in the organization. The table will have the following structure:

Fill in the table with the following data:

Let's create a sql query between, which will help us select all employees who have 2 or 3 children:

The result will be three lines with data on employees with the names of Shumilin, Tropnikov and Avdeeva.

Now we will select the employees accepted from January 1, 2005 to December 31, 2016. It should be noted that different DBMS differently allow you to record in the terms of the date. In most cases, the date simply forcefully leads to the form day-month-year (or as more convenient) and is written in single or double quotes. In the MS Access database, the date is enclosed in the "#" sign. We will execute an example just on its basis:

The result will be five employees, hired at the specified period, inclusive.

Next, let's see how SQL works with strings.

Work in between lines

A very frequent task that has to be dealt with when working with employee names is the need to select only those whose names begin with a certain letter. We will also try to fulfill the request and choose employees whose names begin with the surname from A to B:

The result is as follows:

As you can see, two employees who have a surname with the letter B are not included in the list. What is the reason for this? The point is how exactly the operator compares lines of unequal length. The line "B" is shorter than the line "Vinohrad" and is completed with spaces. But when sorting in alphabetical order, the spaces appear to be leading symbols, and the surname will not be included in the sample. Different DBMS offer different solutions to this problem, but it is often easiest to indicate the following letter of the alphabet in the range:

When the query is executed, the result will completely satisfy us.

This nuance exists only when working with character data, but it shows that when working with even such simple operators as between, one must be careful.