15.00 - Logical Operators and Search Conditions - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
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