Creating complex criteria

To build complex conditions are the operators AND and OR.

Condition (X AND Y) is true if and only if X is true and Y is true.

Condition (X OR Y) is true if and only if X is true or Y is true.

The AND operator has higher precedence than the OR.

To select all employees as a 'manager' and earning more than 2000, type:

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL > 2000
AND JOB = 'MANAGER'

Since the AND operator has higher precedence than OR, the following example we will all persons employed as a 'CLERK' and those persons employed as a 'manager', whose earnings are above 1000.

SELECT ENAME, JOB, SAL FROM EMP WHERE SAL > 1000 AND JOB = 'MANAGER' OR JOB = 'CLERK';

The following example, which uses brackets changing the sequence of actions will select only those employed as a 'CLERK' or as a 'manager', which at the same time earn more than 1000.

SELECT ENAME, JOB, SAL FROM EMP WHERE SAL > 1000
AND (JOB = 'MANAGER' OR JOB = 'CLERK');


The hierarchy of operators

In any expression unless otherwise indicated by parentheses, operations are performed starting from the operators of the highest priority. If the two operators with the same priority exist side by side, they are executed from left to right.

1 =, <>, <=,> =,>, <, BETWEEN ... AND, IN, LIKE, IS NULL
2 NOT
3 AND
4 OR

To write a more transparent and to avoid errors, it is recommended to use parentheses.