Maybe avoid null in C++. I have no clue.
But null is a useful concept in SQL.
But null is a useful concept in SQL.
If you have (1,2,3,4,NULL) as data in column foo, and you query
select foo from tbl WHERE foo!=1
2
3
4
Redshift (and I think Postgres generally) will also, very confusingly if you're not aware of it, return no rows if your subquery returns any NULL values in a NOT IN scenario.
If bar is (1,2,3,4,5)
SELECT * FROM bar WHERE foo NOT IN (SELECT foo FROM tabl)
But are you saying that my query doesn't resolve the NULL issue? I know that I've done just that with tables containing NULL values. Maybe I screwed up the format.
SELECT * FROM bar WHERE foo NOT IN (SELECT foo FROM tabl WHERE foo IS NOT NULL)
SELECT * FROM activation_code
WHERE code NOT IN (
SELECT activation_code_used FROM user
);
I understand why SQL chooses to treat null as unknown for comparison, but I hate it with every fibre of my being and wish SQL dialects would join the 21st century and let me define alternate concepts of nullity like we can with modern type systems.
Embrace NULLs in outer joins.
Let's say that I have a bunch of contact information, from multiple sources, to synthesize. Sources include guest lists, registration forms, notes from agents, and other hand written information. Some of the information is illegible, pages are ripped and damaged, etc.
So let's consider email addresses. In some of our sources, there's no place to enter them, but we know that most people have at least one. The appropriate value for those email addresses is "NULL". That's also appropriate for email addresses in other sources that are illegible.
Conversely, in reliable sources, missing email address means either that there is none, or that it shouldn't be used. In those cases, it would be appropriate to leave the column empty, or use "NONE", or even "nobody@dev.null".
That's how I learned to handle "NULL", anyway.
"Easy to handle" means you don't have to manually interpret the values every time you deal with them. That's the default! That's the hardest a thing gets to handle...
You're talking about an innately fragile process. This algorithm is not general, and it's not generalizable, and thus it represents an upper limit on the effectiveness of our ability to write good software.
You've got a method for solving multiple problems here; you gave me one example. Now give me an implementation of that algorithm, and you'll find it doesn't bother to employ nulls at all. You're just mapping other concepts onto null because that's how you imagine a database to work. You're familiar with a hammer, so you're employing nails all the time.
>Conversely, in reliable sources, missing email address means either that there is none, or that it shouldn't be used. In those cases, it would be appropriate to leave the column empty, or use "NONE", or even "nobody@dev.null".
A properly normalized database would put emails in another table and reference them using a key. You'll have no empty columns. You'll know there is no email because there is no value, not because there's a NULL value. If you want to send emails, you do a join on these two tables and get a list of people who have emails. This is the proper way to model such a domain.
In the mean time, you'll just have a fragile model. Then you'll use that fragile model to make fragile decisions, and your database will be completely useless in 40 years.
(Granted, your DB implementation might combine these tables as an optimization, but then the optimizer has a clean notion of what NULL means across your entire schema, and you shouldn't be exposed to it at all.)
A table with 2 columns (key, value) is logically equivalent to a nullable column in the parent table. There are times when a separate table is a good solution. But there are also times, for example if you have a lot of optional columns that cannot be grouped into sets, that such a thing would be massively over-complicated, space wasting, and poor performing.
NULL means I couldn't store a valid value in that column. Why? Well that depends on the system, just like the contents of every other column.
>NULL means I couldn't store a value in that column. Why? Well that depends on the system, just like the contents of every other column.
No, it means you stored a NULL value in that column. Why you did that rather than throwing an error or generating a more informative value is a mystery. And it is not made clearer by changing the name of your column. And someone writing code to handle that data can't know how to handle it, because they have no clue what went wrong, or even if it is wrong.
Why would you think it's automatically an error or that a more informative value could exist?
I might have a form with yes/no or date answers and if a question isn't required and the user doesn't provide an answer then it's stored as NULL. Either way the meaning is obvious, it's read and written perfectly fine, and it's not an error.
I don't understand the implication that storing NULL means something went wrong? That's not at all what it means to me. I'm going to assume something about your response: you seem to conflating the concept of stored null value in database vs. returning a null reference or pointer in C.
There's no way to programmatically determine which of these cases it is.
>I don't understand the implication that storing NULL means something went wrong?
It doesn't mean something went wrong. It also doesn't mean something went right. It means nothing by itself.
NULL simply has no consistent semantics in a DB model. Its only semantic purpose is to make joins consistent, which often has nothing to do with your problem domain. That's why higher levels of normalization tend toward fewer uses of NULL.
Imagine you were using a database to log processes on a supercomputer. You log access to the machine whenever a process starts, and you write a NULL value to the end_time field for the process. What does NULL mean, here? Does it mean the process is still running? Or does it mean the process failed to write it's end state (for instance, if the machine crashed?) Does it mean the process is non-terminating? If you read this value from the database, would it be correct to wait for the process to finish?
You'd have to go and look at what processes are running. Which means this field no longer models the end time of the process, it models some dirty hybrid thing that you have to manually verify every time you read a NULL value. That's the problem with NULLs: they are bad at modeling your domain. It's a big middle finger from your data telling you to verify it yourself. It is one of those things that makes computers stupid, because you can't get past it without involving a human brain. (Which would make it very hard to make a computer learn how to build database schemas.)
There is no need to programmatically determine which case it is. You're way over thinking the problem.
> NULL simply has no consistent semantics in a DB model.
Yes, it's the lack of a value. If you have an end_time field and you don't have an end_time and column doesn't allow NULLs, what do you put in there? It's a simple question. If you put any kind of valid value in there (which you have to do) then you'll be wrong.
> Or does it mean the process failed to write it's end state (for instance, if the machine crashed?)
You sure as hell better be using transactions to maintain consistency. This has nothing to do with the problem. If you do it this way that's just wrong. Has nothing to do with nulls. If you had a perfectly non-null FLAG you'd have the same problem.
> Which means this field no longer models the end time of the process
While the process is running it doesn't have an end_time, so what do you put in this field?
Thank you for saying the dumbest thing I've heard all week. This conversation is over.
God dammit why do we use this? Oh right, because every attempt to "fix" it throws out the relational algebra baby with the sql bathwater.