4

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?

2 Answers 2

13

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
0
7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.