ExamFinch.com My Study

Study guide

3.2 Logical Conditions & Operator Precedence

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

3.2 Logical Conditions & Operator Precedence

Key Takeaways Explain This Topic AI

Oracle SQL evaluates compound conditions using Three-Valued Logic (3VL): TRUE, FALSE, and UNKNOWN.

The AND operator requires both conditions to be TRUE, whereas OR requires only one condition to be TRUE.

Oracle evaluates arithmetic first, then concatenation, then all SQL conditions (comparison operators, IS NULL, LIKE, BETWEEN, IN, EXISTS) at one equal precedence level, then NOT, then AND, then OR.

Because AND has higher precedence than OR, unparenthesized compound conditions evaluate AND clauses first, frequently resulting in unexpected row inclusion.

The condition x NOT IN (a, b, NULL) expands to (x != a AND x != b AND x != NULL), which evaluates to UNKNOWN for all rows where x is not in {a, b}, returning zero rows.

Last updated: August 2026

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:

AND : Returns TRUE if both component conditions evaluate to TRUE .

OR : Returns TRUE if either component condition evaluates to TRUE .

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.

  • Operand 1 Operator Operand 2 Result
  • TRUE AND TRUE TRUE
  • TRUE AND FALSE FALSE
  • TRUE AND UNKNOWN UNKNOWN
  • FALSE AND TRUE FALSE
  • FALSE AND FALSE FALSE
  • FALSE AND UNKNOWN FALSE
  • UNKNOWN AND TRUE UNKNOWN
  • UNKNOWN AND FALSE FALSE
  • UNKNOWN AND UNKNOWN UNKNOWN
  • 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 .

  • Operand 1 Operator Operand 2 Result
  • TRUE OR TRUE TRUE
  • TRUE OR FALSE TRUE
  • TRUE OR UNKNOWN TRUE
  • FALSE OR TRUE TRUE
  • FALSE OR FALSE FALSE
  • FALSE OR UNKNOWN UNKNOWN
  • UNKNOWN OR TRUE TRUE
  • UNKNOWN OR FALSE UNKNOWN
  • UNKNOWN OR UNKNOWN UNKNOWN
  • The NOT Truth Table
  • Operand Operator Result
  • TRUE NOT FALSE
  • FALSE NOT TRUE
  • UNKNOWN NOT UNKNOWN

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.

  • Precedence Level Category Operators
  • 1 (Highest) Arithmetic Operators * , / , + , - (and unary + , - )
  • 2 Concatenation ||

3 SQL Conditions (all equal to one another) = , != , <> , ^= , < , > , <= , >= , IS [NOT] NULL , [NOT] LIKE , [NOT] BETWEEN ... AND ... , [NOT] IN , EXISTS

  • 4 Logical Negation NOT
  • 5 Logical Conjunction AND
  • 6 (Lowest) Logical Disjunction OR

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:

Oracle encounters OR and AND .

Because AND has higher precedence than OR , Oracle evaluates department_id = 80 AND salary > 10000 first.

The condition is interpreted as: (department_id = 50) OR ((department_id = 80) AND (salary > 10000)) .

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'

Step 1 (Comparisons) : Evaluate comparisons department_id = 50 , salary > 5000 , job_id = 'AD_PRES' .

Step 2 ( NOT ) : Evaluate NOT (department_id = 50) , equivalent to department_id <> 50 .

Step 3 ( AND ) : Evaluate (department_id <> 50) AND (salary > 5000) .

Step 4 ( OR ) : Evaluate ((department_id <> 50 AND salary > 5000) OR (job_id = 'AD_PRES')) .