How to Pass NULL Data Type?

Question

What is the datatype for NULL when passing that value for no data into a database?

Answer 1

There is NO data type of NULL. NULL itself means ABSENCE of data. When there is no data, how can it have type?

Answer 2

Null does not have a specific data type in SQL. Any nullable column or variable can contain null. Null is never equal or unequal to anything. You can cast a variable holding null to another variable and get null, for example:

declare @a integer
set @a = null
select convert (float, @a)

----------------------
NULL

(1 row(s) affected)

Answer 3

Usually NULL is its own datatype - the type of 1 is "INTEGER", the type of the type of NULL is "NULL"

Answer 4

Datatype for NULL is as meaningless as datatype for 0: it can be INTEGER, FLOAT or a VARCHAR. You cannot tell it just from the value.

NULL is legitimate value in almost every datatype domain, which means the absence of actual value.

It's also meaningless to discuss datatypes out of context of certain RDBMS.

In SQLite, for instance, datatypes are value-bound, not column-bound, and NULL is a first-class datatype per se.

In Oracle, the datatypes are more strictly defined. For instance, this query works:

SELECT  COALESCE(dt, i)
FROM    (
        SELECT  CAST(NULL AS DATE) AS dt, CAST(NULL AS DATE) i
        FROM    dual
        ) q

and this does not:

SELECT  COALESCE(dt, i)
FROM    (
        SELECT  CAST(NULL AS DATE) AS dt, CAST(NULL AS NUMBER) i
        FROM    dual
        ) q

, because the latter query returns two columns of different datatypes, both of them having values of NULL, and COALESCE requires both arguments to have same datatype.

It's better to say that a NULL of any datatype can be implicitly converted to a NULL on another datatype.

For instance, a VARCHAR can be implicitly converted to a INTEGER if it has value of 0, but cannot if it has value of 'some_string'.

For NULL's, any datatype can be implicitly converted to any other datatype, if the implicit conversion between them is allowed at all.

Answer 5

In SQL a NULL is a "mark" (something other than a value) that can apply to any SQL type. So it is orthogonal to type.

Answer 6

NULL is the value for 'undefined'. So any type in a database can be 'undefined', as it's a property of the column: a value of a row for the specific column can be 'undefined' which means it's 'NULL', no matter what the type is. As long as the column is nullable.

Answer 7

I think DBNULL or NULL is a special type.

Answer 8

I think the question defeats itself. If NULL had a datatype, wouldn't you be forced to change it with every instantiation outside of its default. For example, when you create it as a character, but then force it into an object's value?

NULL==NULL

That is all.

Answer 9

Actually, in PowerShell comparing $null -eq $null gives False. Also, -not $null will give you True, so here it seems to be reprezented as False. I know, PowerShell might not be a good example, but still :)

Answer 10

Usually SQL NULL does not have a type associated with it. However there are exceptions. Database engines postgresql and derby (javadb) require that null has a type. In other words they do not support untyped null. So query conditions like NULL IS NULL may fail. Here, NULL must be given a type, the expected type of the target that processes the NULL value. In this case this appears silly because there is no target and this can be counterproductive.

See CAST function: -- you must cast NULL as a data type to use it

See Queries with guarded null Parameter fail and Add support for setObject(arg, null)

Please vote for these issues so that the odd database engines change their ways.

Answer 11

Prabhahar, each type of database driver has its own way of handling NULL. You will have to examine the driver API for the specific database.

For example if you are using the Java Derby database, simply pass in the Java native type, null as shown in Ian Bjorhovde's answer to "Derby's Handling of NULL Values":

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
VALUES ( 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
);

Here is another null example of JDBC:Inserting null to Integer column:

pst.setNull(4, java.sql.Types.INTEGER);

Answer 12

http://en.wikipedia.org/wiki/Null_(SQL)

Answer 13

NULL can be cast (converted) to any data type yet data type comparisons with NULL always return FALSE.