Hacker News Reader

Comment by roywiggins | Hacker News Reader

The 0th would be "it's an operator in relational algebra."

https://en.m.wikipedia.org/wiki/Relational_algebra

("The result of the natural join [R ⋈ S] is the set of all combinations of tuples in R and S that are equal on their common attribute names... it is the relational counterpart of the logical AND operator.")

⋈ amounts to a Cartesian product with a predicate that throws out the rows that shouldn't be in the result. Lots of SQL makes sense if you think of joins this way.

View on HN

In the functional interpretation of relational theory, a join is function composition, surprised that one was left out.

Good point, but not exactly correct. If R is a relation on A x B and S is a relation on B x C then SR = {(a, c) | aRb and bSc for some b in B}. The join uses (a, b, c) in the comprehension.

True, but I think slightly pedantic for this context. I think there have been proposed relational operators which produce the result you describe - a join, then remove the join key fields from the result.

If we are in a pedantic mood, also, a relational-tuple is not exactly the same as a mathematical tuple, relational tuples take different forms depending on who's system you are following (e.g. whether the fields are ordered or not).

Just endeavoring to make true statements. A relation on sets A and B is a subset R of the Cartesian product of A and B. E. F. Codd had something else in mind with his definition of join. Do you think he got that wrong?

The cross-product plus predicate is referenced in "A join is a nested loop over rows".

That's true, but I think the benefit of treating a Cartesian product as fundamental is that it lets you stop thinking about loops at all, or which one is the inner or outer loop, or of it as an iterative process at all.

Boxing all that up into the Cartesian product is a really useful concept, and the whole idea of relational algebra is to find convenient formalisms for relational operations, so it seems like it deserves a separate mention.

Absolutely. One of the biggest dis-services that SQL does is getting people thinking of relations as "tables" with "rows" which ends up making them think in an iterative, sequential, tabular model for something that I think they'd be better off thinking more abstractly about.

The whole relational model clicked for me a whole lot more once I started thinking of each tuple as factual propositions (customer a's name is X, phone number is Y), and then all the operations in the relational algebra start to look more like "how would it be best to ask questions about this subject?"... "I'm interested in facts about..."

1,000%. I learned discrete/set math in college and then later SQL on the job. Helping a few analysts moving beyond Excel skills to learn SQL was interesting. They struggled with some things that clicked quickly for me because I immediately had "oh, these are set operators" intuition. Stuff like when to use an outer join vs a cross join, or how to control cardinality of output rows in fancy ways (vs slapping `DISTINCT ON` on everything).

I passed on the set understanding where it explained an unintuitive SQL behavior and I hope it helped 'em.

Reply on news.ycombinator.com