Yes, relational algebra isn't a pointer machine. I was trying to give a mental image of what is going on—the resolution or lack thereof of tuples in a tuple-space.
Let me try another analogy, that might suit you better: if you think of each row-tuple in a relation in SQL as an asserted "fact" in a logic knowledge-base, e.g.
INSERT INTO parents VALUES ('x', 'y')
equates to the logical assertion:
parents('x', 'y').
...then a NULL result in SQL is the equivalent of asking your theorem-prover to look at this knowledge-base and deduce the truth-state of other fact you never told it anything about, e.g.:
?- parents('a', 'b').
...and getting back "unsat" — meaning that the theorem-prover couldn't prove your assertion true or false.
A theorem prover has the option to answer any question as Satisfied (Result) | Unsatisfied. This is what I meant by saying "a pointer to a result set, or a lack thereof." NULL is when you don't have such a pointer—which is why it's called NULL, by analogy to the DBMS's C implementation where the rowset pointer itself is likely set to be the pointer sentinel value ("NULL" or 0), rather than set to point to an empty rowset. The name NULL probably ended up in SQL after "bubbling up" from the implementations of the DBMS engines in this way.
The Weird Thing About SQL is that the grammar is phrased in terms of individual rows, whereas the query-planning occurs in terms of relations. So, when doing joins, you need a way to talk about what maps to "a join of a populated relation to an unpopulated relation", which in terms of individual rows, necessarily translates to "a real row-tuple value on one side of the join, along with some scalar sentinel row-tuple value [NULL] on the other side, standing in for 'a member of' the empty result-set."
That's the core semantics of NULL in SQL—to give you a 'scalar handle onto' the fact that there's no row on the other side of a join. That's where NULLs "come from", if you don't introduce them yourself (i.e. if you never make any of the columns in your tables nullable.) And so those semantics must be what you look at when figuring out what NULL "means." In joins, NULL doesn't quite mean "missing" or "unknown", but rather a combination of the two; it means "unsatisfied", just like in the theorem prover; it means that the query-planner has not yet been told enough to resolve your query fully.
(Yes, you can model an ingestion semantics where any row-tuple value that's not "in" a table, means that whatever predicate is represented by the presence of a value in the table should be considered to resolve false in the relation—but that's not really how SQL query-planning sees the world. It thinks of rows it doesn't have as "nobody bothered to tell me this yet"—unsatisfied—rather than "explicitly missing"—false. If you want false-ness semantics in SQL, it'd probably be better to define a view that generates rows with zero-values/false-values where the underlying table doesn't have values, and join to that instead.)
Let me try another analogy, that might suit you better: if you think of each row-tuple in a relation in SQL as an asserted "fact" in a logic knowledge-base, e.g.
equates to the logical assertion: ...then a NULL result in SQL is the equivalent of asking your theorem-prover to look at this knowledge-base and deduce the truth-state of other fact you never told it anything about, e.g.: ...and getting back "unsat" — meaning that the theorem-prover couldn't prove your assertion true or false.A theorem prover has the option to answer any question as Satisfied (Result) | Unsatisfied. This is what I meant by saying "a pointer to a result set, or a lack thereof." NULL is when you don't have such a pointer—which is why it's called NULL, by analogy to the DBMS's C implementation where the rowset pointer itself is likely set to be the pointer sentinel value ("NULL" or 0), rather than set to point to an empty rowset. The name NULL probably ended up in SQL after "bubbling up" from the implementations of the DBMS engines in this way.
The Weird Thing About SQL is that the grammar is phrased in terms of individual rows, whereas the query-planning occurs in terms of relations. So, when doing joins, you need a way to talk about what maps to "a join of a populated relation to an unpopulated relation", which in terms of individual rows, necessarily translates to "a real row-tuple value on one side of the join, along with some scalar sentinel row-tuple value [NULL] on the other side, standing in for 'a member of' the empty result-set."
That's the core semantics of NULL in SQL—to give you a 'scalar handle onto' the fact that there's no row on the other side of a join. That's where NULLs "come from", if you don't introduce them yourself (i.e. if you never make any of the columns in your tables nullable.) And so those semantics must be what you look at when figuring out what NULL "means." In joins, NULL doesn't quite mean "missing" or "unknown", but rather a combination of the two; it means "unsatisfied", just like in the theorem prover; it means that the query-planner has not yet been told enough to resolve your query fully.
(Yes, you can model an ingestion semantics where any row-tuple value that's not "in" a table, means that whatever predicate is represented by the presence of a value in the table should be considered to resolve false in the relation—but that's not really how SQL query-planning sees the world. It thinks of rows it doesn't have as "nobody bothered to tell me this yet"—unsatisfied—rather than "explicitly missing"—false. If you want false-ness semantics in SQL, it'd probably be better to define a view that generates rows with zero-values/false-values where the underlying table doesn't have values, and join to that instead.)