Absorbent of The Mind

Four Rules for NULLs

2010-07-07T13:56:00.000+04:00

/* reprint from http://www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/ */

4 Simple Rules for Handling SQL NULLs

Introduction

"As we know,
There are known knowns.
There are things we know we know.
We also know
There are known unknowns.
That is to say
We know there are some things
We do not know.”- Donald Rumsfeld

In a recent article by James Travis, the handling of NULLs in SQL Server was explored. I discovered some inaccuracies in the article; some caused by inaccuracies in SQL Server Books Online, others rooted in trying to apply other standard 3GL and 4GL programming language two-valued logic to the three-valued logic employed by ANSI SQL.

This article was written to explain ANSI SQL three-valued logic and correct the inaccuracies presented

What is NULL?

First, let's start by defining what a NULL is in SQL-speak. ANSI-SQL defines NULL as "a special value, or mark, that is used to indicate the absence of any data value." (ANSI-92) NULL in SQL is not a "data value", but rather an indicator that we have missing data. As an example, we’ll look at the following table which contains last name, first name and middle name.

Last_Name	First_Name	Middle_Name
'Smith'	'Linda'	'Anita'
'Washington'	'Larry'	''
'Jones'	'John'	NULL

In this example, we know that Linda Smith has a middle name, and we know that it is 'Anita'. We have populated Larry Washington's Middle_Name column with a Zero-Length String ('') indicating that we know for a fact Larry has no middle name. These are the "known knowns" in Rumsfeld-speak.

In the case of John Jones, however, we have set the Middle_Name column to NULL. This indicates that we do not know whether John has a middle name or not. We may discover, in the future, that John has a middle name; likewise, we may discover he has no middle name. In either case, we will update his row accordingly. So, NULLs represent our "known unknowns"; the only thing we know for a fact is that we do *not* know whether John has a middle name or not.

Because of the inherent issues surrounding NULLs and data integrity, three-valued logic, scalar arithmetic and performance, DBA's and Database Designers tend to try to minimize the use of NULLABLE columns in their tables.

Rule #1: Use NULLs to indicate unknown/missing information only. Do not use NULLs in place of zeroes, zero-length strings or other "known" blank values. Update your NULLs with proper information as soon as possible.

Three-Valued Logic

Always in the Top 10 List of the most confusing aspects of ANSI SQL, for those coming from other 3GL and 4GL languages is three-valued logic. Anyone coming from a background in most other computer languages will instantly recognize the two-valued logic table:

Two-Valued Logic Table
p	q	p AND q	p OR q
True	True	True	True
True	False	False	True
False	True	False	True
False	False	False	False

And of course the logic for NOT is fairly simple:

Two-Valued NOT Logic Table
p	NOT p
True	False
False	True

So what is Three-Valued Logic? In SQL three-valued logic, we introduce the concept of Unknown into our logic, which is closely associated with NULL. Our SQL Logic Table looks like this:

Three-Valued Logic Table
p	q	p AND q	p OR q
True	True	True	True
True	False	False	True
True	Unknown	Unknown	True
False	True	False	True
False	False	False	False
False	Unknown	False	Unknown
Unknown	True	Unknown	True
Unknown	False	False	Unknown
Unknown	Unknown	Unknown	Unknown

And in SQL logic, the NOT logic table looks like this:

Three-Valued NOT Logic Table
p	NOT p
True	False
False	True
Unknown	Unknown

So how do we arrive at Unknown in these logic tables? Simply put, we try to compare NULLs to data values, or other NULLs. Per the ANSI-92 SQL Standard, comparisons with NULL always result in Unknown.

Try the following SQL Query in Query Analyzer (requires the Northwind Sample Database):

SET ANSI_NULLS ON
SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] IS NULL

This query returns four employee records. Now try this query:

SET ANSI_NULLS ON
SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] = NULL

This query returns no records. The reason for this is that '[Region] = NULL' evaluates to Unknown every time. Since it never evaluates to True for any rows, it returns no records.

NOTE: It has been widely misstated (even in SQL Server Books Online) that "the result of a comparison is false if one of the operands is NULL." In fact, the ANSI-92 SQL Standard states that if one of the operands is NULL, the result of the comparison is "UNKNOWN". (ANSI-92 SQL Standard) SQL Books Online has recently been updated to reflect the accurate ANSI Standard concerning NULL comparisons (See the update here:Microsoft ANSI_NULLS).

