On Tue, Sep 14, 2021 at 9:06 AM Merlin Moncure <mmoncure@gmail.com> wrote:
> I've long thought that there is more algebraic type syntax sitting
> underneath SQL yearning to get out.
I wanted to come back to this, because I've been looking to take a
single problem (from my perspective) and explain it concisely. Your
intuition is right on the mark.
Shell syntax is a pretty good lingua franca, so let's use it. Say you
were working at the command line, and you said something like:
cat somefile | awk '{print $3 " " $1 " " $5;}' | sort | grep "^Robert"
And the shell responded with something like: ERROR: syntax error at
or near "sort". After a little tinkering, you discover: that's
because the grep has to come before the sort. But why?
The database is not going to evaluate relational operations in order,
passing the output of one into the next as a shell pipe does.
Nevertheless, they are logically independent. Each should take in a
relation and either a second relation or a predicate, and return a
relation. Or, to put it mathily, relations are closed under
relational operations. So:
Operation 1 | Operation 2
and
Operation 2 | Operation 1
should both be valid, whether or not they're semantically equivalent
(though they often are). The operations are inherently atomic, and
can be understood in isolation. That's not the case here:
SELECT col_3, col_1, col_5 FROM sometable WHERE col_3 LIKE 'Robert%'
ORDER BY col_3, col_1, col_5;
Now, if this sort of thing suits the way you think, I say, "Great!"
I'm glad you have a language which suits you. For me, it's too rigid;
it assumes too much about what I might want to say. I wouldn't
program in a language like this, or use a shell like this. I don't
want to write database queries like this. I do, because it's how I
get to talk to the awesome toy in the background, but it always
chafes.
--
Ray Brinzer