Logical Operator Examples | Teradata Vantage - Examples of Logical Operators in Search Conditions - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantageā„¢

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