NULL Versus NULL

NULLs are not equal to other NULLs. In his article, Understanding the Difference Between "IS NULL" and "=NULL", James Travis states that a comparison to a variable initialized to NULL returns False. And it would appear so by his example:

DECLARE @val CHAR(4)
SET @val = NULL
SET ANSI_NULLS ON
If @val = NULL
     PRINT 'TRUE'
ELSE
     PRINT 'FALSE'

Mr. Travis' example prints 'FALSE', which indicates that the result of the comparison '@val = NULL' is False. This is not the case, however. The result of '@val = NULL' is Unknown. The IF statement will print 'TRUE' if, and only if, the result of the comparison is True; otherwise it falls through to the ELSE statement and prints 'FALSE'. For three-valued logic, we need three comparisons to figure out what the actual result of the '@val = NULL' comparison is:

SET ANSI_NULLS ON
DECLARE @val CHAR(4)
SET @val = NULL
SET ANSI_NULLS ON
If @val = NULL
     PRINT 'TRUE'
ELSE IF NOT(@val = NULL)
     PRINT 'FALSE'
ELSE
     PRINT 'UNKNOWN'

Now we see that the result of the comparison is neither True nor False. It is the third value in our three-valued logic, Unknown. Try this statement to demonstrate that NULL is not equal to other NULLs:

SET ANSI_NULLS ON
IF NULL = NULL
     PRINT 'TRUE'
ELSE IF NOT (NULL = NULL)
     PRINT 'FALSE'
ELSE
     PRINT 'UNKNOWN'

This statement prints 'UNKNOWN' because 'NULL = NULL' evaluates to Unknown. Since 'NULL = NULL' evaluates to Unknown, if we look back at the NOT Logic table for three-valued logic, we see that 'NOT(Unknown)' also evaluates to Unknown. This means that 'NOT(NULL = NULL)' is Unknown as well. So what do we expect when we run this statement?

SET ANSI_NULLS ON
IF NULL <> NULL
     PRINT 'TRUE'
ELSE IF NOT (NULL <> NULL)
     PRINT 'FALSE'
ELSE
     PRINT 'UNKNOWN'

If you expect to see ‘TRUE’, guess again! ‘FALSE’? Not a chance. Remember what we said earlier – any comparisons with NULL evaluate to Unknown (neither True nor False). Since the comparisons in the IF statements evaluate to Unknown, ‘UNKNOWN’ is printed in this instance.

The same is true for any comparisons with NULL under the ANSI Standard; whether you are comparing NULL to a CHAR, INT or any other value, variable or table column.

Rule #2: In ANSI SQL, NULL is not equal to anything, even other NULLs! Comparisons with NULL always result in UNKNOWN.

NULL Variables

Variables that have been declared, but not yet initialized via SET statement default to NULL. They are treated exactly like any other NULL values. We can modify the previous queries to see this for ourselves:

SET ANSI_NULLS ON
DECLARE @MyRegion NVARCHAR(15)
SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] = @MyRegion

This returns no rows, just like in the previous query with '=NULL' in the WHERE clause. This has exactly the same effect as the following:

SET ANSI_NULLS ON
DECLARE @MyRegion NVARCHAR(15)
SET @MyRegion = NULL
SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] = @MyRegion

NOTE: You cannot put a variable in place of NULL in the IS NULL clause. The following query will *not* work:

SET ANSI_NULLS ON
DECLARE @MyRegion NVARCHAR(15)
SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] IS @MyRegion

ANSI Versus Microsoft

As we described above, the ANSI SQL Standard describes the result of any comparison with NULL as Unknown. So NULL = NULL, NULL <> 10 and NULL < ‘Hello’ are all Unknown.

Presumably to make the transition smoother for 3GL and 4GL programmers from other languages moving over to SQL, Microsoft implemented the SET ANSI_NULLS OFF option, which allows you to perform = and <> comparisons with NULL values. To demonstrate this in action, we can perform the following query:

SET ANSI_NULLS OFF
SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] = NULL

This allows you to perform basic equality comparisons to NULL; however, SQL written using this option may not be portable to other platforms, and might confuse others who use ANSI Standard NULL-handling. Also, if you try to perform this query without turning ANSI_NULLS OFF first, you will get no rows returned. The safe bet is to use SET ANSI_NULLS ON.

