Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from SQL in a Nutshell

Processing NULLS

Most databases allow any of their supported datatypes to store NULL values. Inexperienced SQL programmers and developers tend to think of NULL as zero or blank. In fact, NULL is neither of these. In the relational database world, NULL literally means that the value is unknown or indeterminate. (This question alone — whether NULL should be considered unknown or indeterminate — is the subject of academic debate.) This differentiation enables a database designer to distinguish between those entries that represent a deliberately placed zero and those where either the data is not recorded in the system or where a NULL has been explicitly entered. For an example of this semantic difference, consider a system that tracks payments. A payment with a NULL amount does not mean that the product is free; instead, a NULL payment indicates that the amount is not known or perhaps not yet determined.

One side effect of the indeterminate nature of a NULL value is it cannot be used in a calculation or a comparison. Here are a few brief, but very important rules to remember about the behavior of NULL values:

  • A NULL value cannot be inserted into a column defined as NOT NULL.

  • NULL values are not equal to each other. It is a frequent mistake to compare two columns that contain NULL and expect the NULL values to match. (A NULL value can be identified in a WHERE clause or in a Boolean expression using phrases such as `value IS NULL’ and `value IS NOT NULL’.)

  • A column containing a NULL value ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443