locked
NULL or UNKNOWN RRS feed

  • Question

  • I'm creating large tables to be queried by a variety of reporting users and reporting applications/tools. what is the best practice regards using NULL Vs a value like UNKNOWN.

     

    What will cause the least amount of friction?

    Tuesday, June 15, 2010 2:23 PM

Answers

  • Best practice is probably to use NULLS because NULLS will provide proper use of 3-state logic whereas plugging a value like 'UNKNOWN' will not.

    One of the problems of plugging UNKNOWN into a column rather than null is that an expression such as:

          WHERE someColumn not in (select aColumnThatContainsUNKNOWN from the table)

    will not behave as expected.

    Oddly enough, there is some chance that it might behave the way you want it to behave.

    (Now figure THAT one out!)

     

    • Marked as answer by Kent Waldrop Tuesday, July 20, 2010 5:27 PM
    Tuesday, June 15, 2010 6:59 PM

All replies

  • >What will cause the least amount of friction?

    A consistent policy.

    Tuesday, June 15, 2010 3:12 PM
    Answerer
  • Best practice is probably to use NULLS because NULLS will provide proper use of 3-state logic whereas plugging a value like 'UNKNOWN' will not.

    One of the problems of plugging UNKNOWN into a column rather than null is that an expression such as:

          WHERE someColumn not in (select aColumnThatContainsUNKNOWN from the table)

    will not behave as expected.

    Oddly enough, there is some chance that it might behave the way you want it to behave.

    (Now figure THAT one out!)

     

    • Marked as answer by Kent Waldrop Tuesday, July 20, 2010 5:27 PM
    Tuesday, June 15, 2010 6:59 PM
  • Best practice is probably to use NULLS because NULLS will provide proper use of 3-state logic whereas plugging a value like 'UNKNOWN' will not.

    One of the problems of plugging UNKNOWN into a column rather than null is that an expression such as:

          WHERE someColumn not in (select aColumnThatContainsUNKNOWN from the table)

    will not behave as expected.

    Oddly enough, there is some chance that it might behave the way you want it to behave.

    (Now figure THAT one out!)

     


    ( Talk about laughable! )
    Tuesday, June 15, 2010 7:01 PM
  • Hi

    Use NULL , however if you want to display 'UNKNOWN' use COALESCE or CASE expression

    SELECT COALESCE(col,'UNKNOWN) AS col FROM tbl

    '

    Wednesday, June 16, 2010 5:30 AM
    • Marked as answer by Kent Waldrop Tuesday, July 20, 2010 5:28 PM
    • Unmarked as answer by Kent Waldrop Tuesday, July 20, 2010 5:29 PM
    Friday, June 18, 2010 10:24 AM