In Oracle, why is `` = '' false?

Question

This question comes from my previous post.

I am curious why:

select * from TPM_USER where '' = '' 

Returns null strings, however:

 select * from TPM_USER where 1 = 1 

Returns each row in the table. Is this SQL standard or is it specific to Oracle?

Oracle SQL Fiddle .

The following works as expected:

PostgreSQL SQL Fiddle

SQL Server SQL Fiddle

mySQL SQL Fiddle

Answer 1

In Oracle, an empty string is NULL . You do not use = for NULL values .

Answer 2

Oracle does not distinguish between empty string and NULL .

This is why the recommended row data type is VARCHAR2 rather than VARCHAR : the latter should make this distinction, but currently does not.

In the trivalent logic used by SQL , NULL = NULL (which is a synonym for '' = '' in Oracle) is evaluated as NULL (not FALSE , but NULL ) and therefore WHERE filtered.