In relational databases (sql) tables are actually table representations of relations, where a relation is defined as any subset of a Cartesian product.
In the first sql example we have a “person” relation, which is a subset of namesXgenders cartesian product. Because of this an element of the person relation (“Jimothy”, null) cannot have “no gender” (as it wouldn’t be a member of the cartesian product namesXgenders).
All of this leads to the following: null in sql doesn’t mean “said element doesn’t have that property”, it means that said property is unknown.
With that in mind, the first example returns expected, if on the surface counterintuitive result: you don’t know what Jimothy 's gender is, so when queried with NOT M NOT F, that row shouldn’t be returned, because you can’t just assume Jinothy’s gender. The query should be, for those cases: not m, not f or unknown (ie is null).
Similarly for the second case. Email is not nonexistent, it’s unknown, so it makes sense that the db allows you to insert more than one person with unknown email addresses.
I wouldn’t say that either of these is an sql wtf, I would just call both of those tables badly designed.
You are not logged in. However you can subscribe from another Fediverse account, for example Lemmy or Mastodon. To do this, paste the following into the search field of your instance: !programmerhumor@lemmy.ml
Post funny things about programming here! (Or just rant about your favourite programming language.)
Rules:
Posts must be relevant to programming, programmers, or computer science.
No NSFW content.
Jokes must be in good taste. No hate speech, bigotry, etc.
In relational databases (sql) tables are actually table representations of relations, where a relation is defined as any subset of a Cartesian product.
In the first sql example we have a “person” relation, which is a subset of namesXgenders cartesian product. Because of this an element of the person relation (“Jimothy”, null) cannot have “no gender” (as it wouldn’t be a member of the cartesian product namesXgenders).
All of this leads to the following: null in sql doesn’t mean “said element doesn’t have that property”, it means that said property is unknown.
With that in mind, the first example returns expected, if on the surface counterintuitive result: you don’t know what Jimothy 's gender is, so when queried with NOT M NOT F, that row shouldn’t be returned, because you can’t just assume Jinothy’s gender. The query should be, for those cases: not m, not f or unknown (ie is null).
Similarly for the second case. Email is not nonexistent, it’s unknown, so it makes sense that the db allows you to insert more than one person with unknown email addresses.
I wouldn’t say that either of these is an sql wtf, I would just call both of those tables badly designed.