3.1 WHERE Clause Filtering & Comparison Operators

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.1 WHERE Clause Filtering & Comparison Operators

Key Takeaways Explain This Topic AI

The WHERE clause directly follows the FROM clause and restricts rows returned before grouping, projection, or sorting occurs.

Character and date literals in WHERE conditions are strictly case-sensitive and format-sensitive, always enclosed in single quotation marks.

The BETWEEN ... AND ... operator is inclusive of both endpoints and requires the lower bound to be specified first (specifying the higher bound first returns zero rows).

The LIKE operator performs pattern matching using wildcard characters (% for zero or more characters, _ for exactly one character), with literal wildcards escaped via the ESCAPE identifier.

Direct comparisons with NULL using = or != always evaluate to UNKNOWN and return zero rows; IS NULL or IS NOT NULL must always be used.

Last updated: August 2026

3.1 WHERE Clause Filtering & Comparison Operators

Quick Answer: The WHERE clause filters rows returned by a query based on one or more boolean conditions. It executes immediately after the FROM clause and before SELECT or ORDER BY . Comparisons use standard relational operators ( = , <> , != , ^= , < , <= , > , >= ), range operators ( BETWEEN ... AND ... ), list membership ( IN (...) ), pattern matching ( LIKE with % and _ ), and null validation ( IS NULL , IS NOT NULL ). Direct equality comparisons with NULL (such as = NULL ) evaluate to UNKNOWN and return zero rows.

Syntax & Logical Placement of the WHERE Clause

In standard SQL syntax, the WHERE clause appears immediately after the FROM clause. The database engine evaluates the WHERE clause during row retrieval before computing column aliases, aggregate calculations, or sort orders. SELECT employee_id, first_name, last_name, salary, department_id

FROM employees
WHERE department_id = 60;
Logical Execution Order

Understanding query execution order is fundamental for the 1Z0-071 exam:

FROM : Identifies and accesses the source tables or views.

WHERE : Filters rows according to predicate conditions.

GROUP BY : Groups surviving rows (if specified).

HAVING : Filters groups (if specified).

SELECT : Evaluates expressions, single-row functions, and assigns column aliases.

ORDER BY : Sorts the final output rows.

Because WHERE executes before SELECT , column aliases defined in the SELECT list cannot be referenced in the WHERE clause . Attempting to do so triggers an ORA-00904: invalid identifier error. -- INVALID: Triggers ORA-00904 because annual_salary is evaluated after WHERE

SELECT employee_id, last_name, salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 50000;

- VALID: Use the underlying expression directly
SELECT employee_id, last_name, salary * 12 AS annual_salary
FROM employees
WHERE (salary * 12) > 50000;
Oracle Comparison Operators

Oracle SQL supports standard relational comparison operators that compare numbers, character strings, and dates.

Operator Meaning Example True Condition Description
= Equal to salary = 5000 Salary exactly matches 5000
!= , <> , ^= Not equal to department_id <> 90 Department is anything other than 90
> Greater than hire_date > '01-JAN-20' Hired after January 1, 2020
>= Greater than or equal to salary >= 10000 Salary is 10,000 or more
< Less than commission_pct < 0.2 Commission is strictly under 20%
<= Less than or equal to salary <= 4000 Salary is 4,000 or less

Note

Oracle supports three distinct syntax forms for "not equal to": != , <> , and ^= . All three are completely interchangeable in SQL queries, though <> is the ANSI/ISO standard.

Character and Date Literals in the WHERE Clause

When filtering by character or date columns, strict formatting and casing rules apply:

Enclosed in Single Quotes : All character and date literals must be enclosed in single quotation marks ( '...' ). Numeric literals do not use quotes.

Case Sensitivity : Character comparisons are case-sensitive. The value 'King' is not equal to 'KING' or 'king' .

Date Format Sensitivity : Dates compared as string literals must match the session's default NLS date format (typically DD-MON-RR or DD-MON-YYYY in standard English locales). -- Case-sensitive match: Finds King, but ignores KING or king

SELECT employee_id, last_name
FROM employees
WHERE last_name = 'King';

- Date comparison using default format
SELECT employee_id, hire_date
FROM employees
WHERE hire_date >= '17-JUN-2003';

- Explicit conversion (Best Practice to avoid NLS dependency)
SELECT employee_id, hire_date
FROM employees
WHERE hire_date >= TO_DATE('2003-06-17', 'YYYY-MM-DD');

Range Filtering with BETWEEN ... AND ...

The BETWEEN ... AND ... operator tests whether an expression falls within an inclusive range of values. SELECT employee_id, last_name, salary

