Oracle SQL NULL Values: 1Z0-071 Series

1Z0-071 Exam Prep Chapter 2 & 3 Volume 1

NULL shows up everywhere on this exam: WHERE clauses, aggregate functions, JOINs, ORDER BY, and single-row functions. Get the mental model wrong once and it costs you marks across multiple topics. So let's get it right.

In this post

1. What NULL actually is
2. Three-valued logic — why rows go missing silently
3. The comparison operator trap
4. IS NULL and IS NOT NULL — the only safe test
5. NULL in arithmetic and string expressions
6. NULL in aggregate functions
7. NVL, NVL2, NULLIF, COALESCE
8. Quick reference
9. Practice questions

01 — What NULL actually is

NULL means the value is unknown or absent. Not zero. Not an empty string. Just — not there.

When a row is inserted without a value for a column, that column holds NULL. When you explicitly insert NULL, same result. Oracle can't tell the difference between the two, and neither can a query.

-- Both rows below look identical from a query perspective
INSERT INTO employees (employee_id, last_name, salary, commission_pct)
VALUES (300, 'Ferris', 5000, NULL);

INSERT INTO employees (employee_id, last_name, salary)
VALUES (301, 'Grant', 5000);

Exam rule: An omitted column value and an explicit NULL are identical. No query can distinguish between them.

02 — Three-valued logic — why rows go missing silently

Normal logic is binary: something is TRUE or FALSE. Oracle SQL adds a third state — UNKNOWN. Any comparison involving NULL returns UNKNOWN.

The WHERE clause only keeps rows where the condition is TRUE. FALSE rows are excluded. UNKNOWN rows are also excluded — silently, with no error or warning. That's where candidates lose marks.

Expression	Result	Row returned?
5 = 5	TRUE	Yes
5 = 6	FALSE	No
NULL = NULL	UNKNOWN	No
NULL = 5	UNKNOWN	No
NULL != 5	UNKNOWN	No
NULL > 0	UNKNOWN	No

Watch out: WHERE commission_pct != 0.2 does NOT include employees with no commission. Their NULL value makes the comparison UNKNOWN — they're excluded just as cleanly as if it were FALSE.

03 — The comparison operator trap

The exam loves asking "how many rows does this query return?" with NULL rows in the mix. The answer is almost always fewer than candidates expect.

-- 107 employees total. 35 have NULL department_id.

SELECT * FROM employees WHERE department_id = 50;
-- Rows with department_id = 50 only. NULL rows excluded.

SELECT * FROM employees WHERE department_id != 50;
-- Only rows with a real value other than 50. NULL rows still excluded.

SELECT * FROM employees
WHERE department_id = 50 OR department_id != 50;
-- Still only 72 rows, not 107. The OR covers all real values
-- but NULL rows produce UNKNOWN on both sides — still excluded.

How to think about it: NULL is like an unknown stranger. You can't say they're your friend. You also can't say they're not your friend. The only question you can answer is: "Is this person a stranger?" — and that's exactly what IS NULL does.

04 — IS NULL and IS NOT NULL — the only safe test

IS NULL and IS NOT NULL are the only operators that return TRUE or FALSE when testing for NULL. Everything else returns UNKNOWN.

-- Correct
SELECT last_name FROM employees WHERE commission_pct IS NULL;
SELECT last_name FROM employees WHERE commission_pct IS NOT NULL;

-- Wrong — syntactically valid but always returns zero rows
SELECT last_name FROM employees WHERE commission_pct = NULL;
SELECT last_name FROM employees WHERE commission_pct != NULL;

Exam trap: = NULL and != NULL are valid SQL syntax in Oracle — they just never return any rows. The exam will put them in answer options knowing they look correct at first glance.

05 — NULL in arithmetic and string expressions

In arithmetic, NULL spreads. If any value in a calculation is NULL, the result is NULL. No exceptions.

String concatenation in Oracle is different — and this difference appears on the exam.

-- Arithmetic: NULL contaminates the result
SELECT salary + commission_pct FROM employees;
-- Result is NULL for anyone with no commission_pct

-- Fix it with NVL
SELECT salary * 12 + NVL(commission_pct, 0) * salary * 12 FROM employees;

