Teradata Logical Operators

Share via:

Dear Readers,

In this article, we will see Teradata Logical Operators.

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 <expression>
NOT IN If the values not in <expression>
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
Teradata AND Condition

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 :

Teradata OR Condition
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 :

Teradata BETWEEN Condition
  • 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’.

Teradata IN Condition

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

Example

The following example fetches records with ID1 and 5.

Teradata NOT IN Condition

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.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment