That's the nice thing about standards...there's so many to choose from.
One man's NULL is another's length=0, is another's "empty" string of ''.
http://en.wikipedia.org/wiki/Null_%28SQL%29
IMHO sqlite3 gets it right and Oracle 11.2.0.1.0 gets it wrong...
Purportedly:
"Null is defined by the ISO SQL standard as different from both an empty string
or the numerical value 0"
In sqlite3 3.7.13:
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> insert into t values(NULL);
sqlite> insert into t values('');
sqlite> select count(a) from t;
1
sqlite> select count(*) from t where a is null;
1
sqlite> select count(*) from t where a ='';
1
sqlite> select count(*) from t where length(a) = 0;
1
SQL> create table t(a varchar(255));
Table created.
SQL> insert into t values(NULL);
1 row created.
SQL> insert into t values('');
1 row created.
SQL> select count(a) from t;
COUNT(A)
----------
0
SQL> select count(*) from t where a is null;
COUNT(*)
----------
2
SQL> select count(*) from t where a = '';
COUNT(*)
----------
0
SQL> select count(*) from t where length(a) = 0;
COUNT(*)
----------
0
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________________
From: [email protected] [[email protected]] on
behalf of John Clegg [[email protected]]
Sent: Monday, September 17, 2012 10:05 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Count(*) help
Yes, that was the problem thanks. Even though sqlitebrowser declared them
as empty with '' !
On 17 September 2012 15:50, Black, Michael (IS) <[email protected]>wrote:
> Or just fix the existing table:
>
> update members set year2007=NULL where year2007='';
> update members set year2008=NULL where year2008='';
> update members set year2009=NULL where year2009='';
> update members set year2010=NULL where year2010='';
> update members set year2011=NULL where year2011='';
> update members set year2012=NULL where year2012='';
>
> Then the counts should be what you want.
>
> I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the
> SQL standard say anything about what a "NULL" value is? And who's correct
> here if there is a standard?
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