Rule #3: Use SET ANSI_NULLS ON, and always use ANSI Standard SQL Syntax for NULLs. Straying from the standard can cause problems including portability issues, incompatibility with existing code and databases and returning incorrect results.

NULL Math

One final point concerns math and string operations with NULLs. If you perform scalar math operations and string concatenation functions with NULL, the result is always NULL. For instance, this query returns NULL:

SELECT 1 + NULL

A numeric value plus, minus, divided by, or multiplied by NULL always equals NULL. A string concatenation with NULL equals NULL as well:

SELECT 'Joe' + NULL

So what if you want to treat NULL as a zero in a math operation? Use SQL's COALESCE() function:

SELECT 1 + COALESCE(NULL, 0)

The COALESCE() function returns the first non-NULL value in its list of values. So in the statement above, "COALESCE(NULL, 0)" returns 0. The SELECT statement above returns 1. Similarly you can use COALESCE() in string concatenations:

SELECT 'Joe' + COALESCE(NULL, '')

The COALESCE() function in this case returns a Zero-Length String, which is appended to 'Joe'. The end result is that 'Joe' is returned instead of NULL. SQL Server provides an additional function called ISNULL() which performs similarly to COALESCE(). Use the COALESCE() function (or alternatively use CASE), as it is ANSI standard syntax.

Rule #4: The ANSI Standard COALESCE() and CASE syntaxes are preferred over ISNULL() or other proprietary syntax.

Conclusions

SQL NULLs and three-valued logic can be confusing for a new SQL Database Designer; particularly if you are coming from a background in another 3GL or 4GL language that uses two-valued logic (i.e., C++, VB, etc.) Learning when and how to use NULLs in your databases, how SQL handles NULLs, and the results of comparisons and math/string operations with NULLs in SQL is very important in order to obtain consistent and optimized results.

GSQL in PPA

2010-06-02T01:57:00.000+04:00

From that moment you can download GSQL from Ubuntu's PPA (for 10.04). These packages have only PostgreSQL and MySQL support. Oracle support is not included. So, to use it you should make a some action:

$ sudo add-apt-repository ppa:halturin/gsql

$ sudo apt-get update

$ sudo apt-get install gsql gsql-postgresql-engine gsql-mysql-engine

Enjoy! :)

The obvious thing... but not all knows it.

2010-04-05T22:56:00.001+04:00

Become a Friend of GNOME!

2010-04-01T23:02:00.003+04:00

Help support the GNOME Foundation's goal to provide a free desktop for all users - become a Friend of GNOME. Your donation will ensure that GNOME continues to be a free and open source desktop by providing resources to developers, software and education for end users, and promotion for GNOME worldwide.

GSQL 0.2.2

2010-03-25T03:51:00.005+03:00

The « laty » release.

This release comes with an initial version of PostgreSQL engine and this is a significant step forward in the development of the project.

Whats new:

PostgreSQL support (experimental)
Plugin Tunnel (libssh 0.4.* is required)

Bugs fixed:

