2.3 Handling NULLs & Distinct Rows in Projection

Handling NULLs & Distinct Rows in Projection

Understanding how Oracle Database handles missing data and duplicate records is critical for writing accurate queries and avoiding common data retrieval bugs. On the 1Z0-071 exam, questions frequently test your ability to predict NULL propagation in arithmetic expressions and evaluate the exact behavior of the DISTINCT keyword across single and multiple columns.

---

1. The Nature and Definition of NULL

In relational database theory, a NULL value represents:

• An absence of data
• An unassigned, unknown, or unavailable value
• An inapplicable attribute (e.g., commission percentage for a non-sales employee)

+-----------------------------------------------------------------------------+
|                           WHAT NULL IS AND IS NOT                           |
|                                                                             |
|   [WHAT NULL IS NOT]                                                        |
|   - NULL is NOT the number zero (0 is a known, definitive integer).         |
|   - NULL is NOT a blank space (' ' is a single-character string).           |
|   - NULL is NOT the literal word 'NULL' or 'null'.                          |
|                                                                             |
|   [WHAT NULL IS]                                                            |
|   - An unassigned pointer / state of missing information.                   |
|   - In Oracle SQL, a zero-length VARCHAR2 string ('') is treated as NULL.   |
+-----------------------------------------------------------------------------+

---

2. NULL Propagation in Arithmetic Expressions

One of the most foundational rules in SQL is NULL propagation:

Any arithmetic calculation (+, -, *, /) containing a NULL value anywhere in the expression evaluates to NULL.

Why NULL Propagates:

If you earn a salary of $5,000 and are promised an unknown bonus (NULL), your total compensation is unknown (NULL). The database cannot fabricate a number when one operand is undefined.

SELECT employee_id, last_name, salary, commission_pct,
       salary * 12 + commission_pct AS "Total Comp"
FROM employees
WHERE department_id = 90;

Execution Breakdown and Output Simulation:

EMPLOYEE_ID LAST_NAME                     SALARY COMMISSION_PCT Total Comp
----------- ------------------------- ---------- -------------- ----------
        100 King                           24000                      NULL
        101 Kochhar                        17000                      NULL
        102 De Haan                        17000                      NULL

Because employees in Department 90 have a NULL commission percentage, 24000 * 12 + NULL evaluates to NULL, resulting in a completely blank value in the Total Comp column.

Division by Zero vs. Division by NULL

• Division by Zero: Executing salary / 0 throws a fatal runtime exception: ORA-01476: divisor is equal to zero.
• Division by NULL: Executing salary / NULL or NULL / 5 evaluates safely to NULL without throwing any error.

---

3. Suppressing Duplicate Rows with DISTINCT / UNIQUE

By default, a SQL query returns every row that satisfies the query criteria, including duplicate rows. This default behavior is represented by the optional ALL keyword.

To eliminate duplicate rows from your query results, use the DISTINCT keyword (or its Oracle synonym, UNIQUE).

-- Returns all 107 rows, with many duplicate department IDs
SELECT ALL department_id FROM employees;

-- Returns only unique department IDs (duplicates suppressed)
SELECT DISTINCT department_id FROM employees;

+-----------------------------------------------------------------------------+
|                        DEFAULT (ALL) VS. DISTINCT                           |
|                                                                             |
|   Query: SELECT department_id FROM employees; (Default ALL)                 |
|   Result: [ 90, 90, 90, 60, 60, 60, 60, 60, 100, 100, 100 ... ] (107 rows) |
|                                                                             |
|   Query: SELECT DISTINCT department_id FROM employees;                     |
|   Result: [ 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, NULL ] (12 rows)  |
+-----------------------------------------------------------------------------+

Key DISTINCT Placement Rules:

1. Immediate Placement: DISTINCT MUST appear immediately after the SELECT keyword.
2. Single Occurrence: You cannot specify DISTINCT multiple times in the same SELECT clause.
3. Placement Errors: Placing DISTINCT after a column name (e.g., SELECT department_id, DISTINCT job_id) causes ORA-00936: missing expression.

---

4. Multi-Column DISTINCT Mechanics

A critical concept on the 1Z0-071 exam is how DISTINCT behaves when multiple columns are listed in the SELECT clause.

[!IMPORTANT] The Multi-Column DISTINCT Rule: DISTINCT applies to ALL projected columns collectively as a composite set, NEVER just to the column immediately following the DISTINCT keyword.

When you execute:

SELECT DISTINCT department_id, job_id 
FROM employees;

Oracle does not return unique department IDs followed by arbitrary job IDs. Instead, it evaluates the pair (department_id, job_id) as a single composite unit. A row is only filtered out if another row has the exact same department_id AND the exact same job_id.

Walkthrough Example:

Consider a sample subset of employees:

Executing SELECT DISTINCT department_id, job_id FROM employees; produces:

DEPARTMENT_ID JOB_ID
------------- ----------
           60 IT_PROG    <-- Ernst & Hunold collapsed into 1 row
           90 AD_PRES    <-- King
           90 AD_VP      <-- Kochhar & De Haan collapsed into 1 row

Notice that Department 90 appears twice because (90, AD_PRES) and (90, AD_VP) are distinct composite combinations.

---

5. How DISTINCT Treats NULL Values

When applying DISTINCT to a column containing missing data:

• Oracle treats all NULL values as duplicates of one another.
• If multiple rows contain NULL in the projected column, the query returns only one row with NULL.
• In multi-column DISTINCT queries, (90, NULL) and (90, NULL) collapse into one row, but (90, NULL) and (60, NULL) remain distinct.

---

6. Common 1Z0-071 Exam Traps & Errors

Trap 1: Assuming DISTINCT applies only to the first column

Candidates often believe SELECT DISTINCT department_id, job_id makes only department_id unique. Remember: DISTINCT qualifies the entire row projection.

Trap 2: Invalid DISTINCT placement in column list

-- SYNTAX ERROR: ORA-00936: missing expression
SELECT department_id, DISTINCT job_id 
FROM employees;

Trap 3: Expecting arithmetic with NULL to default to 0

In expressions like salary * (1 + commission_pct), if commission_pct is NULL, the entire expression evaluates to NULL, not salary * 1.

Trap 4: Confusion between DISTINCT and UNIQUE

In Oracle SQL SELECT queries, UNIQUE is a valid synonym for DISTINCT (SELECT UNIQUE department_id FROM employees;). Both produce identical result sets, although DISTINCT is the ANSI SQL standard.