Comment by geophile
4 years ago
Sort of related: thinking in relational algebra, or SQL. It appears to be "natural" to think about computing one atomic value at a time, in loops, or slightly less intuitively, recursive functions. (That latter choice may follow from whether your first language was pure Lisp.)
I was fortunate to have a teacher whose database course drilled relational algebra into us. This was in the 70s, shortly after Codd's paper, and well before SQL was invented, much less established. Now I think about much computation algebraically (and often functionally). But I do see that this is "unnatural" for many, including students, having taught databases for several years.
SQL reflects this. I often see students writing nested subqueries, because that is more procedural, where joins would be a cleaner choice. A colleague of mine wrote a paper many years ago, pointing out that thinking procedurally is more "natural" for many: https://dl.acm.org/doi/10.1145/319628.319656. But thinking set-at-a-time instead of one-at-a-time is a valuable skill, not that far off from thinking functionally.
It's easier not to mess up table based filters using explicit semi-join operators (eg. in, not in, exists) instead of using regular joins because joins can introduce duplicates.
Give me 'any join' operation - ie. just select the first value instead of all, and I'll happily use joins more. They are actually more intuitive.
It's not that relational algebra is untintuitive. It's because standard SQL sucks.
Indeed, I've taught myself to only use JOIN when I actually need some data from the table I join. For everything else I use EXISTS and friends.
I was thinking SQL could do with a keyword for that, like maybe FILTER, that looks like a JOIN but works like EXISTS.
Clickhouse implements an explicit SEMI join. It can be called semi or any, it doesn't really matter. It's just another join modifier
https://clickhouse.com/docs/en/sql-reference/statements/sele...
My problem with semijoins is that the semantics of "what exactly does a SELECT evaluate to inside an expression" are sometimes murky and might vary across databases.
Could you expand a little?
2 replies →
“First” doesn’t make sense without an order
It even has its own tag on StackOverflow: https://stackoverflow.com/questions/tagged/greatest-n-per-gr...
People who want it, want it with an order.
Look at
https://stackoverflow.com/questions/121387/fetch-the-row-whi...
and
https://stackoverflow.com/questions/3800551/select-first-row...
and
https://stackoverflow.com/questions/8748986/get-records-with...
and their combined thousands of votes and dozens of answers, all full of awkward workaround or ill-performing or specialised-for-one-database-engine code for this common and desirable thing which would be trivial with a couple of boring loops in Python.
It does make sense for semi-joins. I care about the key, not the value.
Random order is also a valid order.
1 reply →
Having taught it, do you have any recommendations for folks who are looking to improve their thinking in relations/sets/SQL skills?
I teach, in order, more or less (there is some interleaving):
- Data modeling - Relational algebra - SQL - DBMS architecture (buffering, btrees, query processing, index selection, ...) - Query optimization - Transactions
A few assignments have students write an in-memory relational algebra implementation, and then use it to write queries. A typical query is a deeply nested set of function calls (as a "one-liner"). And only then to we get to SQL. The hope is that RA is so ingrained, that the connections to SQL are easier to see. And this background is also really useful in understanding query optimization.
All of this material, including assignments, is available online (this is my own webserver): http://geophile.com/115.
https://arxiv.org/abs/1803.05316
I think this is related to "wholemeal" programming as some haskellers/FP-ists do, thinking in sets/tree/graphs operations.
> SQL reflects this. I often see students writing nested subqueries, because that is more procedural, where joins would be a cleaner choice.
In my experience, in the non-ad-hoc use-case, views can often be substituted for the procedural approach, forming the equivalent of a POSIX pipe.
*> A colleague of mine wrote a paper many years ago, pointing out that thinking procedurally is more "natural" for many: https://dl.acm.org/doi/10.1145/319628.319656. But thinking set-at-a-time instead of one-at-a-time is a valuable skill, not that far off from thinking functionally.
Hmm. Given the proliferation of tabular data tools (especially spreadsheets) over the intervening 40 years, I wonder if those results would remain the same today (and whether there would be any difference among Excel power users that use pivot tables, etc.)