Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Various sources (eg Wikipedia, PSOUG) state that Oracle's null does not have a type. Is this true?

What about other RDBMSs?

share|improve this question
add comment

4 Answers

Oracle:

The null literal does not have a type, but

  1. null can be cast to any type, and this may be necessary when

    • calling overloaded procedures or functions
    • controlling the return type of the decode function, eg:

      select decode('A','B',to_char(null),'A','1') from dual;
      DECODE('A','B',TO_CHAR(NULL),'A','1')
      -------------------------------------
      1
      
      select decode('A','B',to_number(null),'A','1') from dual;
      DECODE('A','B',TO_NUMBER(NULL),'A','1')
      --------------------------------------- 
                                            1
      
    • controlling column types of set operators like union when the first query block includes a null
  2. null values stored in the database always have a type:

    create table t(n integer, s varchar(10));
    insert into t values(null, null);
    
    select decode('A','B',n,'A','1') from t; 
    DECODE('A','B',N,'A','1')
    -------------------------
                            1
    
    select decode('A','B',s,'A','1') from t;
    DECODE('A','B',S,'A','1')
    -------------------------
    1
    
share|improve this answer
2  
+1 For curiosity let's try Select NULL what_type_is_this from DUAL; Of course this no practical useful example and I didn't try it yet, I have learned to use casts in such cases. –  bernd_k Aug 6 '11 at 11:16
add comment

SQL Server, int

SELECT NULL AS foo INTO dbo.bar
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'bar'
DROP TABLE dbo.bar

MySQL, binary(0)

CREATE TABLE mydb.foo (select NULL AS bar);
EXPLAIN mydb.foo;
DROP TABLE mydb.foo;
share|improve this answer
    
+1 that's very interesting - I assumed that would throw an error like it does on Oracle –  Jack Douglas Aug 6 '11 at 12:46
4  
Interesting find. However, this shows that those DB engines default to a datatype when creating tables in that way, not necessarily that NULL has a type in those engines. For example, this error suggests that SQL Server does indeed treat NULL as untyped. –  Nick Chammas Aug 6 '11 at 15:16
2  
@Nick select isnumeric(null) = 0... interesting –  Factor Mystic Aug 6 '11 at 21:53
add comment

Oracle it is in some sense some string type.

Thats what ADO Reader tells me. here is a Powershell Script:

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") 
$ConnectionString = "Data Source=myTNS;User ID=myUSER;Password=myPassword" 
$conn=new-object System.Data.OracleClient.OracleConnection 
$conn.ConnectionString=$ConnectionString 
$conn.Open() 
$sql = "Select NULL xx from DUAL"
$cmd=new-object System.Data.OracleClient.OracleCommand($sql,$conn)

$r = $cmd.ExecuteReader()

$r.GetSchemaTable() | % { $_
}        

That gives

ColumnName               : XX
ColumnOrdinal            : 0
ColumnSize               : 0
NumericPrecision         : 0
NumericScale             : 0
DataType                 : System.String
ProviderType             : 22
IsLong                   : False
AllowDBNull              : True
IsAliased                : 
IsExpression             : 
IsKey                    : 
IsUnique                 : 
BaseSchemaName           : 
BaseTableName            : 
BaseColumnName           : 
ProviderSpecificDataType : System.Data.OracleClient.OracleString

Note the line

ProviderSpecificDataType : System.Data.OracleClient.OracleString

share|improve this answer
add comment

postgres:

create table foo as select null as bar;
WARNING:  column "bar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.

postgres=> \d foo

 Column |  Type   | Modifiers
--------+---------+-----------
 bar    | unknown |
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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