Share via:

Operators are symbols or words that cause an ‘operation’ to occur on one or more elements called ‘operands’.

Logical expressions combine operands and operators to produce a Boolean (True/False) result. These expressions can be combined into conditional expressions which are used in the WHERE clause of a SELECT statement.

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

##### Below are the logical operators which are used in Teradata.
 Operator Meaning = Equal to <> Not Equal to > Greater than < Less than > = Greater than or Equal to < = Less than or Equal to AND Combine multiple conditions. Evaluates to true only if all the conditions are met. OR Combine multiple condition. Evaluates to true if either of the condition is met. NOT Reverse the meaning of the condition BETWEEN If the values with range IN If the values in NOT IN If the values not in IS NULL If value is NULL IS NOT NULL If value is NOT NULL
##### Evaluation Results

Each logical expression in a conditional expression evaluates to one of three results:

• TRUE
• FALSE
• UNKNOWN

All the condition must be met for a specific row to be qualified.

Example

Consider the below EMPLOYEE table.

 ID FIRST_NAME LAST_NAME DEPARTMENT 1 Alex Green IT 2 Monica Adams Sales 3 Peter Williams IT 4 Alex Adams Finance 5 Mike Geller Sales

The following example will give output whose first name is FIRST_NAME is Alex AND DEPARTMENT is IT.

Below is the output returned :

###### Only one of the conditions need to be true for a row to be qualified.

Example

The following example will give output whose first name is FIRST_NAME is AlexORDEPARTMENT is IT.

Output :

• The BETWEEN condition is used to filter the selected rows for a specific range.
• There are two types of testing that can be performed with this operator :
• NUMERIC Range Testing :To locate rows for which a numeric column is within a range of values, use the BETWEEN <a> and <b>, specify the upper and lower range of values that qualify the row where <a>is the lower limit and <b>is the upper limit.
• CHARACTER Range Testing :Use the BETWEEN <a> and <b> operator To locate rows for which a character column is within a range of values. Specify the upper and lower range of values that qualify the row.
• BETWEEN will select those values which are greater than or equal to <a> and less or equal to <b>. (BETWEEN is inclusive).

Example

The following is a Numeric range testing example will give output whose ID is between 2 and 4.

The following is a Character range testing which will give output where Department names first alphabet is between ‘F’ and ‘S’.

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

Example

The following example fetches records with ID1 and 5.

NOT IN command reverses the result of IN command. It will return the result which are NOT IN the given list of values.

Example

The following example returns the records except the ID2,3 and 4.

##### Rules for Order of Evaluation

The following rules apply to evaluation order for conditional expressions:

• If an expression contains more than one of the same operator, the evaluation precedence is left to right.
• If an expression contains a combination of logical operators, the order of evaluation is as follows :
• NOT
• AND
• OR
• Parentheses can be used to establish the desired evaluation precedence.
• The logical expressions in a conditional expression are not always evaluated left to right.
• Avoid using a conditional expression if its accuracy depends on the order in which its logical expressions are evaluated.

For example, compare the following two expressions:

The first expression guarantees exclusion of division by zero.

The second allows the possibility of error, because the order of its evaluation determines the exclusion of zeros.

###### AND Truth Table

The following table illustrates the AND logic used in evaluating search conditions.

 x FALSE x UNKNOWN x TRUE y  FALSE FALSE FALSE FALSE y UNKNOWN FALSE UNKNOWN UNKNOWN y TRUE FALSE UNKNOWN TRUE
##### OR Truth Table

The following table illustrates the OR logic used in evaluating search conditions.

 x FALSE x UNKNOWN x TRUE y FALSE FALSE UNKNOWN TRUE y UNKNOWN UNKNOWN UNKNOWN TRUE y TRUE TRUE TRUE TRUE
##### NOT Truth Table

The following table illustrates the NOT logic used in evaluating search conditions.

 Result x FALSE TRUE x UNKNOWN UNKNOWN x TRUE FALSE
##### Effect of NULL on Logical Operators

The result of an AND comparison may be NULL if one or both sides of the expression are NULL. If at least one side of an AND operator is FALSE, the expression evaluates to FALSE:

The result of an OR comparison may be NULL if one or both sides of the expression are NULL. If at least one side of an OR operator is TRUE, the expression evaluates to TRUE:

Thank you for giving your valuable time to read the above information.     (No Ratings Yet) Loading...