Print Shortlink

Empty strings treated as null value in Oracle

Why o why, dear Oracle, do you treat empty strings as a null value in SQL-queries? And, dear Oracle, if you do so, why did you implement it sloppy?

SELECT * FROM table_x WHERE varchar2_column_y <> '';

This query takes a few minutes to grind through ~70 million rows and then returns 0 results. This takes way longer then other queries and the result count is wrong.

Initially I started to doubt my own ability to write valid SQL. After an hour and many tests I decided to see if maybe there is something in Oracle that can create this kind of behavior and I found this page on the world wide web: Oracle/PLSQL: Difference between an empty string and a null value. It still doesn’t help me to understand why the query would return 0 results after running for a few minutes but at least it made me understand how to formulate the correct query:

SELECT * FROM table_x WHERE varchar2_column_y is not null;

Sigh.

Leave a Reply