Logical Conditions

A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 6-4 lists logical conditions.

NOT 

SELECT *
  FROM employees
  WHERE NOT (job_id IS NULL)
  ORDER BY employee_id;
SELECT *
  FROM employees
  WHERE NOT 
  (salary BETWEEN 1000 AND 2000)
  ORDER BY employee_id;

AND 

SELECT *
  FROM employees
  WHERE job_id = 'PU_CLERK'
  AND department_id = 30
  ORDER BY employee_id;

OR 

SELECT *
  FROM employees
  WHERE job_id = 'PU_CLERK'
  OR department_id = 10
  ORDER BY employee_id;

Table 6-5 shows the result of applying the NOT condition to an expression.

Table 6-6 shows the results of combining the AND condition to two expressions.

For example, in the WHERE clause of the following SELECT statement, the AND logical condition is used to ensure that only those hired before 2004 and earning more than $2500 a month are returned:

SELECT * FROM employees
WHERE hire_date < TO_DATE('01-JAN-2004', 'DD-MON-YYYY')
  AND salary > 2500
  ORDER BY employee_id;

Table 6-7 shows the results of applying OR to two expressions.

For example, the following query returns employees who have a 40% commission rate or a salary greater than $20,000:

SELECT employee_id FROM employees
   WHERE commission_pct = .4 OR salary > 20000
   ORDER BY employee_id;