Multiple Value Range Search (BETWEEN) Teradata

The BETWEEN comparison can be used as another technique to request multiple values for a column that are all in a specific range. It is easier than writing a compound OR comparison or a long value list of sequential numbers when using the IN.

This is a good time to point out that this chapter is incrementally adding new ways to compare for values within a WHERE clause. However, all of these techniques can be used together in a single WHERE clause. One method does not eliminate the ability to use one or more of the others using logical operators between each comparison.

The next SELECT shows the syntax format for using the BETWEEN:

SEL[ECT] <column-name>
[,<column-name> ]
FROM <table-name>
WHERE <column-name> BETWEEN <low-value> AND <high-value>

The first and second values specified are inclusive for the purposes of the search. In other words, when these values are found in the data, the rows are included in the output.

As an example, the following code returns all students whose grade points of 2.0, 4.0 and all values between them:

SELECT Grade_Pt
FROM Student_Table
WHERE Grade_Pt BETWEEN 2.0 and 4.0 ;
7 Rows returnedreturns all students whose grade points of 2.0, 4.0

Notice that due to the inclusive nature of the BETWEEN, both 2.0 and 4.0 were included in the answer set. The first value of the BETWEEN must be the lower value, otherwise, no rows will be returned. This is because it looks for all values that are greater or equal to the first value and less than or equal to the second value.

A BETWEEN can also be used to search for character values. When doing this, care must be taken to insure that rows are received with the values that are needed.The system can only compare character values that are the same length. So, if one column or value is shorter than the other, the shortest will automatically be padded with spaces out to the same length as the longer value.
Comparing ‘CA’ and ‘CALIFORNIA’ never constitutes a match. In reality, the database is comparing ‘CA ’ with ‘CALIFORNIA ‘ and they are not equal. Although, easier to code, it does not always mean faster to execute. There is always a trade-off to consider.

The next SELECT finds all of the students whose last name starts with an L:

SELECT Last_Name
FROM Student_Table
WHERE Last_Name BETWEEN 'L' AND 'LZ' ;
1 Row returnednext SELECT finds all of the students whose last name starts with an L

Last_Name

Larkins

In reality, the WHERE could have used BETWEEN ‘L’ and ‘M’ as long as no student's last name was ‘M’. The data needs to be understood when using BETWEEN for character comparisons.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics