6 ms·
I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. NULL can be the value of a field in a reco
by crazygringo 4y ago
I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me.
NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN.
If I run:
SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a
and I get back
[5, NULL]
I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5.
Obviously there are lots of workarounds, but to me this is a truly fundamental design flaw. SQL should have specified another NULL-like value, call it EMPTY, used only for joins that find no data. And analagous to IS NULL, it would be checked using IS EMPTY.
- onlyrealcuzzo 4y agoHow do you expect `y.b` to behave on EMPTY rows? Should it return EMPTY or NULL?
- goto11 4y agoA nullable column can always be extracted to a separate table with a non-nullable column and a foreign key. If you left-join this back with the base table, you will get the nulls again for the missing values. So to me it seem nicely symmetrical to use the same kind of NULL value.
- dspillett 4y ago> but it's the twin meanings of NULL that really kills me NULL only has one meaning: NULL. This is roughly analogous to unknown. The one that his a lot of people is WHERE <value> NOT IN (<set>) where <set> contains a NULL. Because NOT IN unrolls to “<value> <> <s1> AND <value> <> <s2> AND … AND <value> <> <sx>” any NULL values in the set makes one predicate NULL which makes the whole expression NULL even if one or more of the other values match. > I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5. Not directly, but you can infer the difference going by the value of (in your example) y.a - if it is NULL then there was no match, otherwise a NULL for y.b is a NULL from the source not an indication of no match. > SQL should have specified another NULL-like value This sort of thing causes problems of its own. Are the unknowns equivalent? Where are they relevant? How do they affect each other? Do you need more to cover other edge cases? I have memories of VB6's four constants of the apocalypse (null, empty, missing, nothing). This is one of the reasons some purists argue against NULL existing in SQL at all, rather than needing a family of NULL-a-likes.
- onlyrealcuzzo 4y ago> This is one of the reasons some purists argue against NULL existing in SQL at all, rather than needing a family of NULL-a-likes. So then are there no optional values? What happens when OUTER JOINs don't match?
- vbezhenar 4y agoYou don’t do outer joins.
- Koshkin 4y agoDon’t these exist for a reason?
- andy81 4y agoOf course. You've got a list of countries and are pulling each country's national flower, national bird, largest port city etc. Without outer joins, Liechtenstein with no ports doesn't show in the list at all. Sad news for people who want to know all countries, or Liechtenstein's national bird (eagle).
- vbezhenar 4y agoYou’re not obliged to pull everything in one request. You can issue several requests.
- tremon 4y agoSure. You're also not obligated to include a WHERE-clause in the query you send to your database. You can do the filtering in the application.
- vbezhenar 4y agoWhat's your point? You can live without nulls, they're not required and you don't need to avoid WHERE-clause for that. It's a theoretical concept, nobody in sane mind would do that, but nulls are not required for relational algebra.
- gfody 4y agoyou should not need to distinguish between empty and null because they mean the same thing. if you want to select records from x that aren't in y you should use an anti-join (where not exists, not in, etc.)
- thedataslinger 4y agoThis is not true--especially when considering different SQL implementations (e.g. Oracle SQL versus Microsoft SQL). NULL and EMPTY handle the intersection of ontic versus epistemic claims. EMPTY implies a known, 0-byte value whereas NULL can imply either an unknown value or the "unknowability" of a value (i.e. the in-existence of a value). In practical terms, this would be like equating the statements "I don't know whether that dog has a name" (i.e. a NULL name) and "I do not know the name of that dog" (an EMPTY name). The former does not assert the existence of a proper noun to represent "that dog", whereas the latter implicitly asserts that there exists a proper noun which nominates "that dog".
- gfody 4y agobeg pardon I wasn't aware sql actually defined empty. of the engines I'm familiar with there is no keyword for empty like what gp is asking for (and I hold my argument for why it should not be necessary). it sounds like what you're describing is a blank string and I agree with using blanks to distinguish between "has no phone number" and "we don't know their phone number" but that isn't what gp was talking about.
- thedataslinger 4y agoTo piggy-back, it bothers me so much that this is valid syntax in many implementations: UPDATE x SET a = NULL WHERE b IS NULL; Like... wat?
- paulclinger 4y ago> I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5. You can always add SELECT y.a, which will allow you to disambiguate between the two options (it will be non-NULL in the first case and NULL in the second).
- janci 4y agoThis is why JS has both null and undefined.
- janci 4y agoAlso you should select y.a to know wether the y-record exists.
- ianmcgowan 4y agoI'd check to see if y.a IS NULL in that situation. I'm sure there are cases where it matters, but most of the time for me the difference between "there's a row in y, but the value is NULL" and "there's no row in y, the value is NULL" is irrelevant. I can't think of a time when that distinction has been important, and I'm working on a project converting thousands of complex SQL queries. The thing that really bugs me about NULL is the default assumptions - 99.9% of the time I want NULL in a string context to be the empty string and NULL in a numeric context to be 0, but I have to use ISNULL or COALESCE to get that. I wish it were the other way round where NULL is treated as '' or 0 by default, but I can do something special if I really want to test for NULL'ness.
- crazygringo 4y agoInteresting, we must work with really different data/queries. I've come up against the distinction lots of times (and yes have to retrieve additional fields in order to address it), while I don't ever want to confuse 0 with NULL. Tons of things are legitimately zero but crucially non-null, like an inventory count. (Empty strings, on the other hand, do seem much more interchangeable with NULL in probably the vast majority of contexts.)
- emmelaich 4y ago'' is NULL in Oracle. People complain about it, and it always seemed a little weird to me.
- rrrrrrrrrrrryan 4y agoWhy are you joining on a nullable column in the first place? If your database is well designed, joining on a nullable column should be a relatively exotic use case, and can be handled by writing a tiny bit more code to check for NULLs before performing your join.
- crazygringo 4y agoIt's not. Assume y.a is the non-nullable primary key, while y.b is the column that may be null. But in this example there might not be a row where y.a = 5. Or there might be. But you can't tell.
- tremon 4y agoBut in those cases, you can always tell by including the non-nullable key in your result set. It's already being evaluated, so it's virtually free (only adds to the network transport size).
- cryptonector 4y agoYou need one more output column. The specifics will vary by RDBMS because SQL isn't that standard. The most portable thing would be to do this: SELECT x.a, y.a IS NOT NULL, y.b FROM x LEFT JOIN y ON x.a = y.a; assuming y.a is a NOT NULL column, or a PRIMARY KEY column, since PKs are supposed to be non-nullable. In PG you could: SELECT x.a, y IS DISTINCT FROM NULL, y.b FROM x LEFT JOIN y ON x.a = y.a;
- Shorel 4y agoGood point, it would be nice to have both NULL and EMPTY to split these meanings.
- cmrdporcupine 4y agoFabian Pascal made a whole career out of writing about how stupid "NULL" is in SQL. See if you can find his writings some time. He was positively livid about it. Notably NULL of any kind is completely absent from the relational algebra. There are other ways to express absence. Notably, this is all supposed to be first-order predicate logic <hand wave hand wave> stuff, and... in that world "null" is also not a "thing". Also SQL insists on allowing duplicate "rows" in its "tables"; whereas the relational model is very clear that the contents of a relation ("table") is a set, not a bag. These two confusions in SQL actually lead to many many data integrity problems as follow-on effects and also complicate query planning and optimization.
- SPBS 4y agoWhy does it kill you? A NULL in a row is semantically identical to the row never existing in the first place, unless there is some other non-null column (the primary key) that lets you disambiguate between the two cases. It's hardly a problem in practice because all good tables should have primary keys.
- rawgabbit 4y agoAs another commenter said, if your table have required ID columns, you should specify it in your SELECT. SELECT x.Id,x.a,y.ID,y.b FROM x LEFT JOIN y on x.a = y.a The output [Id12345,5,Id6789,NULL] means the column is null. The output [Id12345,5,NULL,NULL] means the row is empty. On a side not, I use the SQL below all the time to identify missing rows on the "comparetable". SELECT #basetable.columnname FROM #basetable LEFT OUTER JOIN #comparetable ON #basetable.columnname1 = #comparetable.columnname1 and #basetable.columnname2 = #comparetable.columnname2 and #basetable.columnname3 = #comparetable.columnname3 WHERE #comparetable.columnname1 is null
- ErwinSmout 4y agoThe predicate corresponding to an outer join is fundamentally disjunctive in nature (any outer join is equivalent to a UNION of at least 2 SELECTs) meaning that "after the fact", there is no way to determine which particular one of the disjuncts made the row appear in the result.