3.1 WHERE Clause Filtering & Comparison Operators

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:

1. FROM: Identifies and accesses the source tables or views.
2. WHERE: Filters rows according to predicate conditions.
3. GROUP BY: Groups surviving rows (if specified).
4. HAVING: Filters groups (if specified).
5. SELECT: Evaluates expressions, single-row functions, and assigns column aliases.
6. 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.

[!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:

1. Enclosed in Single Quotes: All character and date literals must be enclosed in single quotation marks ('...'). Numeric literals do not use quotes.
2. Case Sensitivity: Character comparisons are case-sensitive. The value 'King' is not equal to 'KING' or 'king'.
3. 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

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:

1. The ESCAPE '\' clause informs Oracle that the backslash \ is the escape identifier.
2. In the pattern 'SA\_%', the \_ sequence instructs Oracle to treat _ as a literal underscore rather than a single-character wildcard.
3. 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

1. Aliases in WHERE: You cannot use SELECT aliases in the WHERE clause due to execution order.
2. Reversed BETWEEN bounds: BETWEEN 100 AND 50 always yields zero rows.
3. Literal Case Sensitivity: 'SMITH' does not match 'Smith' unless converted with UPPER().
4. The = NULL Bug: Testing for nulls with = or != never returns rows; always write IS NULL or IS NOT NULL.
5. Single Quotes vs. Double Quotes: WHERE filter values use single quotes ('value'). Double quotes ("col") are reserved for case-sensitive column/object aliases.