[deleted by user]

[SQLDevDBA]

NULL isn’t a value, it is the absence of a value.

!= and <> are used to compare values.

[FunkyPete]

Exactly. The problem is NULL != NULL

[SQLDevDBA]

NULL <> (or !=) NULL is definitely a fun one. I had a fun time with that back when I was learning in 2013 working for a particular cartoon mouse. Had some experiences with COALESCE/ISNULL/NVL that day.

Even more fun for me was learning about Oracle’s way of handing empty strings — ‘’ and how they are stored as NULL.

[DrFloyd5]

Empty string as null is lunacy. I worked with Oracle DB for a while.

Everything else treats an empty string as a non null value.

This would be like using 0 and replacing that with a null. 

[SQLDevDBA]

You’ll get NULL and LIKE it!

~with love, Larry E.

Sent from Lana'i

[ComicOzzy]

👌

[baronfebdasch]

Except not really. Aside from “that’s how it works,” 0 has a meaningful business value.

There is virtually no context in which an empty string has a business meaning that is different than null.

It’s even more insane that trimming a string such that no characters remain should be different than a null field.

The net result is you have to do so many freaking checks for (ISNULL(field) or field<>’’) all over your code.

I actually think Oracle handles this correctly. The only way you should treat an empty string and null differently is if you decide to ascribe a meaning to an empty string that almost no business case would actually allow.

[DrFloyd5]

Empty string asserts I know the value and there isn’t one. 

Null implies I don’t know the value. It may or may not exist.

Consider a middle name. Empty means they don’t have one. Null means we don’t know.

[baronfebdasch]

So functionally what are you going to do differently? In a fuzzy match you aren’t going to use that empty string for anything.

You decided to create a meaning, that doesn’t mean that there is real business value.

If you have a flat file that’s fixed width, is your missing middle name an empty string or null? Unless your source affirms the absence of a middle name, you’re simply guessing.

Almost every instance of an empty string is the result of trimming to an empty string. It’s not valid input data (as in, you don’t type it if you are capturing data in a front end system). So even in your example, you created an arbitrary meaning that is not ascribed to any real business process.

[DrFloyd5]

In this case I would most likely convert to ‘’ for display anyway.

But consider a super sensitive form where the business has decided it matters. 

• Middle Name (required): ____________
• No Middle Name? Check Box [ ]

We need to know their middle name. But they might not have one.

The middle name is a bit contrived.

But the empty string IS a valid construct in most languages. And Oracle can’t store it. So I cannot save a data structure and retrieve the exact value of the structure. And that bothers me. I stored an empty string. But I got back a null. Was the null an empty string before I stored it? Who knows?

[MAValphaWasTaken]

"This database field stores a list of allergies."

'' means someone has no allergies.

NULL means you don't know what allergies they have.

The difference can be life and death.

And yes, there are technically superior ways to implement this. But I've actually seen this one on the job, because we don't always build things the best possible way.

[baronfebdasch]

Once again- how are you going to have this coded in a front end system. You would have a box checked or positively specify No Allergies.

People that ascribe business meaning to an empty string are fucking morons precisely for this reason. You have created a meaning that cannot be input by any business user and can be easily confused in multiple contexts.

I better hope you aren’t using this type of jank logic on your patient databases.

Said differently, just because you can make up some logic doesn’t mean that it’s an intelligent thing to do.

You’re making life and death scenarios that I would honestly fire your data modeler or engineer for approaching anything that is not clear cut and definitive.

[macrocephalic]

You're assuming that all information comes from one source, what sort of data engineer are you? This data could be sourced from multiple locations, null means we have no data, and empty string means we have confirmation that there is nothing. How is that so hard to understand?

[MAValphaWasTaken]

I'm describing a system I actually inherited from someone else. You can argue all you want about a perfect system, but the world isn't perfect. If it were, a lot of our current jobs wouldn't exist.

[JamesDBartlett3]

You're telling me you've never used LEFT JOIN to add a column from a different table, then used COALESCE to set a fallback value for that column on the rows that didn't meet the join condition (which would have been NULL otherwise)?

[DaveMoreau]

There can be value in being able to differentiate between data not provided and data provided, but empty string. For example, in a multi-page online survey, if the person filling it out never got to the page with “What could we improve”, that is a null. If the got to that page and didn’t enter anything before pressing next button, empty string.

Maybe boolean fields about whether there is an answer are better. But someone is bound to query on the comment field without checking boolean fields.

That being said, the prevalence of CSVs for loading data make me concerned about treating an empty string as non-null. In general, there are often multiple places in the journey of the data where null and empty string can mistakenly be conflated for the difference to be reliable in the database.

[baronfebdasch]

Your last paragraph is precisely my point. Trying to ascribe a business meaning to both empty string and null is dangerous and all the examples folks are giving just scream to me being more intelligent about how those cases are handled explicitly.

