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:
The join statement returns:
|10012||Watson L||Vice Pres||500||ATL|
|10014||Inglis C||Tech Writer||500||ATL|
|10019||Newman P||Test Tech||600||CHI|