Between SQL: examples, description

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.

between sql

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:

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

This between SQL statement has certain features. Get to know them:

  1. When working with numbers and dates, “From and To” constraint values ​​are included in the selection.
  2. 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.

sql query between

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.

sql between description

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.

Source: https://habr.com/ru/post/K6611/


All Articles