FROM employees
WHERE salary BETWEEN 2500 AND 3500;
The query above is logically equivalent to: SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= 2500 AND salary <= 3500;
The Reversed Bound Trap

Oracle requires the lower bound to be specified first and the higher bound second . If you reverse the order, Oracle evaluates the condition as val >= higher AND val <= lower , which is mathematically impossible and silently returns zero rows without throwing an error. -- RETURNS ZERO ROWS (salary cannot be >= 3500 AND <= 2500 simultaneously)

SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN 3500 AND 2500;
Non-Numeric BETWEEN Conditions

BETWEEN works on character strings and dates using standard lexicographical and chronological ordering: -- Lexicographical range (inclusive of 'A' up to exactly 'C')

- Note: 'Carl' is NOT included because 'Carl' > 'C'
SELECT last_name
FROM employees
WHERE last_name BETWEEN 'A' AND 'C';
Set Membership Filtering with the IN Operator

The IN operator tests whether a value matches any member of a specified list of literals, expressions, or subquery results. It acts as an abbreviated shorthand for a chain of OR conditions. SELECT employee_id, last_name, manager_id

FROM employees
WHERE manager_id IN (100, 101, 201);

-- Logically equivalent to:

- WHERE manager_id = 100 OR manager_id = 101 OR manager_id = 201;
IN lists support character strings, numbers, and dates: SELECT employee_id, job_id
FROM employees
WHERE job_id IN ('IT_PROG', 'SA_REP', 'HR_REP');
Pattern Matching with LIKE & the ESCAPE Clause

The LIKE operator performs pattern matching on character expressions using two specialized wildcard symbols:

% (Percent): Matches zero or more characters of any kind.

_ (Underscore): Matches exactly one single character.

Wildcard Pattern Examples

Pattern Expression Matches
LIKE 'S%' Any string starting with capital 'S' (e.g., 'Smith', 'Steven', 'S')
LIKE '%s' Any string ending with lowercase 's' (e.g., 'Jones', 'Matthews')
LIKE '%am%' Any string containing the substring 'am' anywhere
LIKE '_o%' Any string where the second character is lowercase 'o' (e.g., 'Kochhar')
LIKE '___' Any string containing exactly three characters
LIKE '_a%e' Second character is 'a' and last character is 'e' (e.g., 'James')
Searching for Literal Wildcards Using the ESCAPE Clause

When searching for strings that literally contain % or _ characters (such as job IDs like IT_PROG or discount codes like 10%_OFF ), you must define an escape character using the ESCAPE clause. -- Search for job IDs starting with 'SA_' literally

SELECT job_id, job_title
FROM jobs
WHERE job_id LIKE 'SA\_%' ESCAPE '\';

In this query:

The ESCAPE '\' clause informs Oracle that the backslash \ is the escape identifier.

In the pattern 'SA\_%' , the \_ sequence instructs Oracle to treat _ as a literal underscore rather than a single-character wildcard.

The trailing % remains a regular wildcard matching zero or more remaining characters.

You can designate any valid single character as your escape identifier: -- Using '#' as the escape character
SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%10#%%OFF' ESCAPE '#';
The NULL Comparison Trap: IS NULL vs. = NULL

A NULL represents an absent, unknown, or unassigned value. In relational database theory and Oracle SQL:

NULL is not equal to anything, including another NULL .

Any arithmetic or direct comparison involving NULL ( = NULL , != NULL , <> NULL ) yields UNKNOWN .

Because a WHERE clause only accepts rows where the condition evaluates to TRUE , UNKNOWN conditions fail to qualify and return zero rows . -- WRONG: Evaluates to UNKNOWN for every row; returns 0 rows!

SELECT employee_id, last_name
FROM employees
WHERE commission_pct = NULL;

- CORRECT: Tests for presence of NULL
SELECT employee_id, last_name
FROM employees
WHERE commission_pct IS NULL;

- CORRECT: Tests for non-null values
SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
Summary of 1Z0-071 Exam Traps

Aliases in WHERE : You cannot use SELECT aliases in the WHERE clause due to execution order.

Reversed BETWEEN bounds : BETWEEN 100 AND 50 always yields zero rows.

Literal Case Sensitivity : 'SMITH' does not match 'Smith' unless converted with UPPER() .

The = NULL Bug : Testing for nulls with = or != never returns rows; always write IS NULL or IS NOT NULL .

Single Quotes vs. Double Quotes : WHERE filter values use single quotes ( 'value' ). Double quotes ( "col" ) are reserved for case-sensitive column/object aliases.