ExamFinch.com My Study

Study guide

5.3 General NULL-Handling Functions

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

5.3 General NULL-Handling Functions

Key Takeaways Explain This Topic AI

NVL(expr1, expr2) replaces a NULL expr1 with expr2; if datatypes differ, Oracle implicitly converts expr2 to match expr1's datatype.

NVL2(expr1, expr2, expr3) returns expr2 if expr1 is NOT NULL, and expr3 if expr1 is NULL; the return datatype is always determined by expr2.

NULLIF(expr1, expr2) compares two expressions and returns NULL if they are equal, or expr1 if they differ; expr1 cannot be a literal NULL.

COALESCE(expr1, expr2, ..., exprn) returns the first non-null expression in its argument list and utilizes short-circuit (lazy) evaluation.

COALESCE is an ANSI SQL standard function, whereas NVL and NVL2 are Oracle proprietary; all expressions in COALESCE must share a compatible datatype with the first expression.

Last updated: August 2026

5.3 General NULL-Handling Functions

In relational database theory and Oracle SQL, NULL represents a state of missing, unknown, unassigned, or inapplicable data. Because NULL does not represent a known value, standard arithmetic operations and comparison operators exhibit distinct three-valued logic ( T R U E TRUE TR U E , F A L S E FALSE F A L SE , U N K N O W N UNKNOWN U N K NO W N ):

Any arithmetic operation involving NULL yields NULL (e.g., salary + commission_pct yields NULL if commission is null).

Standard equality comparisons against NULL yield UNKNOWN rather than TRUE (e.g., commission_pct = NULL never matches any row).

To manage null values safely in calculations, reports, and logical predicates, Oracle SQL provides four general NULL-handling functions: NVL , NVL2 , NULLIF , and COALESCE .

1. The NVL Function

The NVL(expr1, expr2) function replaces a NULL value with a meaningful alternate value. +------------------------+

  • | NVL(expr1, expr2) |
  • +------------------------+
  • |
  • +----------------+----------------+
  • | |
  • [expr1 IS NOT NULL] [expr1 IS NULL]
  • | |
  • v v
  • Returns: expr1 Returns: expr2
  • Syntax & Datatype Rules: NVL(expr1, expr2)

If expr1 is NOT NULL , NVL returns expr1 .

If expr1 is NULL , NVL returns expr2 .

Datatype Compatibility Rule: The datatypes of expr1 and expr2 must be compatible. If they differ, Oracle implicitly converts expr2 to the datatype of expr1 before returning a value. -- Calculating total compensation (replacing NULL commission with 0):

  • SELECT employee_id,
  • salary + (salary * NVL(commission_pct, 0)) AS total_compensation
  • FROM employees;

-- Datatype coercion example: '100' is implicitly converted to NUMBER (datatype of expr1):

SELECT NVL(100, '200') FROM dual; --> Returns 100 (NUMBER)

-- Datatype mismatch error: Oracle tries to convert 'None' to NUMBER and fails:

  • SELECT NVL(commission_pct, 'None') FROM employees;
  • - Error: ORA-01722: invalid number

-- Correct approach with explicit conversion:

SELECT NVL(TO_CHAR(commission_pct), 'None') FROM employees;

Exam Watchout: NVL does not short-circuit. Both expr1 and expr2 are always evaluated by the SQL engine, even when expr1 is not null.

2. The NVL2 Function

The NVL2(expr1, expr2, expr3) function provides three-parameter conditional substitution based on whether the first expression is null or not null. +------------------------------+

  • | NVL2(expr1, expr2, expr3) |
  • +------------------------------+
  • |
  • +----------------+----------------+
  • | |
  • [expr1 IS NOT NULL] [expr1 IS NULL]
  • | |
  • v v
  • Returns: expr2 Returns: expr3
  • Syntax & Datatype Rules: NVL2(expr1, expr2, expr3)

If expr1 is NOT NULL , NVL2 returns expr2 .

If expr1 is NULL , NVL2 returns expr3 .

Datatype Rule: expr1 can be of any datatype . The return datatype of the function is determined by expr2 :

If expr2 is numeric, expr3 is converted to numeric.

If expr2 is character data, expr3 is converted to character ( VARCHAR2 ). -- Displaying compensation status:

  • SELECT last_name,
  • salary,
  • NVL2(commission_pct, 'Salary + Comm', 'Salary Only') AS income_type,
  • NVL2(commission_pct, salary + (salary * commission_pct), salary) AS net_pay
  • FROM employees;

