What exactly is nothing? Is nothing an absence of something? Or is it where there never was anything at all? Is it an indicator of no value, like zero? Or is it the total absence of any information whatsoever?
You may think these questions silly, but the context of "nothing" can have vastly different implications and lead to completely different results. Computers do not easily relate to our concept of nothing. When working with data, you may find that there are many ways to describe it.
zero: a numeric description of no value that can be used, for example, in a mathematical operation.
blank: a bit ambiguous, but often means an empty string. It is not the same thing as null.
null: a unique classification for the absence of value within a specific field and row. Using functions like ISNULL(), its presence can be tested for.
implied null: literally a string that reads 'null', 'none', or perhaps 'N/A'. It's not actually nothing, but if data has been inputted manually by someone it may imply nothing without the data being nothing.
what I like to call "void": a complete absence of a row or column that is trying to be referenced. Relevant, for example, if one is using a LOOKUP function.
Here are a few examples of different "nothings" being understood by computer software, namely SQL and Tableau, in different ways. Stay til the end - I saved the best for last:
1. SQL NULLs
If you want to select all values from a table where the values are not null, you may think to phrase your query like this:
SELECT column_name
FROM table
WHERE column_name <> NULL
But it will not work. This WHERE clause will not work either:
WHERE column_name <> 'null'
Queries using either will return no records. SQL doesn't recognize NULL as a numeric value nor a string. NULL is unique, unlike any data type. You must phrase your WHERE clause like this:
WHERE column_name IS NOT NULL
2. SQL "Count All" with Alias
In SQL, COUNT(*) will give you a count of all rows from the selected table:
SELECT COUNT(*)
FROM table
If you create an alias for said table however, say 'A', and then run the following query:
SELECT COUNT(A.*)
FROM table
Here it will return only those rows that are not null. This is a counterintuitive yet necessary quirk of how SQL reads nulls.
3. ZN Function in Tableau
Using the ZN (zero nulls) function in Tableau can be tricky. Its intended use is to locate any nulls within the expression and transform them into zeroes. It does not, however, work on anything other than nulls.
I'll share an example of this I encountered recently using Tableau's Superstore dataset from the 2022.3 version.
Let's say we want to put zeroes there where we see nothing. Unfortunately, using the ZN function doesn't work as intended. So what's going on? Are those not nulls? As it turns out, Tableau has "scaffolded" this table with blanks. But... are those actually "blanks"?
In this subset of Superstore, there are no rows for Bookcases Sales in February 2020. Because of the way Tableau shows data in the View however, there has to be a space there which necessarily includes that corresponding cell anyway. By doing this, Tableau turns a void into a blank.
But our problem hasn't changed - ZN doesn't work on blanks. To get around this, you can perform another process that changes the "blank" nothing into a "null" nothing. Using LOOKUP(SUM([Sales]), 0), a table calc which targets the data at the cell level, allows Tableau to "recognize" that there's no data there and, stay with me, in doing so tranforms the blank into a null. Then and only then does the ZN function work.
As you go forward in your journey with analytical tools, remember that when travelling to foreign lands it is best to try to learn the language of the people who live there. I would encourage you to try thinking how computers speak and think - it will only serve you well when treading amidst their domain.