3.2 Logical Conditions & Operator Precedence

3.2 Logical Conditions & Operator Precedence

Quick Answer: Logical conditions combine multiple search criteria using AND, OR, and NOT. Oracle uses Three-Valued Logic (3VL), where expressions evaluate to TRUE, FALSE, or UNKNOWN (when evaluating NULL). In operator precedence, NOT binds tighter than AND, and AND binds tighter than OR. To prevent unexpected filtering behavior, always use parentheses to explicitly define compound boolean grouping.

---

The Three Logical Operators

When filtering rows, a query often needs to satisfy multiple criteria simultaneously or provide alternative criteria:

1. AND: Returns TRUE if both component conditions evaluate to TRUE.
2. OR: Returns TRUE if either component condition evaluates to TRUE.
3. NOT: Reverses the truth value of a condition (TRUE becomes FALSE, FALSE becomes TRUE, and UNKNOWN remains UNKNOWN).

-- Both conditions must be satisfied
SELECT employee_id, last_name, department_id, salary
FROM employees
WHERE department_id = 80 
  AND salary > 10000;

-- Either condition can be satisfied
SELECT employee_id, last_name, department_id, job_id
FROM employees
WHERE department_id = 50 
   OR job_id = 'IT_PROG';

---

Three-Valued Logic (3VL) Truth Matrices

In relational databases, because NULL represents missing or unknown data, comparison with NULL yields UNKNOWN. Oracle evaluates boolean conditions using Three-Valued Logic.

The AND Truth Table

For AND to return TRUE, both operands must be TRUE. If either operand is FALSE, the result is FALSE regardless of the other operand.

The OR Truth Table

For OR to return TRUE, at least one operand must be TRUE. If one operand is TRUE, the entire condition evaluates to TRUE even if the other operand is UNKNOWN.

The NOT Truth Table

[!IMPORTANT] A row is returned by a WHERE clause only if the final condition evaluates to TRUE. Rows evaluating to FALSE or UNKNOWN are excluded from the query result.

---

Oracle Operator Precedence Hierarchy

When an expression contains multiple operators without enclosing parentheses, Oracle applies default operator precedence rules. Operators with higher precedence are evaluated before operators with lower precedence.

Exam Precision: Oracle documents every condition — comparison operators, IS [NOT] NULL, LIKE, BETWEEN, IN, and EXISTS — at a single, equal precedence level. Only NOT, AND, and OR sit below them, in that order. Do not memorize an invented ranking that puts BETWEEN above or below IN; when two same-level conditions appear in one expression, use parentheses rather than relying on ordering.

---

Overriding Precedence with Parentheses

Because AND has higher precedence than OR, Oracle groups AND conditions first unless overridden with parentheses (...).

Example: Unparenthesized vs. Parenthesized Evaluation

Consider this query intended to find high-earning sales reps (salary > 10,000 in department 80) OR anyone in department 50:

SELECT last_name, department_id, salary
FROM employees
WHERE department_id = 50 
   OR department_id = 80 
  AND salary > 10000;

Step-by-Step Default Evaluation:

1. Oracle encounters OR and AND.
2. Because AND has higher precedence than OR, Oracle evaluates department_id = 80 AND salary > 10000 first.
3. The condition is interpreted as: (department_id = 50) OR ((department_id = 80) AND (salary > 10000)).
4. Result: Returns ALL employees in department 50 (regardless of their salary), plus employees in department 80 whose salary is over 10,000.

If the business requirement was to find employees in either department 50 or 80 who earn more than 10,000, parentheses are mandatory:

SELECT last_name, department_id, salary
FROM employees
WHERE (department_id = 50 OR department_id = 80)
  AND salary > 10000;

With parentheses, Oracle evaluates the (department_id = 50 OR department_id = 80) group first, and only returns employees from those two departments who earn over 10,000.

---

The NOT IN with NULL Trap

A classic 1Z0-071 exam question tests what happens when NOT IN encounters a list containing a NULL value.

Understanding the Expansion

Recall how IN and NOT IN expand logically:

• x IN (10, 20, NULL) expands to: (x = 10 OR x = 20 OR x = NULL)

  • If x = 10, (TRUE OR FALSE OR UNKNOWN) -> TRUE (Row returned!)
  • If x = 30, (FALSE OR FALSE OR UNKNOWN) -> UNKNOWN (Row excluded)

• x NOT IN (10, 20, NULL) expands to: (x != 10 AND x != 20 AND x != NULL)

  • If x = 10, (FALSE AND TRUE AND UNKNOWN) -> FALSE (Row excluded)
  • If x = 30, (TRUE AND TRUE AND UNKNOWN) -> UNKNOWN (Row excluded!)

Because x != NULL always evaluates to UNKNOWN, the entire AND chain can never evaluate to TRUE for any value of x. Consequently, x NOT IN (..., NULL) always returns ZERO rows.

-- DANGER: Returns 0 rows if any employee has manager_id IS NULL
SELECT employee_id, last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

-- SAFE: Explicitly filter out NULLs in the subquery
SELECT employee_id, last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

---

Complex Boolean Expression Walkthrough

Let us trace how Oracle evaluates a multi-operator condition step-by-step:

WHERE NOT department_id = 50 AND salary > 5000 OR job_id = 'AD_PRES'

1. Step 1 (Comparisons): Evaluate comparisons department_id = 50, salary > 5000, job_id = 'AD_PRES'.
2. Step 2 (NOT): Evaluate NOT (department_id = 50), equivalent to department_id <> 50.
3. Step 3 (AND): Evaluate (department_id <> 50) AND (salary > 5000).
4. Step 4 (OR): Evaluate ((department_id <> 50 AND salary > 5000) OR (job_id = 'AD_PRES')).