SELECT Semantics, NULL, Expressions, Conversion, NLS Effects, and Deterministic Ordering

Learning outcomes

ServiceHub is importing work-order events from several clients. One client sends an empty status string, another formats dates according to its local session, and a report uses ORDER BY priority even though many rows share the same priority. Every query looks reasonable in isolation, yet the application produces missing rows, ambiguous dates, and unstable pagination. This lesson turns those surprises into an Oracle-specific mental model.

1. Start with values, not their display

Oracle stores a DATE in a fixed internal representation containing year, month, day, hour, minute, and second. It does not store the string 25-AUG-26 or 2026-08-25. Those strings are presentation or conversion choices. Likewise, NUMBER is not stored with your session’s decimal separator. This distinction matters because client and session settings can change how Oracle converts between typed values and text.

The session parameter NLS_DATE_FORMAT controls the default format used when a DATE is implicitly converted to text or when text is implicitly converted to DATE. NLS_NUMERIC_CHARACTERS similarly affects decimal and group separators in character/number conversion. SQL Developer, OCI-based clients, and JDBC clients can initialize session NLS values differently, so a database-level default is not proof of what a particular session actually uses.

SELECT    SYS_CONTEXT('USERENV','CON_NAME') AS con_name,    SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schema,    SESSIONTIMEZONE AS session_tzFROM dual;SELECT parameter, valueFROM nls_session_parametersWHERE parameter IN (    'NLS_DATE_FORMAT',    'NLS_TIMESTAMP_FORMAT',    'NLS_TIMESTAMP_TZ_FORMAT',    'NLS_DATE_LANGUAGE',    'NLS_NUMERIC_CHARACTERS')ORDER BY parameter;

This evidence proves the current session’s settings. It does not prove that another connection pool, SQL Developer worksheet, or application session uses the same settings.

2. Oracle SQL currently collapses a zero-length character string to NULL

In Oracle SQL, a zero-length character string is currently treated as NULL. That means an application that tries to distinguish “empty but present” from “missing” using VARCHAR2 and the literal '' does not get two distinguishable SQL states. This is a major portability difference from PostgreSQL and from engines that preserve an empty string as a non-null value.

SELECT    CASE WHEN '' IS NULL THEN 'empty string behaves as NULL' ELSE 'distinct' END AS empty_test,    CASE WHEN LENGTH('') IS NULL THEN 'LENGTH returned NULL' ELSE 'length exists' END AS length_testFROM dual;SELECT 'matched' AS resultFROM dualWHERE '' = '';SELECT 'matched' AS resultFROM dualWHERE '' IS NULL;

The equality predicate does not return a row because comparisons with NULL evaluate to UNKNOWN, not true. IS NULL is the correct test. Do not “fix” this with = NULL; that has the same three-valued-logic problem.

3. Deliberately wrong approach: let NLS decide what a date string means

The most dangerous NLS bugs are often wrong results rather than syntax errors. The same text can represent different dates under different session formats. The following experiment changes only the session and asks Oracle to convert the same text without an explicit format model.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';SELECT TO_CHAR(TO_DATE('04-05-2026'), 'YYYY-MM-DD') AS interpreted_dateFROM dual;-- 2026-05-04ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY';SELECT TO_CHAR(TO_DATE('04-05-2026'), 'YYYY-MM-DD') AS interpreted_dateFROM dual;-- 2026-04-05

Nothing about the character literal changed; the session interpretation did. This is precisely why production SQL should not use implicit text-to-date conversion for fixed-format application data.

SELECT DATE '2026-05-04' AS typed_dateFROM dual;SELECT TO_DATE('2026-05-04', 'YYYY-MM-DD') AS parsed_dateFROM dual;SELECT TO_TIMESTAMP_TZ(         '2026-05-04 14:30:00 +04:00',         'YYYY-MM-DD HH24:MI:SS TZH:TZM'       ) AS parsed_timestampFROM dual;

Typed ANSI date literals are independent of NLS_DATE_FORMAT. Explicit format models make the text contract visible. Bind variables are even better when an application already holds a typed date/time value.

4. Logical query semantics and aliases

SQL is declarative: you describe the result, and Oracle is free to transform the execution as long as semantics are preserved. A useful reasoning order is FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, but this is a conceptual model, not a promise about physical execution order. The optimizer can reorder joins, push predicates, transform subqueries, and choose access paths.

One practical consequence is alias scope. A select-list alias is available to the final ORDER BY, but it is generally not available to the same query block’s WHERE clause because filtering conceptually occurs before projection.

SELECT event_id, total_amountFROM (    SELECT event_id,           labor_amount + parts_amount AS total_amount    FROM servicehub_sql_event)WHERE total_amount >= 500ORDER BY total_amount DESC, event_id;

This structure gives the expression a named result in an inner query block and then filters that result in an outer query block. Later lessons show how common table expressions and optimizer transformations affect the physical plan without changing the required result.

5. Deterministic ordering requires a complete tie-breaker

Without an ORDER BY, SQL result order is not guaranteed. Even with ORDER BY priority, rows that share the same priority have no specified relative order unless you add another ordering key. An index, hash join, parallel plan, changed statistics, or different fetch can expose that freedom.

SELECT event_id, priority, occurred_atFROM servicehub_sql_eventORDER BY priority DESC,         occurred_at DESC,         event_id ASC;

For pagination, the tie-breaker is essential. If the ordering keys are not unique, two executions can legally place equal-key rows differently, causing duplicates or omissions between pages. Also state the desired NULLS FIRST or NULLS LAST behavior when null placement matters to the application contract.

6. Hands-on lab: build a SELECT semantics probe

Run the lab as SERVICEHUB_OWNER in the disposable course PDB. It creates one small table, demonstrates Oracle’s empty-string behavior, records the session’s NLS state, and proves a deterministic order. Cleanup removes only the probe table.

DROP TABLE servicehub_sql_event IF EXISTS PURGE;CREATE TABLE servicehub_sql_event (    event_id      NUMBER PRIMARY KEY,    event_code    VARCHAR2(30) NOT NULL,    status_code   VARCHAR2(20),    priority      NUMBER NOT NULL,    occurred_at   DATE NOT NULL,    labor_amount  NUMBER(10,2) DEFAULT 0 NOT NULL,    parts_amount  NUMBER(10,2) DEFAULT 0 NOT NULL);INSERT INTO servicehub_sql_event    (event_id,event_code,status_code,priority,occurred_at,labor_amount,parts_amount)VALUES    (1,'EVT-001','OPEN',2,DATE '2026-08-25',300,250);INSERT INTO servicehub_sql_event    (event_id,event_code,status_code,priority,occurred_at,labor_amount,parts_amount)VALUES    (2,'EVT-002','',2,DATE '2026-08-25',100,50);INSERT INTO servicehub_sql_event    (event_id,event_code,status_code,priority,occurred_at,labor_amount,parts_amount)VALUES    (3,'EVT-003',NULL,1,DATE '2026-08-24',80,20);COMMIT;SELECT event_id, event_code,       CASE WHEN status_code IS NULL THEN 'NULL' ELSE status_code END AS status_evidenceFROM servicehub_sql_eventORDER BY event_id;SELECT event_id, priority, occurred_atFROM servicehub_sql_eventORDER BY priority DESC, occurred_at DESC, event_id;

DROP TABLE servicehub_sql_event PURGE;

Verification checklist:

• Rows 2 and 3 both show a null status because the empty string was not preserved as a distinct VARCHAR2 value.
• The stored dates are created from typed literals and do not depend on NLS_DATE_FORMAT.
• The final ordering includes event_id as a unique tie-breaker.
• Your NLS evidence comes from the current session, not a remembered database default.

7. Production judgment

Keep typed data typed across the application/database boundary. Use bind variables for dates and numbers, explicit format models at genuine text boundaries, and deterministic sort keys for APIs and pagination. NLS parameters are valuable for presentation and localization; they are poor hidden contracts for machine-to-machine data exchange. Record client/driver session initialization when investigating conversion bugs.

No special edition, option, pack, initialization parameter change, restart, or COMPATIBLE change is required for this lesson. The examples are PDB-local SQL. The main risks are correctness and security: implicit conversion can change results and, in dynamically constructed SQL, NLS-dependent conversion can even contribute to injection vulnerabilities. Lesson 2 moves from single-table semantics to joins, where row preservation and row multiplication create a different class of correctness failures.

8. Summary and next step

Oracle SQL currently treats zero-length character strings as null, three-valued logic requires IS NULL, NLS settings can change text conversion, typed literals and explicit formats remove ambiguity, and ordering is deterministic only when the complete ordering key resolves ties. With those foundations stable, the next lesson builds joins without accidentally losing or multiplying ServiceHub rows.

Authoritative references

• NLS_DATE_FORMAT — session/default date-format behavior and client override context
• Format Models — explicit datetime parsing and formatting
• Data Type Comparison Rules — implicit conversion and NLS-dependent security considerations
• Concatenation Operator — current zero-length character string treatment
• SELECT — query and ORDER BY syntax