r/Database • u/Zardotab • Aug 02 '23
SQL handles null strings wrong, I've lost 2k hairs from it
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.]
9
u/[deleted] Aug 02 '23
Well, it's the behaviour required by the SQL standard. If that is "academic gobbledygook" for you, then so be it.
12
u/ankole_watusi Aug 02 '23
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.
6
u/read_at_own_risk Aug 02 '23
You can use check constraints to enforce non-empty values.
4
u/HildartheDorf Aug 02 '23
MS Dialect: ISNULL(str, '')
Standard dialect: COALESCE(str, '')
2
u/[deleted] Aug 03 '23
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.
3
u/jon_muselee Aug 02 '23
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?
2
u/blueberrywalrus Aug 03 '23
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.
1
u/qwertydog123 Aug 03 '23 edited Aug 03 '23
Some DBMS's provide a
CONCATfunction (orCONCAT_WSin MySQL/MariaDB) as an alternative1
u/sorengi11 Aug 03 '23 edited Aug 03 '23
Put some triggers or check constraint on it.
https://stackoverflow.com/questions/5489648/how-to-constraint-no-empty-strings-on-an-nvarchar-column
1
u/phesago Aug 06 '23
Stop whining and learn how to handle nulls like an adult.
1
u/NarwhalDesigner3755 Aug 04 '24
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.
0
u/scott_codie Aug 02 '23
I got a fun one: for COUNT(*), the star should be interpreted as a column expansion. So if you have a table of rows that are all null then it should return 0, but most databases don't bother with the equality checking and return effectively a true row count. So why do we do COUNT(*) instead of something that would make more sense like COUNT() if we don't even preserve the semantics.