min(column) with nulls?

Question

I am new to databases, but I hope you can help me to understand something.

Take part of this query:

select min(date) as date...

Assume that some of the dates are null. What effect would it have? Would the query break?

Answer 1

MIN will return the smallest non-NULL value. The query won't throw an error, it will simply ignore the NULL values

SQL> with x as (
  2    select null dt from dual union all
  3    select sysdate from dual
  4  )
  5  select min(dt)
  6    from x;

MIN(DT)
---------
21-OCT-11

Answer 2

Assume that some of the dates are null

For completeness, I'm also assuming there may be zero rows

I'm using this test data:

create table t as
select to_date('20110101', 'YYYYMMDD')+level-1 as d from dual connect by level<=5
union all select null from dual;

select * from t;
D                         
------------------------- 
01-JAN-11 00.00.00        
02-JAN-11 00.00.00        
03-JAN-11 00.00.00        
04-JAN-11 00.00.00        
05-JAN-11 00.00.00        
(null)

and considering the following cases:

1. some rows but no null values:

   select min(d) from t where d is not null;
   MIN(D)                    
   ------------------------- 
   01-JAN-11 00.00.00      
   

2. some rows and both null and non-null values:

   select min(d) from t;
   MIN(D)                    
   ------------------------- 
   01-JAN-11 00.00.00      
   

3. some rows and all values are null:

   select min(d) from t where d is null;
   MIN(D)                    
   ------------------------- 
   (null)
   

4. no rows:

   select min(d) from t where 1=2;
   MIN(D)                    
   ------------------------- 
   (null)
   

5. an interesting edge case with group by that returns no rows:

   select min(d) from t where 1=2 group by 1;
   MIN(D)                    
   ------------------------- 
   

---

_{aside: I've haven't used date as an alias because Oracle complains (not unreasonably IMO):}

select to_date('20110101', 'YYYYMMDD') as date from dual;
ORA-00923: FROM keyword not found where expected