3 ms·
"SELECT" doesn't mean what you think it means, I think. In the relational algebra "select" is actually a "filter" operation. It's what the "where clause" impli
by cmrdporcupine 4y ago
"SELECT" doesn't mean what you think it means, I think.
In the relational algebra "select" is actually a "filter" operation. It's what the "where clause" implies. The other name for it is "Restrict." It reduces the result set down to the tuples that match on the supplied predicates.
SQL does a funny thing by putting it at the front of the query, but it does so as a way of saying "Hey, I'm about to begin a relational query."
What you've done here is more incorrect. Maybe? Not sure of your intent.
The name for the operator which "chooses which attributes to put in my results" is "PROJECTION". That's the thing that some people seem to think "select" refers to.
In a more pure relational system it would look something like (syntax totally made up)
Project( (id, first_name), Select( Predicate(last_name = $lastName), Relation(User)))
Or maybe:
User.Select(last_name=$lastName).Project({id, first_name})
As for this "order of execution" thing, SQL explicitly doesn't do that on purpose because the entire point is that the query optimizer can and should re-order things for the most efficient result. The beauty of the relational algebra (which SQL only partially implements) is that the order of operations can in many cases be re-ordered (in logically provable ways). So there is no set execution order. The system can take care of that for you.
- karamanolev 4y agoWithout any further knowledge, what I think they did is not so much express execution order in the RDBMS engine sense, but some logical generic-to-specific, large-to-small order. Table first (all rows, all columns), WHERE after (filter row), SELECT/project (filter columns). I'm not sure where all the other SQL operators would fit in that order, especially JOINs, GROUP BYs and so on.
- cmrdporcupine 4y agoYeah I guess the thing is that that 'logical' ordering is entirely arbitrary. It makes sense to this author I guess, but wouldn't to others. SQL's syntax is crap, but I don't see any advantages to this. Chris Date's Tutorial D had alternate syntax proposals. Personally I'd go for something more terse, like in the Rel Project's impl of the D concepts: USER {ID, FIRST_NAME} WHERE LAST_NAME= NAME($lastName) https://reldb.org/c/wp-content/uploads/2017/12/Rel-and-Tutorial-D-Quickstart.pdf https://reldb.org/c/wp-content/uploads/2017/12/Rel-and-Tutor...
- deleted 4y ago[deleted]
- twic 4y agoSQL's SELECT does both restriction and projection; indeed, it always does projection, but the restriction is optional: select id, first_name from user; So it's not crazy to equate SELECT with projection. If we're making up new syntaxes for relational calculus, i think something pipey would feel most natural: from user | restrict last_name = $last_name | project id, first_name; Letting you repeat stages as you see fit (this is somewhat silly example): from user | project *, is_boss = role in ('manager', 'executive') | restrict last_name = $last_name and is_boss | project id, first_name; You need some sort of converging fork to do joins, maybe: from user | restrict role = 'executive' > boss from department | restrict location = 'HQ' > hq_dept join < boss < hq_dept on boss.id = hq_dept.head_id | project first_name, last_name, dept_name Or maybe something better!
- madeofpalk 4y agoDid you just accidentally invent KQL? https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...
- twic 4y agoOh, I'm going to sue these bozos into the ground.
- cmrdporcupine 4y agoI like it. Let's implement it.