Main /

Sql Nulls

Wiki_System*

technolowiki.org

[-edit SideBar-]

Sql Nulls

Database/SQL Null Values

What is Null?

  • Null is not the empty string/varchar, but the total absence of a value.
  • Null cannot be queried by an ordinary operator such as "=", but yields meaningful results only with the "IS" operator (IS NULL, IS NOT NULL)
  • Null is always distinct (actually, your database of choice may differ!)

How are Nulls Handled in different DBMS?

AspectNULL IS NOT EMPTYNULL CONCATNULL IS DISTINCTNULL IS FORBIDDENNULL IS ALLOWEDNULL MAKES DISTINCT
DBMSstring in a column valuewith a string/varchar still results in nullin a unique indexas part of a primary keyas part of a composite foreign key and detaches the referencea composite unique index when all other column values are duplicate
Microsoft Access
XS2compliestunablecompliescompliescomplies
XS97~2010compliestunablecompliescompliesDIFFERScomplies
Microsoft SQL Server
SQLSRV 6.5~2005compliestunableDIFFERScompliescompliesBROKEN
Informix
Informix  DIFFERS
Interbase/Firebird
Firebird 1.0~2.0compliescompliescompliescompliescomplies 1)DIFFERS
MySql
MySql 4  complies
Oracle
OraclecompliesDIFFERScomplies  DIFFERS
PostgreSQL
Postgre 8.3compliescompliescompliescompliescompliescomplies
SQLite
SQLite  complies
Recent Changes (All) | Edit SideBar Page last modified on August 10, 2010, at 05:28 PM Edit Page | Page History
Powered by TechnoloWiki