Geek asks and answers

Why should I avoid NULL values ​​in an SQL database?

I read the 45-tips-database-performance-tips-for-developers document from a well-known commercial provider for SQL tools today, and one piece of advice confused me:

If possible, avoid NULL values ​​in your database. If not, use the appropriate IS NULL and IS NOT NULL code.

I like to have NULL values ​​because for me it is a difference if the value has never been set or it 0or string empty. So databases are relevant to porpuse.

So this question is pointless or do I need to take measures to not have NULL values ​​at all in my database tables? Does it affect performance, does it matter NULLinstead of numberor string?

+3
source share
5 answers

The NULL question is not simple ... Every professional has a personal opinion about this.

Relational Theory Two-valued logic (2VL: TRUE and FALSE) rejects NULL, and Chris Date is one of the most enemies of NULL. But Ted Codd instead adopted a three-digit logic (TRUE, FALSE, and UNKNOWN).

There are a few things to note for Oracle:

  • Single column B * Tree indices do not contain NULL records. Therefore, the Optimizer cannot use the index if you code "WHERE XXX IS NULL".

  • Oracle NULL , , :

    WHERE SOME_FIELD = NULL

    :

    WHERE SOME_FIELD = ''

, NULL , NULL NULL. , , NULL . WHERE, :

WHERE SOME_FIELD NOT IN (SELECT C FROM SOME_TABLE)

NULL, !

, . NULL ...

+3

, , NULL .

, Codd

- , .

,

- , , , .

. () , . - .
, , , . ?
, .

+5

NULL, , . :

  • .
  • .
  • .

"N/A" "N/K" , , NULL, - , (, date_of_birth , "reason_for_no_date_of_birth", .

, , , - date_of_death - , date_of_account_termination.

, , "ACCOUNT_DATES" DATE_TYPES "Open", "Close" ..

+2

. , "" (.. ). , SQL SQL- , . , , .

Since there is nothing like zeros in the real world, using them means making some compromises on how your database represents reality. In fact, there is no single, consistent “meaning” of zeros and little general agreement on what they are intended for. In practice, nulls are used to represent all kinds of different situations. If you use them, then it's nice to document what zero means for any given attribute.

Here's a great lecture on Chris's “zero issue”. The date:

http://www.youtube.com/watch?v=kU-MXf2TsPE

+2
source

More articles:


All Articles