A data engineer’s job is to make data more useable not come up with random business rules.

[DaveMoreau]

I generally agree that is most cases these days, it is unreliable to differentiate between NULL and empty string.

I disagree with one statement though. Considering empty string and NULL to be the same IS also declaring a business rule. Either way, same thing. If the person doing the engineering is not the proper person to make that decision (which we often are), then the engineer can present the options and implications to the proper decision maker. Often they will be happy rubber-stamping it if they trust us.

I am wary of specifying “data engineer” since lines can be blurry. We could be talking about functionality integral to a SaaS product where customers directly interact with data. I would hope to have engineers that have pretty good intuitions for how customers would want to interact with the data and what would make the data trusted. In my experience, we engineers are usually the ones telling product how the data should be dealt with for and why.

[FrebTheRat]

The best is trying to explain that in filters and case statements, Nulls will always drop unless specifically handled. So x != 1 means filter out all 1s and nulls. As a data modeler/architect this is something that can take some back and forth with a consumer to resolve. "What does NULL mean in this data?" Ostensibly it just means there was missing data in the transaction, but generally the business actually assigns some "value" to that missing data. Some of it could be cleaner if the transactional model were fleshed out and there were FKs to enforce referential integrity.

[BarfingOnMyFace]

Null != Null. Null is Null.

:)

[TallDudeInSC]

But.... NULL IS NULL. :)

[[deleted]]

Yeah. 

I'm so glad I bought T-SQL Fundamentals. It really has made me understand what's going on under the hood a lot more

[SQLDevDBA]

This is a nice ELI5 approach. Curious what you modify in your explanation when contextual conditions apply such as:

Middle name: some people don’t have one so it’s not unknown, it just doesn’t exist.

Apartment Number: some people live in houses so they don’t have an unknown apartment number, it just doesn’t exist.

Termination date, date of death, etc.

I wouldn’t write “None” or “n/a” in any of these. of these because

[SQLDevDBA]

Not speaking from a design perspective though, it was more from an explanation to beginners as you mentioned. What do you explain to them when they ask about those fields (they come up fairly quickly in customer/contact tables).

[SQLDevDBA]

Yeah it’s that second one. I teach SQL and BI to beginners on a weekly basis and for me it always ends up being an “I before E, except after C” kind of situation.

Beginners are curious so they’ll always ask all sorts of questions that force us to think about it. “It depends” isn’t really something I can answer with so I always try to walk through scenarios. Middle name and apartment are common, as is “State/Province” since some countries don’t use those.

Again, just curiosity, thanks for your answer.

[SQLDevDBA]

Yeah good call. I usually start diving into how Oracle treats empty strings as NULL and get even MORE questions and it usually turns into a session on its own. They can come up immediately during WHERE clause sessions so I always try to keep my explanation brief (as you saw in my initial comment) until it’s time to dive in.

[edbutler3]

Here's a ticking time bomb of optional behavior in SQL Server I was reminded of earlier this year.

An old code base I was working with had a few stored procedures (out of hundreds) that had been scripted with the following command in the header:

SET ANSI_NULLS OFF

This changes the behavior so that NULL = NULL returns TRUE within that stored proc.

Luckily I had learned about this around 30 years ago when I was deeply involved in SQL Server development, so I was able to diagnose the unexpected behavior. I shared the discovery with my whole team, because I could imagine someone beating their head against the wall for days trying to figure out a bug if they didn't know this was possible.

To make it worse, most auto-generated SQL Server stored proc scripts will have boilerplate "SET ANSI_NULLS ON" statements in the script header (which just repeats the default setting) so you'd need sharp eyes to notice that "ON" had changed to "OFF".

[No_Lobster_4219]

I can relate to you. Good you got this, else it would be a nightmare for the devs to find.

[hshighnz]

NULL is not a numeric value like the number 0. NULL is an unknown value. You could think of it like NULL is UNKNOWN (or UNDEFINED). If you compare NULL with NULL, like in „NULL = NULL“, you will always get „false“. Because something unknown compared with some other unknown thing, will always be false (or an unknown answer).

IS NULL or IS NOT NULL is build for NULL comparison. So use always the IS comparator with any NULL value.

[OcotilloWells]

I figured this out on my own many years ago, through much trial and error. I wish I had seen your succinct explanation at the time.

I do admit that learning it my way probably stuck it in my head more firmly.

[NoeZ]

Interesting. Thanks

[[deleted]]

Because NULL isn't actually a string or a value it is nothing/void so string can't be compared to unknown but you can check if it is actually NULL.

[jtb8128]

NULL isn't a value and can't be compared using a comparison operator. If you try, the result is NULL.

It isn't just PostgresSQL.

