7 ms·
Maybe avoid null in C++. I have no clue. But null is a useful concept in SQL.
by mirimir 11y ago
Maybe avoid null in C++. I have no clue.
But null is a useful concept in SQL.
- TrisMcC 11y agoAvoid NULLs in SQL schemas. Embrace NULLs in outer joins.
- wvenable 11y agoIt's impractical in real life to avoid NULLs in SQL schema. Just embrace NULLs altogether; they aren't hard to understand or work with.
- Retra 11y agoThey are hard to understand. You don't know if they represent missing data, invalid data, or valid data that is null-valued, so you never know how to handle it.
- mirimir 11y agoThey're easy to handle. For any column with nulls, you just need to deal with them explicitly.
- Retra 11y agoAnd that's your standard for "easy to handle?"
- mirimir 11y agoYes. What's wrong with that? 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.
- Retra 11y agoWhat's wrong with it is that it's cherry-picking. "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.)
- mirimir 11y agoI'm not talking about databases. I'm talking about processing data that will end up in databases. The final databases will for sure be normalized.
- wvenable 11y ago> 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. 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.
- wvenable 11y agoThat doesn't make them hard to understand. Every column requires some context to understand adding NULLs changes very little.
- Retra 11y agoThe context of a column can't tell you why a given row is null. For instance, if you're looking at a middle_name field in a database and you see NULL, you have no idea what that means. It doesn't model the thing the scheme was meant to model, it only models the system's ignorance about the world.
- wvenable 11y agoIf I also have column that is called "Active" that is True or False what does that mean? I have to look somewhere else for that information. You seem to want to ascribe more pain to nulls than makes sense relative to other values. 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.
- Retra 11y agoWhat it means is that you have an awfully named 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.
- wvenable 11y ago> Why you did that rather than throwing an error or generating a more informative value is a mystery. 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.
- dragonwriter 11y agoIt's not impractical; it is occasionally undesirable for performance or otger reasons to normalize to a level that would avoid all nulls in base tables, but more often than not nulls are permitted because of poor data modeling. Nullable columns are not always wrong, but they are a design smell.
- deleted 11y ago[deleted]
- Pxtl 11y agoWell, it would be if testing x = y produced sane results where one of those is null. 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.
- letstryagain 11y agoIdeally you'd want your RDBMS to refuse to compile tests like x = y in cases where either x or y could be null. You would have to explicitly handle nulls in boolean statements.
- dllthomas 11y agoI've been working on a linter for SQL - that's probably not a bad rule to toss in the mix...
- Pxtl 11y agoThat would be sensible if SQL dialects usually offered a sane comparison that returns true/false reasonably for null comparisons. Since they don't, and = is the only available equality test, I'm going to stick to my original point that null comparison in SQL is dumb and wrong for 99% of use-cases and compiler warnings would be solving the wrong problem.
- dllthomas 11y agoMySql, Postgres, and Vertica, at least, all support <=> as an operator that treats NULL as an ordinary, distinct value.
- yahelc 11y agoUseful but dangerous: If you have (1,2,3,4,NULL) as data in column foo, and you query select foo from tbl WHERE foo!=1 Returns: 2 3 4 Which, makes sense, but can be very confusing if you're not explicitly looking out for it. 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) Will return no results (instead of a single row with 5).
- mirimir 11y agoSELECT * FROM bar WHERE foo IS NOT NULL AND foo NOT IN (SELECT foo FROM tabl)
- dllthomas 11y agoThe fact that you seem to think that changes anything clearly demonstrates that this behavior is confusing.
- mirimir 11y agoOK, so I don't currently have a server to test that on. And I'm a little rusty. 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.
- dllthomas 11y agoYour adjustment will filter out any nulls that would have otherwise been in the output. The issue was not NULLs in the output (there was nothing in the output), but NULLs in the subquery in the NOT IN clause. You could address this by filtering inside the subquery: SELECT * FROM bar WHERE foo NOT IN (SELECT foo FROM tabl WHERE foo IS NOT NULL) Making this a little more realistic: SELECT * FROM activation_code WHERE code NOT IN ( SELECT activation_code_used FROM user ); If that query was previously returning many rows, I contend that many people would not expect it to start returning zero rows just because a user was created without using an activation code.
- dllthomas 11y agoNull is a broken concept in SQL. It conflates "unknown" with "known to be absent".