Oracle Database Behavior

In Oracle databases, the distinction between an empty string ('') and a NULL value is effectively nonexistent; the database engine interprets them identically. This behavior differs significantly from other relational database systems. To demonstrate this, we can create a test table and observe how Oracle processes these inputs.

CREATE TABLE demo_table (
    id_num NUMBER,
    txt_val VARCHAR2(20)
);

-- Insert a standard string, an empty string, and an explicit NULL
INSERT INTO demo_table VALUES (10, 'Oracle');
INSERT INTO demo_table VALUES (20, '');
INSERT INTO demo_table VALUES (30, NULL);

-- Retrieve all records to check storage
SELECT * FROM demo_table;

The output displays the following:

    ID_NUM TXT_VAL
---------- --------------------
        10 Oracle
        20
        30

Although the second row was inserted with an empty string and the third with NULL, they are stored and retrieved identically. Consequently, standard equality checks against an empty string will fail:

-- Returns no rows
SELECT * FROM demo_table WHERE txt_val = '';

Attempting to evaluate NULL or empty strings using standard comparison operators such as =, <>, or > will not yield results. The only valid method to identify these values is using the IS NULL predicate:

-- Returns both ID 20 and ID 30
SELECT * FROM demo_table WHERE txt_val IS NULL;

Furthermore, using the NVL function reveals that Oracle treats the empty string as a NULL value, triggering the replacement argument:

SELECT id_num, 
       NVL(txt_val, 'Value is missing') AS checked_val
FROM   demo_table;

The result confirms that both the empty string and NULL are handled the same way:

    ID_NUM CHECKED_VAL
---------- --------------------
        10 Oracle
        20 Value is missing
        30 Value is missing

MySQL Database Behavior

In contrast, MySQL maintains a strict distinction between an empty string and a NULL value. An empty string is treated as a valid character string of zero length, while NULL indicates the absence of any data. We can replicate the previous test scenario in a MySQL environment to observe the diffference.

CREATE TABLE sample_data (
    pk_id INT,
    data_col VARCHAR(50)
);

INSERT INTO sample_data VALUES (1, 'MySQL'), (2, ''), (3, NULL);

SELECT * FROM sample_data;

The query results clearly show the separation between the two states:

+-------+----------+
| pk_id | data_col |
+-------+----------+
|     1 | MySQL    |
|     2 |          |
|     3 | NULL     |
+-------+----------+

Here, queries behave predictably based on the sepcific predicate used:

-- Selects only the row with the empty string (ID 2)
SELECT * FROM sample_data WHERE data_col = '';

-- Selects only the row with the explicit NULL (ID 3)
SELECT * FROM sample_data WHERE data_col IS NULL;

Logic operators also treat them differently. The empty string behaves like a value, whereas NULL requires specific exclusion logic:

-- Selects ID 1 and ID 3 (Excludes ID 2 because it matches the empty string)
SELECT * FROM sample_data WHERE data_col <> '';

-- Selects ID 1 and ID 2 (Excludes ID 3 because it is NULL)
SELECT * FROM sample_data WHERE data_col IS NOT NULL;

Thus, in MySQL, developers must handle logic for empty strings and NULL values separately to insure accurate data manipulation.