On: Null Values in SQL Queries

[ak39]

AFAIK, this Oracle "feature" is only true for columns marked as "not nullable". So if you attempt to insert an empty sting ("") into a not-nullable column, it will fail.

All other relational databases differentiate between empty strings and NULL.

[Sean1708 → ak39]

Nah, it's true for all columns:

  SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 3 15:56:30 2020
  
  Copyright (c) 1982, 2013, Oracle.  All rights reserved.
  
  
  Connected to:
  Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  
  SQL> create table foo(fook varchar2(10) not null);
  
  Table created.
  
  SQL> insert into foo values ('');
  insert into foo values ('')
                          *
  ERROR at line 1:
  ORA-01400: cannot insert NULL into ("SPORTSBOOK_DOCK"."FOO"."FOOK")
  
  
  SQL> create table bar(bark varchar2(10));
  
  Table created.
  
  SQL> insert into bar values ('');
  
  1 row created.
  
  SQL> insert into bar values ('a');
  
  1 row created.
  
  SQL> insert into bar values (null);
  
  1 row created.
  
  SQL> select * from bar;
  
  BARK
  ----------
  
  a
  
  
  SQL> select * from bar where bark = '';
  
  no rows selected

[shawnz → ak39]

It is also true for nullable columns.

[thenewnewguy → ak39]

> if you attempt to insert an empty sting ("") into a not-nullable column, it will fail

Yes, because it will convert the empty string into a NULL, and fail to insert that NULL into a non-NULLable column.