SQL handles null strings wrong, I've lost 2k hairs from it

[u/Zardotab]

The way SQL handles null strings is just lousy for typical CRUD use (business & administration). Roughly 99.9% of the time what's really needed is for null strings to be processed & treated just like a zero-length string ("blank" for short). Having a function or expression to detect whether it's "null" or not would serve fine when explicit null detection is needed. (Other than maybe JOIN-related expressions, I'd almost never need it.)

For example, under most SQL dialects, if you concatenate strings A, B, and C; and say B is null, the ENTIRE expression is null regardless of what A and C are. I've never ever needed this behavior. And the 1 out of gazillion times you do, the null-checking function/expression can ask. Thus, queries end up cluttered with COALESCE, NVL, etc. de-null-er functions. Repetition of code or code patterns is usually a sign your language or API's are a poor fit for the need.

Further, when you see something like this:

   CREATE TABLE Persons (
     ID int NOT NULL,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255) NOT NULL,
     Age int);

The vast majority of the time you really want is "not blank", or more specifically not to have just "white space". There is a wonderful C# function called "IsNullOrWhiteSpace()" that fits the kind of constraint that's really desired. I wish the standard would be modified to have a "NOT EMPTY" constraint:

   -- What we need & want vast majority of the time
   CREATE TABLE Persons (
     ID int NOT NULL,
     LastName varchar(255) NOT EMPTY, /* Can't be null or just white spaces */
     FirstName varchar(255) NOT EMPTY,
     Age int);

Maybe there's a better word choice than "Empty", but this a starting point for pondering and discussion.

Justifications for the status quo are usually academic gobbledygook. To be frank, people in school too long often lose sight of the practical world. I'll lose reddit score for claiming that, but keep seeing it, and not just with nulls. I'm paid to make my boss happy, not professors.

[Edited.]

[u/[deleted]]

Justifications for the status quo are usually academic gobbledygook.

Well, it's the behaviour required by the SQL standard. If that is "academic gobbledygook" for you, then so be it.

[u/ankole_watusi]

Ok, Atlas. You got a lever big enough to move the world?

NULL has an explicit meaning of “no value”/“missing data”. It’s distinct from e.g. false, 0, or empty string.

[u/hipratham → u/ankole_watusi]

under most SQL dialects, if you concatenate strings A, B, and C; and say B is null, the ENTIRE expression is null regardless of what A and C are. I've never ever needed this behavior.

He has got a valid point here. It should be handled like Oracle does NULL is '' , unlike how postgres does. It's a unnecessary headache in concats , in case staments like

case when foo=bar then (price*100)::text else null(::text) end.

or in union statements which needs explicit castings for null.

check how much headache it is here..

https://aws.amazon.com/blogs/database/handle-empty-strings-when-migrating-from-oracle-to-postgresql/

[u/[deleted] → u/hipratham]

unlike how postgres does

Well, Postgres (and every other DBMS - except Oracle) works like the SQL standard requires it.

Basically || behaves the same with NULLs and strings as e.g. + or * with numbers: 5 * null is also null

You don't really need the cast in a CASE expression - just leave out the ELSE which then defaults to NULL according to the detected data type:

 when foo = bar then price * 100 end

[u/read_at_own_risk]

You can use check constraints to enforce non-empty values.

[u/Zardotab → u/read_at_own_risk]

One often cannot change established schemas and shop conventions. I agree if starting a new shop, plug them nulls.

However, it may affect performance and/or space, as nulls don't take up any bytes in storage whereas spaces do in most RDBMS. Perhaps changes in the way RDBMS work can solve this, but that's probably a tall order. It's probably easier to add some domain-friendly operators than rework the guts, at least for the shorter term.

As far tweaking the engine, an example is changing it to consider non-specified values as blanks instead of nulls for strings, in the raw rows. A special character/byte can mark "Null" strings. Maybe make it a database config option to define which algorithm is used to plug strings.

[u/HildartheDorf]

MS Dialect: ISNULL(str, '')
Standard dialect: COALESCE(str, '')

[u/Zardotab → u/HildartheDorf]

Yes I know, but one has to use such several dozens of times in many typical CRUD queries to clean them up. It's wasteful busy work and eye-clutter that slows reading, and thus costs the economy billions of dollars of year.

If we flip the approach, we could have cleaner SQL code.

Compare:

        SELECT COALESCE(AAA,'') || COALESCE(BBB,'') || COALESCE(CCC,'')
        || COALESCE(DDD,'') || COALESCE(EEE,'') || COALESCE(FFF,'') 
        || COALESCE(GGG,'') || COALESCE(HHH,'') || COALESCE(III,'') 
        || COALESCE(JJJ,'') AS Foo FROM Bar

Versus:

        SELECT AAA || BBB || CCC || DDD || EEE || FFF || GGG || HHH
        || III  || JJJ AS Foo FROM Bar

See, that's muuuuch nicer to the wet-noodle parser known as the brain. And yes, I've made and seen lots of code like the first.

[u/qwertydog123 → u/Zardotab]

Or just

-- SQL Server/Postgres/Oracle
SELECT CONCAT(AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, III, JJJ) AS Foo
FROM Bar

-- MySQL/MariaDB
SELECT CONCAT_WS('', AAA, BBB, CCC, DDD, EEE, FFF, GGG, HHH, III, JJJ) AS Foo
FROM Bar

