Null values in a column are neither zeros nor spaces, although they appear to be spaces since the field is blank. Technically, null values are low values or Hexadecimal zeros.

So, how do you know if a field contains null values or just spaces? In the relational world, most databases do not store spaces, or the lack of a value for the field as a space. Good database design would load number fields with zeros, but data comes from a variety of places and can contain spaces or no value at all.   

All data types can be plagued with null values. The only exception would be primary keys (the coded relationship between data objects in a relational database). The associated field linked to a primary key should have values too or there would never be a match (the child row would never be retrieved).   

At the database level, there is code that will load the fields immediately with default values when a value is not presented and the row is being inserted into the database. How this is handled is database specific, but has been a common feature of most relational databases for quite a while. 

The moral of the story is you, the business analyst or user of the data need to be aware when selecting rows if the field might contain null values. Clause nulls are also an issue and have their own syntax to check for the existence of a null or non-existence of a null (i.e.: the field has a data value).   

This blog will focus on these areas of SQL using Traditional Toad Data Point: 

• Where Clause Null Value Syntax 
• Toad Data Point Helpful Techniques

Query Builder will also be demonstrated. Query Builder is a tool designed for beginners and allows for queries to be built using just the mouse. This feature also has a 'query' tab that allows for the SQL to be reviewed and tweaked. 

Where Clause Null Value Syntax 

The SQL allows for data selectivity (where clauses) with columns that have null values or the absence of a null value. 

The syntax is where COLUMN_NAME IS NULL or COLUMN_NAME IS NOT NULL. 

The obvious syntax would be COLUMN_NAME = NULL or COLUMN_NAME <> NULL. However, this syntax does not throw an error nor return any rows as it should. 

Here is your data. Toad Data Point displays the null fields with a {null} in them. This is an adjustable feature for displaying data and the {null} text is not actually in the column.

TDP Options allow for Toad and Toad Data Point to be customized for your individual needs and preferences.

The Environment>Grid in the upper left part of this panel allows you to choose how null values will appear in any Toad Data Point data grid. If these null values are of significant interest to you, change the color to red to make the null values column stand out more, such as changing the font and size of the text.

The text in the editor windows can be changed using the Options Panel Editor>General section.

As you can see, you can display how about any data type will appear. The Toad Data Point date format is MM/DD/YYYY. Numbers default to two decimal positions, etc.

This button on the Toad Data Point Workbook tool bar brings up the Options panel.

Traditional Toad Data Point and Toad Data Point Workbook are basically the same tool. So, the options you set in one interface will be saved and used in the other interface.

If we use the obvious syntax, WHERE = NULL, we get no data. This is the incorrect syntax and for some reason, it executes but does not throw an error nor does it return any data. I find this interesting because the novice might think there just isn't any null value data to be had.

The serious analyst will confirm and test their data selection to make sure the correct data is being accessed for the business need. The better you know your data, the better you can perform your analytical tasks. Toad Data Point has the features you need to access data across any number of supported databases.

This example shows the proper syntax and displays the proper data with null values.

This example shows the NOT NULL proper syntax.

Toad Data Point Helpful Techniques

If you are a novice and prefer using the Query Builder, this feature of Toad Data Point also supports NULL value processing.

Start the query builder from the ribbon bar and drag and drop the EMP data store onto the canvas. Select these four fields.

In the Where line for the COMM field, click on those three dots '…'.  

Notice that Query Builder has a drop down menu that allows for IS NULL and IS NOT NULL to be selected.

Query Builder creates the correct syntax that returns the right data.

Though data can often be plagued with null values, Toad Data Point offers an easy way to deal with null value processing.

Related Links

How to use Quest® Toad® Data Point to cleanse, transform, save and retrieve data

Access and query data with a repeatable process using Quest® Toad® Data Point

How to use Quest® Toad® Data Point to Securely Save and Share Data Sets