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

score 7 · Answer 1 · answered Jun 07 '12 at 17:54

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.

score 6 · Accepted Answer · edited May 23 '17 at 10:29

6

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

edited May 23 '17 at 10:29

Community

1
1

answered Jun 07 '12 at 17:47

gpojd

22,558
8
42
71

Nope, that results in `ORA-00908: missing NULL keyword` – Mike Christensen Jun 07 '12 at 17:50
Thanks. The rest of my comment is still true. – gpojd Jun 07 '12 at 17:51
Thanks, `+1` from me. Justin Cave's answer in your linked question explains the history behind this perfectly. – Mike Christensen Jun 07 '12 at 17:56

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

2 Answers2