Sqlite: Why does "select min (time) from asdftable" return an empty string when the DB is empty?

Question

This is weird. I have an empty SQLite DB.

When I execute select min(time) from asdftable , I return an empty column. When I execute select time from asdftable , I get nothing (which is correct).

Any ideas why this is?

Answer 1

Aggregated functions return something, even if nothing is found, simply because they implicitly group all (any) rows in your table to get you the cumulative value. Take count , for example:

 sqlite> create table foo (a int not null); sqlite> select count(a) from foo; 0 

null matches min , since 0 matches count , where no rows are returned.

This may be a useful property. Consider the following example:

 sqlite> select ifnull(min(a), "I'm null") from foo; I'm null 

If you want to filter out this case so that no records are returned, you can use the having clause (and do an explicit grouping):

 sqlite> select min(a) as min_a from foo group by a having min_a not null;