Teradata - Logical and Conditional Operators

Teradata supports the following logical and conditional operators. These operators are used to perform comparison and combine multiple conditions.

Syntax Meaning
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
= Equal to
BETWEEN If values within range
IN If values in <expression>
NOT IN If values not in <expression>
IS NULL If value is NULL
IS NOT NULL If value is NOT NULL
AND Combine multiple conditions. Evaluates to true only if all conditions are met
OR Combine multiple conditions. Evaluates to true only if either of the conditions is met.
NOT Reverses the meaning of the condition

BETWEEN

BETWEEN command is used to check if a value is within a range of values.

Example

Consider the following employee table.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Robert Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Robert James 1/4/2008 3 12/1/1984

The following example fetches records with employee numbers in the range between 101,102 and 103.

SELECT EmployeeNo, FirstName FROM  
Employee 
WHERE EmployeeNo BETWEEN 101 AND 103;

When the above query is executed, it returns the employee records with employee no between 101 and 103.

*** Query completed. 3 rows found. 2 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo            FirstName 
-----------  ------------------------------ 
   101                   Mike 
   102                   Robert 
   103                   Peter

IN

IN command is used to check the value against a given list of values.

Example

The following example fetches records with employee numbers in 101, 102 and 103.

SELECT EmployeeNo, FirstName FROM  
Employee 
WHERE EmployeeNo in (101,102,103);

The above query returns the following records.

*** Query completed. 3 rows found. 2 columns returned. 
*** Total elapsed time was 1 second.  
 EmployeeNo            FirstName 
-----------  ------------------------------ 
   101                   Mike 
   102                   Robert 
   103                   Peter

NOT IN

NOT IN command reverses the result of IN command. It fetches records with values that don’t match with the given list.

Example

The following example fetches records with employee numbers not in 101, 102 and 103.

SELECT * FROM  
Employee 
WHERE EmployeeNo not in (101,102,103);

The above query returns the following records.

*** Query completed. 2 rows found. 6 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo          FirstName                      LastName 
----------- ------------------------------ -----------------------------    
    104                Alex                          Stuart 
    105                Robert                        James
Advertisements