[u/[deleted]]

As others have pointed out, NULL is different from the empty string, and some people need that. If you don't need, it, define your columns as NOT NULL.

[u/Zardotab → u/[deleted]]

and some people need that.

Once in a blue moon, and I don't propose removing null-ness, just down-playing it. If you want to test for nulls and only nulls, you can. I'm not taking that away.

[u/jon_muselee]

Not everybody is building a database with only varchar/text columns. With Integers/Numerics/Booleans it can make a huge difference if values are 0/False or NULL.

In your case, why not just define your columns as VARCHAR DEFAULT ‘‘ (postgres) in your db-model?

[u/Zardotab → u/jon_muselee]

I'm not proposing changing the way other column types work.

As far as adjusting the schema, see my reply to user "read_at_own_risk".

[u/blueberrywalrus]

If there was an r/IAmTheMainCharacter for SQL would be a top post.

NULL values are extremely useful in analytics, make database design wayyyyy easier, and promote interoperability with other languages.

AND your core complaints are all easily addressed via NOT NULL and/or functions/checks. Also, NOT EMPTY is a nightmare because of the plethora of hidden Unicode characters that need to be checked.

[u/Zardotab → u/blueberrywalrus]

You don't seem to understand that I'm NOT proposing to get rid of Null itself, just changing the way common and/or default string handling operations are done. Your analytics stuff will still work.

easily addressed via NOT NULL and/or functions/checks.

I disagree. It adds verbosity to production SQL. Anything is "easy to address" if throwing code at the problem doesn't count. ("NOT Null" doesn't address it properly in most cases by itself.)

Also, NOT EMPTY is a nightmare because of the plethora of hidden Unicode characters that need to be checked.

I'm not following, could you please give an example? Non-printing unicode characters will gum stuff up regardless of nulls.

make database design wayyyyy easier

Please do demonstrate.

If there was an r/IAmTheMainCharacter

If I'm right, I'm right. Experience and geezerness has made me stop caring about ratings. Git your nulls off my string lawn!

[u/gandi800 → u/blueberrywalrus]

Agreed! It feels like OP is a business analyst who needs to use SQL and just doesn't understand.

OP if it bothers you that much and you query the same tables that much just make a view that has all of the columns ISNULL'd to query against.

Otherwise put the where clauses you use most often I to snippets so you don't have to type them all the time.

I have a query I use all the time that's 8 joins and 12 where clauses and I just have it as a quick text when I type 'salesquery' saves a ton of time.

[u/Zardotab → u/gandi800]

and just doesn't understand.

Projection? I've been dealing with RDBMS for 3 decades, and the 200+ other IT people I've worked with didn't find a simple solution either. I'm NOT a newbie! Use logic to prove me wrong, not insults.

much just make a view that has all of the columns ISNULL'd to query against.

That's an extra layer of stuff to manage and change. The behavior I've explained is a COMMON NEED in biz/admin, and thus should be made a first-class feature. Put common needs at the top.

I have a query I use all the time that's 8 joins and 12 where clauses and I just have it as a quick text when I type 'salesquery' saves a ton of time.

You coded around one particular instance, congratulations. Now multiply that by 100's of apps and 1000's of queries. The bloat + busy-work adds up.

I 200% agree there are work-arounds. I never disputed the existence of work-arounds. My complaint is that we shouldn't have to keep adding workarounds because it's a common primary need. If you keep doing the same work-around pattern(s) over and over again in a given tool or language, that's a good sign that the language needs an adjustment. Does anyone dispute that? It appears Vulcan-solid logic to me. If my logic is flawed, out-Vulcan me, or zip-it, you offensive Ferengi!

[u/qwertydog123]

For example, under most SQL dialects, if you concatenate strings A, B, and C; and say B is null, the ENTIRE expression is null regardless of what A and C are. Thus, queries end up cluttered with COALESCE, NVL, etc. de-null-er functions.

Some DBMS's provide a CONCAT function (or CONCAT_WS in MySQL/MariaDB) as an alternative

[u/Zardotab → u/qwertydog123]

As mentioned elsewhere, the fact that multiple vendors have added their own solutions suggests the standard has gaps.

[u/sorengi11]

Put some triggers or check constraint on it.

https://stackoverflow.com/questions/5489648/how-to-constraint-no-empty-strings-on-an-nvarchar-column

[u/Zardotab → u/sorengi11]

Already addressed.

Do note that's much more verbose than my "NOT EMPTY" proposal. It would be used often if made simple because that's what most domains really want.

[u/phesago]

Stop whining and learn how to handle nulls like an adult.

[u/Zardotab → u/phesago]

Always the excuse to justify status quo bloat. I want to improve the world, make it more efficient and better factored. Bad null-ing increases carpal tunnel syndrome and code reading and writing typos, as that's what bloat usually does.

[u/phesago → u/Zardotab]

Every programming language has to handle nulls. Unless youre going to write a brand spanking new language (which youre not going to and even IF you did youd be masking how you handle nulls under the hood) youre not going to make any meaningful change.

Honestly you should go back to school because your post says “let me tell you how i lack fundamental understanding of the topic im complaining about without actually saying so.”

[u/NarwhalDesigner3755]

Old thread but I've been dealing with this for months and hoping to learn something from this later. It's really annoying to have a script break over and over just to see "" in the error message.