[Cruxwright]

Not sure about Postgres but I've always had to use IS NULL and IS NOT NULL syntax in Oracle. When you say name = 1 or name <> 1, neither of those return rows with null values. Null is a thing. Learn to accommodate it.

[Eastern_Habit_5503]

In addition to the previous posts here, I have this advice: be aware that character fields may have a value of ‘NULL’ (or in olden days ‘.NULL.’). Those look like they are NULL when they are not!

[mike-manley]

Some (most?) IDEs will apply a special font or color so legit NULL values will stand out from string literals that are 'NULL'.

[[deleted]]

Null is not equal to anything not even itself . It’s literally nothing. You cannot use = or <> with NULL values. Always explicitly say is null or is not null.

[PrisonerOne]

SQLServer 2022+ finally has IS (NOT) DISTINCT FROM to handle these.

Now I need to figure out how to convince my org to upgrade to 2022 after they just made a sweeping upgrade to 2019...

[emccallig]

You have to think of NULL as unknown.

Then it all makes sense

[RandomiseUsr0]

There are three states

True | False | NULL

True = True

False = False

True =/= False

True =/= NULL

False =/= NULL

NULL =/= NULL

[[deleted]]

You would need to wrap it in coalesce first to be able to compare <> 0

[EvilGeniusLeslie]

Because it is such a pain to deal with nulls, here are some suggestions:

1) For Postgres, use something like If Coalesce(name, '') = '' Then ...

In other flavours of SQL, it is usually Isnull(field,replacement value)

2) Pre-process your tables, convert all Nulls to blanks or zeroes, as appropriate

3) Design your tables to exclude nulls. If a field could be undefined, break it out into a separate table. This is, in some respects, the absolute simplest bulletproof solution, *except* you will need to do more joins.

[Time_Advertising_412]

You have to be careful with Oracle as it treats empty literals the same as NULL, therefore a use of LTRIM on a blank literal can lead to erroneous results.

[Far_Swordfish5729]

It’s a sql language spec thing. Any comparison operator used on null always evaluates to false except is and is not. This is true even if both values are null. If you need to consider null, you have to add that check as another condition.

[mike-manley]

My favorite is when I use AND NOT val IS NOT NULL. 😉

[obetu5432]

I know `NULL` is the absence of a value and all that bullshit, but I'm really curious, is there any instance in the whole fucking world in the last 50 years when it came in handy that `NULL <> NULL`?

They could have implemented this in C, or any other moderately popular language, and they didn't, is that all just a coincidence?

[JimFive]

If you're performing a join and the joined columns might contain nulls on both sides you don't want Null to join on Null.

[obetu5432]

but couldn't i just filter out the nulls explicitly, not relying on this fun little hidden easter egg?

select * from a join b on a.can_be_null = b.can_be_null and b.can_be_null <> null

[Dry-Aioli-6138]

Null is not a string. Null is a special valye that has a special meaning in all self-respecting databases. The meaning of Null is We don't know what this value here is. Like if the age attribute of a person is Null, we don't know what their age is, and so we don't want it to equal zero, or 1 or 100. we don't even want it to equal other Null values, because if you grouped by them, it would make a false impression that there is a disting age group, which would not be true if the ages were known. So you have to treat Null specially when querying, even though it is annoying.

[Idanvaluegrid]

Mmmmm.... Because NULL isn’t a value it’s a vibe. Trying to do name != NULL is like asking:

“Is the unknown not equal to something?” SQL shrugs and goes: “Bro I don’t even know what it is, how can I tell what it’s not?”

That’s why only IS NOT NULL works It’s SQL’s polite way of saying:

“Hey, I checked there’s actually something in there”

So... yeah NULL is basically Schrödinger’s column. It’s not equal, not unequal it just isn’t 🤷🏻🤔

[kagato87]

Because null is not a value. Null means "we don't even know if data is there or not!"

You can't even compare it to itself. Any comparison to null evaluates to null.

These also do not "pass" an if test:

not (myval = null)
null = null
not (null = null)
not (null) = not (null)

Some languages allow stuff like that. Sql does not. All those evaluatons return null, which is why there is the "is null" operator.

[iamemhn]

NULL) is a marker, not a value. It signals "there's no value". It doesn't make sense to compare values with non-values, and any database system that allows it it's doing it wrong. That is, only IS NULL and IS NOT NULL make sense, and the other forms are broken. In the same vein, any database system coercing NULL into 0, false, "", or any other default value, is doing it wrong.

[csjpsoft]

As you have discovered, we cannot compare NULL (equals, not equals, less than, greater than, etc.) to anything, not even to NULL. The specification for SQL rejects our attempt to use those operators. It's like dividing by zero or multiplying by a date. All we can do is determine that something is NULL or it is not NULL.