-- String concatenation: Oracle treats NULL as empty string
SELECT 'Hello' || NULL || ' World' FROM dual;
-- Returns: 'Hello World'
-- This is Oracle-specific. The SQL standard says this should return NULL.

Oracle exception: Concatenating NULL with || treats NULL as an empty string. Arithmetic with NULL always gives NULL. The exam tests whether you know these two behave differently.

06 — NULL in aggregate functions

Most aggregate functions skip NULL rows. The one that doesn't — COUNT(*) — is also the one that trips people up most.

-- 107 total rows, 35 with NULL commission_pct

SELECT COUNT(*)              FROM employees;  -- 107 — counts every row
SELECT COUNT(commission_pct)  FROM employees;  -- 72  — NULL rows skipped
SELECT AVG(commission_pct)    FROM employees;  -- sum / 72, not sum / 107
SELECT SUM(commission_pct)    FROM employees;  -- only adds the 72 real values
SELECT MAX(commission_pct)    FROM employees;  -- NULL never wins a comparison

Function	NULL rows	Exam catch
COUNT(*)	Counted	Different result from COUNT(col) when column has NULLs
COUNT(col)	Skipped	Gives a lower number than COUNT(*)
AVG(col)	Skipped from both sum and count	Higher average than if NULLs were treated as zero
SUM(col)	Skipped	Safe — NULLs don't distort the total
MAX / MIN	Skipped	NULL is never the highest or lowest value

07 — NVL, NVL2, NULLIF, COALESCE

Four functions. Each solves a slightly different NULL problem. The exam asks you to tell them apart.

-- NVL: swap NULL for a default value
NVL(commission_pct, 0)
-- Returns 0 when commission_pct is NULL, otherwise commission_pct

-- NVL2: choose one value for NULL, another for non-NULL
NVL2(commission_pct, 'Has commission', 'No commission')
-- 'Has commission' when not null, 'No commission' when null

-- NULLIF: return NULL when two values match, otherwise return the first
NULLIF(hours_worked, 0)
-- Returns NULL if hours_worked = 0 (stops divide-by-zero)
-- Returns hours_worked for any other value

-- COALESCE: return the first non-NULL from a list
COALESCE(commission_pct, bonus_rate, 0)
-- Checks commission_pct first, then bonus_rate, then falls back to 0

NVL vs COALESCE: NVL always evaluates both arguments. COALESCE stops as soon as it finds something non-NULL. If you need to check a list of columns in order, COALESCE is the right answer — not multiple nested NVLs.

08 — Quick reference

Rule	Example	Result
Any comparison with NULL	NULL = NULL	UNKNOWN — not TRUE
UNKNOWN in WHERE	WHERE col != 5 (col is NULL)	Row not returned
Only correct NULL test	IS NULL / IS NOT NULL	Returns TRUE or FALSE
NULL in arithmetic	salary + NULL	NULL
NULL in concatenation	'A' || NULL	'A' (Oracle only)
COUNT(*) vs COUNT(col)	35 NULLs in 107 rows	107 vs 72
AVG skips NULLs	72 values across 107 rows	Divides by 72
ORDER BY ASC default	ORDER BY col ASC	NULLs sort last
Override sort position	ORDER BY col NULLS FIRST	NULLs appear first

09 — Practice questions

Q1 — EMPLOYEES has 107 rows. COMMISSION_PCT is NULL for 35 of them. How many rows does this return?

SELECT last_name FROM employees WHERE commission_pct != 0.25;

A) 107 B) 72 C) Fewer than 72 D) The query raises an error

Q2 — Which expression returns the string 'None' for employees who have no manager (manager_id is NULL)?

A) WHERE manager_id = NULL B) NVL(TO_CHAR(manager_id), 'None') C) COALESCE(TO_CHAR(manager_id), 'None') D) Both B and C

Q3 — What does SELECT AVG(commission_pct) FROM employees return when 35 of 107 employees have NULL commission_pct?

A) Sum of commissions divided by 107 B) Sum of commissions divided by 72 C) NULL, because some values are NULL D) Zero, because Oracle treats NULL as 0 in aggregates

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Every exam topic covered — NULL, joins, subqueries, DDL, DML, views, sequences, and more. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.