ExamFinch.com My Study

Study guide

2.3 Handling NULLs & Distinct Rows in Projection

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

2.3 Handling NULLs & Distinct Rows in Projection

Key Takeaways Explain This Topic AI

A NULL represents a missing, unknown, unassigned, or inapplicable value; it is neither numeric zero (0) nor an empty space (' ').

Any arithmetic expression (+, -, *, /) that operates on a NULL value evaluates strictly to NULL.

The DISTINCT (or UNIQUE) keyword eliminates duplicate rows from the query output, ensuring each row in the result set is unique across the projected columns.

DISTINCT applies to the entire combination of all selected columns in the SELECT list, never to an individual column alone.

DISTINCT must be placed immediately following the SELECT keyword; placing it after column names or in expression lists causes a syntax error.

Last updated: August 2026

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. |
  • +-----------------------------------------------------------------------------+
  • Attribute Numeric Zero ( 0 ) Blank Space ( ' ' ) NULL
  • Has Data? Yes Yes (ASCII 32) No
  • Storage Type 1-byte number 1-byte character 0 bytes (or flag)
  • Arithmetic Result 100 + 0 = 100 Type conversion error 100 + NULL = 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:

Immediate Placement: DISTINCT MUST appear immediately after the SELECT keyword.

Single Occurrence: You cannot specify DISTINCT multiple times in the same SELECT clause.

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:

  • Employee DEPARTMENT_ID JOB_ID
  • King 90 AD_PRES
  • Kochhar 90 AD_VP
  • De Haan 90 AD_VP
  • Ernst 60 IT_PROG
  • Hunold 60 IT_PROG
  • 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.