Exam Trap: In NVL2 , the second parameter ( expr2 ) corresponds to the NOT NULL state, and the third parameter ( expr3 ) corresponds to the NULL state. Do not invert the arguments!

3. The NULLIF Function

The NULLIF(expr1, expr2) function compares two expressions and returns NULL if they are equal, or expr1 if they are not equal. +------------------------+

  • | NULLIF(expr1, expr2) |
  • +------------------------+
  • |
  • +----------------+----------------+
  • | |
  • [expr1 = expr2] [expr1 != expr2]
  • | |
  • v v
  • Returns: NULL Returns: expr1
  • Syntax & Critical Restrictions: NULLIF(expr1, expr2)

If expr1 equals expr2 , NULLIF returns NULL .

If expr1 does not equal expr2 , NULLIF returns expr1 .

The Literal NULL Restriction: The first argument expr1 CANNOT be the literal keyword NULL . Writing NULLIF(NULL, expr2) causes an immediate syntax error ( ORA-00932: inconsistent datatypes or ORA-00904 ).

expr1 and expr2 must be of comparable datatypes.

Practical Applications of NULLIF:

  • Preventing Division-by-Zero Errors ( ORA-01476 ): SELECT department_id,
  • total_sales / NULLIF(staff_count, 0) AS sales_per_rep
  • FROM department_stats;

If staff_count is 0 , NULLIF(staff_count, 0) returns NULL , and total_sales / NULL safely yields NULL instead of crashing the query with a division-by-zero error.

Cleansing Sentinel / Default Values: -- Converting dummy text 'N/A' into true NULL:

  • SELECT customer_id, NULLIF(phone_number, 'N/A') AS clean_phone
  • FROM customers;
  • 4. The COALESCE Function

The COALESCE(expr1, expr2, ..., exprn) function evaluates a list of expressions from left to right and returns the first non-null expression encountered. +-------------------------------------------------------------------------+

  • | COALESCE(expr1, expr2, expr3, ..., exprN) |
  • +-------------------------------------------------------------------------+
  • | Evaluate expr1 -> NOT NULL? ------> RETURN expr1 |
  • | | (is NULL) |
  • | v |
  • | Evaluate expr2 -> NOT NULL? ------> RETURN expr2 |
  • | | (is NULL) |
  • | v |
  • | Evaluate expr3 -> NOT NULL? ------> RETURN expr3 |
  • | | (is NULL) |
  • | v |
  • | ... All NULL? --------------------> RETURN NULL |
  • +-------------------------------------------------------------------------+
  • Syntax & Characteristics: COALESCE(expr1, expr2, ..., exprn)

Requires at least two arguments .

Returns the value of the first expression that does not evaluate to NULL .

If all arguments evaluate to NULL , COALESCE returns NULL .

Short-Circuit (Lazy) Evaluation: COALESCE stops evaluating expressions as soon as a non-null value is found. Expressions appearing after the first non-null argument are never executed, offering performance savings and error prevention.

Datatype Consistency: All expressions in the parameter list must share a compatible datatype with the first expression ( expr1 ) . -- Cascading contact lookup:

  • SELECT customer_id,
  • COALESCE(mobile_phone, business_phone, home_phone, 'No Contact Available') AS contact_info
  • FROM customer_contacts;
  • Comprehensive NULL-Handling Functions Comparison
  • Feature NVL NVL2 NULLIF COALESCE
  • Number of Arguments Exactly 2 Exactly 3 Exactly 2 2 or more ( N ≥ 2 N \ge 2 N ≥ 2 )

Logic Returns expr2 if expr1 is NULL Returns expr2 if expr1 NOT NULL; expr3 if NULL Returns NULL if expr1 = expr2 ; else expr1 Returns first non-null expression

  • Return Datatype Rule Datatype of expr1 Datatype of expr2 Datatype of expr1 Datatype of expr1
  • Short-Circuit Evaluation? No (evaluates both) No (evaluates all) No Yes (stops at first non-null)
  • Standard Compliance Oracle Proprietary Oracle Proprietary ANSI SQL Standard ANSI SQL Standard

Key Syntax Restriction Types must be compatible Types of expr2/3 must match expr1 cannot be literal NULL All types must match expr1