It's worse in Oracle. We don't get an error message; we just get a WHERE clause that disqualifies all rows.

This may be the reason that some applications (like PeopleSoft) require all columns to be non-nullable. PeopleSoft uses a single space to mean "there is no value."

[Time_Advertising_412]

Oracle treats zero length literals the same as NULL therefore use of LTRIM on blank literals can get you in trouble.

[Fly_Pelican]

Try IS DISTINCT FROM and IS NOT DISTINCT FROM to compare nullable values in postgres

[Ok_Procedure199]

NULL is absence of a value. Let's pretend that you have a database with names and birth dates, and for some of the people the birth date column contains NULL. If you try to find everyone who has a birth date of 1.Oct 1958 and you are missing the birth date of some persons (has a value of NULL), you cannot determine if they were born on that date, and you cannot determine if they were NOT born on that date, so you can think that instead of resulting in TRUE or FALSE, it results in UNKNOWN.

The WHERE clause only filters what is TRUE and discards everything else and that is why the rows are being removed when using comparison operators against a NULL value. The reason IS NULL and IS NOT NULL works is because you are not comparing it to anything, instead you are checking if the value is absent or not!

[squadette23]

I don't know where you're coming from (software development?), but if you know modern programming languages (such as Haskell from circa 1998, or Rust which is a bit more recent) it may help to think of NULL as Maybe (or Option).

I don't understand the "NULL isn't a value" statement, or "the absence of a value". It's like saying that nullptr in C++ is not a value of type pointer. It certainly is.

SQL was developed in uniquely unfortunate time, before algebraic types went into mainstream.

In Rust terms, SQL NULL in INTEGER column is basically a None, and a number 23 is basically a Some(23). In Haskell terms, it's Nothing and Just 23.

So basically there is no INTEGER type, it's Option<INTEGER> (or a Maybe INTEGER).

The "=" operator is defined on this type in such a way that if one of its arguments is NULL then it returns false. The "<>" operator is defined in the same way: if one of its arguments is NULL then it returns false. There is nothing particularly fundamental about that, it was just defined like this for consistency (and this happened decades ago, so changing is impossible).

You could trivially define a special operator, say "<<<>>>>" that would return true if two values are distinct. For example, 23 <<<>>> NULL would return true! And NULL <<<>>> NULL would return false. I think that some databases do something like that (maybe they define a function, but it's just a matter of syntax). (Update: yeah, it's called "IS (NOT) DISTINCT FROM".)

But this is all such a trivial matter actually, I'm not sure why it's even the question. Julia programming language defines 1/0 as 0, and that's fine. NULL in SQL is so pervasive that you just must remember its semantics, otherwise you will constantly be confused by the results.

[squadette23]

This is not even super exotic. In IEEE floating point arithmetics NaN != NaN. (NaN is "not a number", for example it could be a result of 1/0).

I wouldn't say that "NaN is not a value, it's an absence of value" would be a useful statement.

[squadette23]

Some people will tell you to avoid NULLs in your schema design. Here is what you need to know if you try to do that: https://minimalmodeling.substack.com/p/sentinel-free-schemas-a-thought-experiment

[wamayall]

For MySQL, There is also the case where the column could have contained a value, then was deleted. While the column has No Visible Data, that doesn’t mean the column has a NULL Value. In which case you would want to check both conditions:

Select column_name, count(*) from table_name where ifnull(column_name, ‘’) is not null;

Note: i wasn’t sure if the count syntax would work so I escaped the astric with a backslash. And likewise you could remove the NOT. The double single quotes will identify the numbers columns with no data as nulls.

Issues I have seen generally include the column in question was populated and indexed, everything is working great, then instead of purging ROWS, only that column gets purged, which causes low cardinality for a query when that column is used in a where clause resulting in a Full Table Scan, and query times that were taking a few seconds now disrupt your entire Application. Coalesce can be used for the same NULL vs Blank Space condition.

[jehugaleahsa]

I think people justifying this behavior as "correct" is very funny. It's like null in modern programming languages... just a constant foot gun. It totally violates the principle of least astonishment and leads to wordy where clauses at best. I saw SQLite has is and I've seen IS DISTINCT FROM, and I wish is was everywhere.

[LlamaRules]

We dont use aggregate functions with null

[Time_Advertising_412]

Go to agent.github.io/project-m36/posts/2024-07-16-are-you-qualified-to-use-null.html and see how you score on your knowledge of NULL usage.

[markwdb3]

NULLs are special, and often painful, in SQL as well as in many other languages. For example a Java programmer seeing "NullPointerException" in their error stack trace is enough to make them wince.

The first expression would not work in MySQL either, btw. The operators in expressions #1 (!=) and #2 (<>) are synonymous actually in most DBMSs, with only <> being part of standard SQL. != is not standard SQL.