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 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. 

EMP data with null values

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.

Toad data point field display options

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 TDP Options Access

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.

wrong syntax for NULL Values in DataIf 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.

Data with NULL Values

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

Data without 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.

EMP Table Data in Query Builder

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 '…'.  

Query Builder Where Clause Selections

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

SQL Tab of Query Builder

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

Try Toad Data Point for free

Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.

Get started with our free 30-day trial.

Already in a trial? If Toad Data Point is helping you connect, query and prepare data for faster business insights, buy it now or contact a sales representative.


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

About the Author

Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Start the discussion at