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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of John Clegg [john.cl...@nailsea.net] 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) <michael.bla...@ngc.com>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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users