Home > Database, SQL Server > Understanding NULLS

Understanding NULLS

Some SQL programmers and developers tend to think NULL as zero or blank. In fact, NULL is neither of these. NULL literally means that the value is unknown or indeterminate.

One side effect of the indeterminate nature of NULL value is it cannot be used in a calculation or a comparision.

Listed below are a few important rules to remember about the behaviour 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 column 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 is ignored in the calculation of aggregate values such as AVG, SUM or MAX.
  • When columns that contain NULL values in a GROUP BY clause of a query are listed, the query output contains rows for those NULL values.
  • JOINS between tables, in which one join condition contains values and the other contains NULL, are governed by the rules for “outer joins”.
Advertisements
Categories: Database, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: