SQL NULL values & COALESCE.

SQL and columns with NULL values?

  • In today's database products, NULLs are used to represent both missing and inapplicable values.
  • Unknown pieces of information are called distinguished nulls—their values are not precisely known.
  • In relational database management systems, however, distinguished nulls are problematic in terms of both definition and implementation.
  • You cannot determine whether a NULL exactly matches any other value, even another NULL.
  • When you perform an arithmetic operation on an unknown value, the result can only be unknown.
  • However, Aggregate functions will omit the NULL values in the calculations.
  • To overcome the problem of NULL, you can set default values for columns when you CREATE or ALTER a table.
  • A good default for a character type column might be "unknown," while a good default for the date column might be today's date.
  • Another way to handle the NULL values is to a use functions that work with NULL values, which the function COALESCE() do.
    COALESCE Example:
    MySQL, Oracle, SQL server, PostgreSQL:
    select 	TELEPHONE_TYPE, CUSTOMER_ID,
            COALESCE(TELEPHONE_NUMBER,'Only Fax') as "Contact phone no."
       from telephones where TELEPHONE_TYPE='Unknown' ;
    The result should be:
    telephone_type customer_id Contact phone no.
    Unknown 10002 (678) 223-94
    Unknown 10004 (881) 141-54
    Unknown 10004 Only Fax
    Unknown 10005 (881) 141-54
    Unknown 10005 Only Fax
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.