The BETWEEN operator in Teradata selects a range of data between two values. This is generally used with the WHERE clause to filter out the data in the result-set.
Teradata BETWEEN Syntax
Following is the syntax of the Teradata BETWEEN operator.
WHERE expression1 [NOT] BETWEEN expression2 AND expression3;
HERE,
- expression1 – The name of the column or alias in the select statement for which the filtering has to be done.
- NOT – If you don’t want to select the data between 2 ranges.
- expression2 – The starting range for which the test needs to be done.
- expression3 – The ending range for which the test needs to be done.
If the BETWEEN test fails, no rows return.
Teradata BETWEEN example
Consider the following employee table.
| emp_id | emp_name | emp_phone | emp_gender | department |
| 101 | Kalyan Roy | 9620139678 | M | HR |
| 102 | Rajesh Sharma | 9611895588 | M | ADMIN |
| 103 | Rupali Sharma | 8884692570 | F | SALES |
| 104 | Dipankar Sen | 9957889640 | M | HR |
| 105 | Sunitha Rai | 9742067708 | F | SALES |
| 106 | Parag Barman | 8254066054 | M | MARKETING |
| 107 | Vinitha Sharma | 9435746645 | F | ADMIN |
| 108 | Abhishek Saha | 9850157207 | M | SALES |
| 109 | Rushang Desai | 9850157207 | M | SALES |
| 110 | Arvin Kumar | 8892340054 | M | ADMIN |
The below query returns the employee details whose id falls between 101 to 105.
SELECT * FROM TERADATAPOINT.EMPLOYEE WHERE EMP_ID BETWEEN 101 AND 105;
Output:
Teradata NOT BETWEEN example
The below example shows the opposite of the above employee i.e. this will not select employee_id between 101 and 105.
SELECT * FROM TERADATAPOINT.EMPLOYEE WHERE EMP_ID NOT BETWEEN 101 AND 105;
