Logical Operator Examples | VantageCloud Lake - Examples: Logical Operators in Search Conditions - Teradata Vantage

The following examples show 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:

The join statement returns: