NULLs and empty strings: Oracle versus SQL Server

Oracle treats a character value of length zero as NULL. Insert '' into a VARCHAR2 column and the row holds NULL: WHERE nick = '' never matches it, WHERE nick IS NULL finds it, and COUNT(nick) skips it. SQL Server keeps an empty string as an ordinary value of length 0 that is not NULL, so '' = '' is true there and COUNT(nick) counts the row. Inside one engine both rules are consistent. The trouble starts when data or code crosses between them: a NOT NULL column full of empty strings in SQL Server cannot be loaded into Oracle, counts and filters change their answers, and text built by concatenation comes out differently. Oracle’s own reference calls the rule current behaviour that may change in a future release, and advises against code that treats empty strings and NULLs as the same thing.

One INSERT, two different rows

Everything else in this lesson follows from that one difference. SQL Server stores what it was given, and its NULLs and empty strings stay apart. Oracle has only NULLs for a missing text value: an empty string coming in from a form, a CSV file or another database becomes NULL on the way in. A single space is not empty, so ' ' stays a real value in both engines.

The same table under both rules

SQLite, the engine that runs this track’s examples in your browser, keeps empty strings the way SQL Server does. NULLIF(nick, '') returns NULL where nick is '', which is exactly what Oracle would have stored, so one table can be read both ways side by side:

-- Five customers of a shop. Ravi and Zoya left the nickname box empty; Meera's was never asked for.
-- SQLite keeps '' as a value, as SQL Server does. NULLIF(nick, '') turns each '' into NULL, which is
-- what Oracle stores for a zero-length string: a model of Oracle's documented rule, run in SQLite.
CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  nick TEXT
);
INSERT INTO customers (id, name, nick) VALUES
  (1, 'Asha',  'Ash'),
  (2, 'Ravi',  ''),
  (3, 'Meera', NULL),
  (4, 'Kabir', 'Kabs'),
  (5, 'Zoya',  '');

-- The same four questions, asked of the data as SQL Server keeps it and as Oracle would keep it.
WITH kept AS (
  SELECT id, nick AS sql_server, NULLIF(nick, '') AS oracle FROM customers
)
SELECT 'WHERE nick = ''''' AS question,
       (SELECT count(*) FROM kept WHERE sql_server = '') AS sql_server,
       (SELECT count(*) FROM kept WHERE oracle = '')     AS oracle
UNION ALL
SELECT 'WHERE nick IS NULL',
       (SELECT count(*) FROM kept WHERE sql_server IS NULL),
       (SELECT count(*) FROM kept WHERE oracle IS NULL)
UNION ALL
SELECT 'COUNT(nick)', count(sql_server), count(oracle) FROM kept
UNION ALL
SELECT 'COUNT(DISTINCT nick)', count(DISTINCT sql_server), count(DISTINCT oracle) FROM kept;

-- Row by row: what is stored, its length, and a label joined from two columns.
-- In SQLite, || with a NULL gives NULL, like T-SQL's + with its default settings.
-- Oracle's || skips a NULL operand, modelled here with coalesce(..., '').
SELECT id,
       quote(nick)                                      AS stored,
       length(nick)                                     AS len,
       quote(NULLIF(nick, ''))                          AS oracle_stores,
       length(NULLIF(nick, ''))                         AS oracle_len,
       name || ' / ' || nick                            AS label_plus,
       name || ' / ' || coalesce(NULLIF(nick, ''), '')  AS label_oracle
FROM customers
ORDER BY id;

┌──────────────────────┬────────────┬────────┐
│       question       │ sql_server │ oracle │
├──────────────────────┼────────────┼────────┤
│ WHERE nick = ''      │ 2          │ 0      │
│ WHERE nick IS NULL   │ 1          │ 3      │
│ COUNT(nick)          │ 4          │ 2      │
│ COUNT(DISTINCT nick) │ 3          │ 2      │
└──────────────────────┴────────────┴────────┘
┌────┬────────┬─────┬───────────────┬────────────┬──────────────┬──────────────┐
│ id │ stored │ len │ oracle_stores │ oracle_len │  label_plus  │ label_oracle │
├────┼────────┼─────┼───────────────┼────────────┼──────────────┼──────────────┤
│ 1  │ 'Ash'  │ 3   │ 'Ash'         │ 3          │ Asha / Ash   │ Asha / Ash   │
│ 2  │ ''     │ 0   │ NULL          │            │ Ravi /       │ Ravi /       │
│ 3  │ NULL   │     │ NULL          │            │              │ Meera /      │
│ 4  │ 'Kabs' │ 4   │ 'Kabs'        │ 4          │ Kabir / Kabs │ Kabir / Kabs │
│ 5  │ ''     │ 0   │ NULL          │            │ Zoya /       │ Zoya /       │
└────┴────────┴─────┴───────────────┴────────────┴──────────────┴──────────────┘

Three of the four answers change. Two rows match = '' under SQL Server’s rule and none under Oracle’s, because nothing in Oracle equals an empty string: comparing anything with NULL is unknown. IS NULL finds one row in SQL Server and three in Oracle. COUNT(nick) falls from 4 to 2, and COUNT(DISTINCT nick) from 3 to 2. Oracle’s LENGTH('') is NULL for the same reason, while SQL Server’s LEN('') is 0.

The labels in the second result show the other half of the story. In SQLite, || with a NULL operand gives NULL, and so does T-SQL’s + with its standard setting CONCAT_NULL_YIELDS_NULL ON, which SQL Server 2017 and later always apply. SQL Server 2025’s new || operator returns NULL as well. Oracle’s || skips a NULL operand instead, so Meera’s label survives there and vanishes in SQL Server. T-SQL’s CONCAT function treats NULL arguments as empty strings, the closest match to Oracle’s behaviour.

Sorting, CHAR columns and the rules both engines share

Sort order differs too. SQL Server treats NULL as the lowest value, so NULLs come first in ascending order. Oracle puts NULLs last in ascending order and first in descending order, and its former empty strings sort with them. Write NULLS FIRST or NULLS LAST in Oracle when the position matters; SQL Server has no such clause, so sort on CASE WHEN nick IS NULL THEN 1 ELSE 0 END first.

Fixed-length columns follow the same rule. Oracle blank-pads a CHAR value to its declared length, so ' ' in a CHAR(3) column becomes three spaces, but '' becomes NULL before any padding happens.

Two rules are the same in both engines. NOT IN against a list that contains a NULL returns no rows, and = NULL matches nothing (SQL Server’s old ANSI_NULLS OFF mode, which changed that, is deprecated).

The functions that handle NULLs

Each engine has its own functions for replacing a NULL or comparing two values that may be NULL:

ISNULL returns its second argument when the first is NULL. NVL does the same in Oracle. NVL2 returns its second argument when the first is not NULL and its third when it is. COALESCE returns the first argument that is not NULL, in both engines. NULLIF returns NULL when its two arguments are equal. IS NOT DISTINCT FROM compares two values and treats two NULLs as equal. DECODE compares a value with a list and also treats two NULLs as equal.

What moving data between them changes

• UNIQUE keys follow a different NULL rule in each engine, so a key that is valid in SQL Server can collide in Oracle.
• Oracle’s B-tree indexes leave out rows whose key columns are all NULL.
• ISNULL and COALESCE return different types, and COALESCE may evaluate an argument twice in SQL Server.
• Before-and-after checks need a comparison that treats two NULLs as equal.
• A dry run on a copy of the data puts numbers on every change before the real move.

Your turn

-- Return the id and name of every vendor that Oracle would refuse to load,
-- because a NOT NULL column (name or code) holds an empty string.
SELECT id, name
FROM vendors
WHERE code IS NULL;

# Sample tests: each one builds a small vendors table as it sits in SQL Server, runs your query on it and compares the
# rows it returns (in any order).
tests:
  - name: finds a vendor whose code is an empty string
    setup: |
      CREATE TABLE vendors (id INTEGER PRIMARY KEY, name TEXT NOT NULL, code TEXT NOT NULL, city TEXT);
      INSERT INTO vendors VALUES
        (1, 'Ganesh Traders',  'GT01', 'Pune'),
        (2, 'Noor Textiles',   '',     'Surat'),
        (3, 'Kaveri Spices',   'KS07', NULL);
    expected:
      columns: [id, name]
      rows:
        - [2, Noor Textiles]
  - name: finds an empty name too, but not an empty city
    setup: |
      CREATE TABLE vendors (id INTEGER PRIMARY KEY, name TEXT NOT NULL, code TEXT NOT NULL, city TEXT);
      INSERT INTO vendors VALUES
        (4, '',              'AN12', 'Kochi'),
        (5, 'Lotus Paper',   'LP03', ''),
        (6, 'Indus Steel',   'IS55', 'Nagpur');
    expected:
      columns: [id, name]
      rows:
        - [4, '']
  - name: returns a row once even when both columns are empty
    setup: |
      CREATE TABLE vendors (id INTEGER PRIMARY KEY, name TEXT NOT NULL, code TEXT NOT NULL, city TEXT);
      INSERT INTO vendors VALUES
        (7, '',              '',     'Delhi'),
        (8, 'Sahyadri Agro', '',     NULL),
        (9, 'Mehta & Sons',  'MS09', 'Rajkot');
    expected:
      columns: [id, name]
      rows:
        - [7, '']
        - [8, Sahyadri Agro]
  - name: returns nothing when every NOT NULL column has a value
    setup: |
      CREATE TABLE vendors (id INTEGER PRIMARY KEY, name TEXT NOT NULL, code TEXT NOT NULL, city TEXT);
      INSERT INTO vendors VALUES
        (10, 'Pragati Tools', 'PT10', NULL),
        (11, 'Vasant Foods',  'VF11', '');
    expected:
      columns: [id, name]
      rows: []

UNIQUE keys and indexes when empty strings become NULL, with the failures reproduced Pro: opens with a Pro, Premium or Ultimate pass

NULL functions in depth and a migration plan, tested on a dry run Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Oracle stores an empty string as NULL; SQL Server keeps it as a value of length 0.
• In Oracle, test for a missing text value with IS NULL: = '' never matches.
• COUNT(column) and COUNT(DISTINCT column) drop when empty strings become NULLs.
• Oracle’s || skips NULLs; T-SQL’s + and || return NULL, and CONCAT treats NULL as empty.
• NULLIF(column, '') previews in SQL Server what Oracle would store.