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