Fix some typos and GUI stuff (#25 and #29)
Fix oracle crashed on deleting variables (#23)
Added port number to MySQL and PGSQL login dialog (#21)
Fix mysql crashed with invalid query (#30)

Latest versions are available at:

Source code: http://code.google.com/p/gsql/downloads/list
Ubuntu's binary: able to download after Ubuntu 10.04 release here or use
$ sudo add-apt-repository ppa:halturin/gsql

Multiple engines and plugins helps defining a better API for GSQL, so contributions
are welcome! If you want to contribute you can ask for information and report bugs at:

#gsql on freenode
GSQL's mailing list: http://groups.google.com/group/gsql-dev
Bug report: http://code.google.com/p/gsql/issues/list

What's next?

You will expect another one release in the 0.2 branch and it will be bug-fix release. But the most interesting will be further. From this time all the resourses have been moved on the branch refact.API.stage2. This project has a new stage of evolution.

A new representational model of the tree structure of the database objects. Now all data will be stored in XML-tree. This feature will allow us to make the most flexible tool to support versioning databases. As you know, different versions of the databases have a different set of objects. The SQL-queries for getting object's information are also have changes from version to version. So, how will it be? For example, let's see sample pictures explaining some details:

The elements of the interface will become dockable. To realize this feature we will use the 'libgdl' library (it is a part of Anjuta project). The main menu will be completely redesigned. It means that all items of the menu will have correct behaviour which will depend on activity/availability of the functional.

SQL-editor - is the main tool of this project. It will undergo a lot of architectural changes. Also the most usable feature is able to customize hotkeys. It means you can set your favorite shortcuts scheme from the list of preset.

GNOME 3.0 migration.

The new API will be more usable and greatly simplify the development of your own Engines and Plugins. We will finish writing the WIKI-pages (NavTreeCookBook, HowtoWriteObjectEditor, HowtoWriteAPlugin, HowtoWriteAnEngine).

It is just a plan list of the 0.3.0 release. Namely this version will give a new wave of the project growth. So many things we would like to do, so many plans and we have a great desire to realize it!

PostgreSQL support.

2009-09-03T19:50:00.003+04:00

A few day ago Estêvão Samuel Procópio sent a letter to the mailing list that he is developing PostgreSQL support for my project. It is a really excellent news for me! So... the next release will include this 'engine' with 'experimental' status.

Why I'm not using libgda/ODBC/and.so.on

2009-07-20T11:36:00.000+04:00

I was constantly asked about not using ODBC. If my project is point at GNOME desktop integration, I'll not use the exist library (libgda) to the database accesss (libgda is the part of the GNOMEDB project). To answer this questions please read this wiki page

New logo!

2009-06-14T20:58:00.002+04:00

A few days ago i asked my friends (http://linuxgraphics.ru) for drawing a new logo. So... Konstantin Rotkevich has done it.
How do you find it?

I think it is perfect! There will be a new design with new logo in the near future.

GSQL 0.2.1 Released.

2008-12-23T09:27:00.001+03:00

In this release we fixed some bugs:

- Issue 7 (oracle-engine: SIGSEGV with TIMESTAMP data type)
- Issue 10 (restoring the window state after restart is fixed now)
- Issue 6 (mysql-engine: case sensitive on the database object names)
- Issue 17 (fix FreeBSD build)

Buildsystem was cleaned up (Now the files are installed according to
standards).

Debian build routines rewritten from scratch. Fixes for RPM building.

There are also several improvements:

- oracle-engine: scanning of tnsnames.ora - you can select alias name
from the drop-down list, or simply start typing the name of an alias
and get suggested autocompletion.

- option to switch window to fullscreen mode

- the logon window now includes action by default. you do not have to
aim to click "OK", just press Enter.

First, I would like to thank the developers Dmitry Oboukhov (Debian)
and Pavel Shevchuk (Fedora) for a great help in adapting build on
these distributions. I hope to see this project in official
repositories of these distributions in near future.

Also thanks to all who wrote bug reports! Thank you all! I'm looking
forwards to see more bugreports ;-)

Happy New Year everyone! Merry Christmas!

See you next year!

PS: Uninstall all previous ubuntu packages prior to installing new
version.

GSQL 0.2.0 released!

2008-11-30T00:18:00.007+03:00

Announcing the first public release of project GSQL version 0.2.0

GSQL is an integrated database development environment for GNOME. It is designed to support multiple database engines, so far MySQL and Oracle are supported.

This release introduces:

SQL editor
PL /SQL editor (in triggers, functions, procedures and packages) accessible in Oracle engine.
tree-like database object navigation
using standard notification service for pop up messages. E. g. in case of inactivity user will be notified on query completion
create and connect to a duplicate session with parameters copied from the active session, all in one click
configuration improved. It is now possible to tune GSQL look, customize editor and set up the settings of DBMS engine, e.g. set custom environment variables to access Oracle
new plugins:
- Terminal: opens a terminal session with parameters (such as login, password etc) taken from the active session
- Exporter: exports query results to a CVS file. So far only fetched records could be exported
- Runner: periodically executes SQL queries.

Release remarks:

MySQL engine:
- MySQL server version 5 and higher is required. GSQL relies on information_schema, which was introduced in version 5.0
- You can not cancel a running query as easy as in Oracle due to API limitations. You can always open a terminal session and cancel the query with “KILL QUERY”
Oracle engine:
- This version was tested with Oracle version 10.1.0.2.0. Should you experience any troubles with different Oracle versions please report to GSLQ authors
- It is not recommended to use oracle-xe-client from http://oss.oracle.com/debian repository, there are known issues with NLS with this client.

GSQL API is now stable enough, and you are highly encouraged to contribute your own plugins or support additional database engines.

We wish GSQL could become the main database development tool in GNOME one day.