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

Question

This question comes from my previous post.

I'm curious as to why:

select * from TPM_USER where '' = ''

Returns zero rows, however:

select * from TPM_USER where 1 = 1

Returns every row in the table. Is this per SQL standard, or is this Oracle specific?

Oracle SQL Fiddle.

The following work as expected:

PostgreSQL SQL Fiddle

SQL Server SQL Fiddle

mySQL SQL Fiddle

Answer 1

Oracle does not distinguish between empty string and NULL.

That's why the recommended string datatype is VARCHAR2 and not VARCHAR: the latter is supposed to do this distinction but currently does not.

In trivalent logic which SQL uses, NULL = NULL (which in Oracle is synonymous to '' = '') evaluates to NULL (not FALSE but NULL) and hence is filtered out by the WHERE clause.

Answer 2

In Oracle, an empty string equates to NULL. You don't use = for NULL values.