15.00 - Logical Operators and Search Conditions - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Logical Operators and Search Conditions

A search condition, or conditional expression, consists of one or more conditional terms connected by one or more of the following logical predicates:

• Comparison operators
• BETWEEN/NOT BETWEEN
• LIKE
• IN/NOT IN
• ALL or ANY/SOME
• EXISTS/NOT EXISTS
• OVERLAPS
• IS NULL/IS NOT NULL
• Logical Operators

An operator applied to the result of a predicate to determine the result of a search condition.

The logical operators are:

• AND
• NOT
• OR
• For example:

Use NOT to negate an expression, for example:

Where To Use Search Conditions

A search condition can be used in various SQL clauses such as WHERE, ON, QUALIFY, RESET WHEN, or HAVING.

When used in a HAVING clause, a logical expression can be used with an aggregate operator.

For example, the following query uses a search condition in a HAVING clause to select from the Employee table those departments with the number 100, 300, 500, or 600, and with a salary average of at least \$35,000 but not more than \$55,000:

SELECT AVG(Salary)
FROM Employee
WHERE DeptNo IN (100,300,500,600)
GROUP BY DeptNo
HAVING AVG(Salary) BETWEEN 35000 AND 55000 ;

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:
•
 1 NOT 2 AND 3 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:

F2/(NULLIF(F1,0)) > 500
F1 <> 0 AND F2/F1 > 500

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.

Evaluation Results

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

• TRUE
• FALSE
• UNKNOWN
• 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

Subquery Restrictions

Predicates in search conditions cannot specify SELECT AND CONSUME statements in subqueries.

Examples of Logical Operators in Search Conditions

The following examples illustrate the use of logical operators in search conditions.

Example 1

The following example uses a search condition to select from a user table named Profile the names of applicants who have either more than two years of experience or at least twelve years of schooling with a high school diploma:

SELECT Name
FROM Profile
WHERE YrsExp > 2
OR (EdLev >= 12 AND Grad = 'Y') ;

Example 2

The following statement requests a list of all the employees who report to manager number 10007 or manager number 10012. The manager information is contained in the Department table, while the employee information is contained in the Employee table. The request is processed by joining the tables on DeptNo, their common column.

DeptNo must be fully qualified in every reference to avoid ambiguity and an extra set of parentheses is needed to group the ORed IN conditions. Without them, the result is a Cartesian product.

SELECT EmpNo,Name,JobTitle,Employee.DeptNo,Loc
FROM Employee,Department
WHERE (Employee.DeptNo=Department.DeptNo)
AND ((Employee.DeptNo IN
(SELECT Department.DeptNo
FROM Department
WHERE MgrNo=10007))
OR (Employee.DeptNo IN
(SELECT Department.DeptNo
FROM Department
WHERE MgrNo=10012))) ;

Assuming that the Department table contains the following rows:

 DeptNo Department Loc MgrNo 100 Administration NYC 10005 600 Manufacturing CHI 10007 500 Engineering ATL 10012 300 Exec Office NYC 10018 700 Marketing NYC 10021

The join statement returns:

 EmpNo Name JobTitle DeptNo Loc 10012 Watson L Vice Pres 500 ATL 10004 Smith T Engineer 500 ATL 10014 Inglis C Tech Writer 500 ATL 10009 Marston A Secretary 500 ATL 10006 Kemper R Assembler 600 CHI 10015 Omura H Programmer 500 ATL 10007 Aguilar J Manager 600 CHI 10010 Reed C Technician 500 ATL 10013 Regan R Purchaser 600 CHI 10016 Carter J Engineer 500 ATL 10019 Newman P Test Tech 600 CHI