16.20 - Examples of Logical Operators in Search Conditions - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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

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') ;

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