3 ms·
The order should be starting on FROM, followed by any sequences of whatever clauses (except for FROM), always creating an intermediary result-set. FROM table -
by marcosdumay 1y ago
The order should be starting on FROM, followed by any sequences of whatever clauses (except for FROM), always creating an intermediary result-set.
FROM table -- equivalent to today's select * from table
SELECT a, 1 as b, c, d -- equivalent to select ... from table
WHERE a in (1, 2, 3) -- the above with the where
GROUP BY c -- the above with the group by
WHERE sum(d) > 100 -- the above with having sum(d) > 100
SELECT count(a distinct) qt_a, sum(b) as count, sum(d) total_d -- the above being a sub-query this selects from
- sgarland 1y ago> always creating an intermediary result-set You want the DB to first run SELECT * FROM <table>, and then start operating on that?
- dragonwriter 1y agoPresumably, the intermediate result sets would necessary be materialized, only logical.
- kerkeslager 1y agoObviously not, and bringing this up as if it's a gotcha just shows you aren't keeping up with the conversation. Try less to correct people and more to understand people. It's about how humans think about it, not about how the computer executes it.
- sgarland 1y agoHow else do you read what they wrote?
- eyelidlessness 1y agoI read it as describing their preferred mental model for declaring a result set, which is different from describing their preferred behavior to produce it. This seems clear to me in wording and context; it’s also broadly consistent with how SQL is understood, and how it is generally implemented.
- deleted 1y ago[deleted]
- AdieuToLogic 1y agoHow would this approach support SQL constructs such as the HAVING[0] clause? Or is that what you meant by: WHERE sum(d) > 100 -- the above with having sum(d) > 100 0 - https://www.w3schools.com/sql/sql_having.asp https://www.w3schools.com/sql/sql_having.asp
- Pxtl 1y agoMy other big dream would be allowing multiple WHERE clauses that would be semantically ANDed together because that's what would happen if you filtered a result set twice.
- kerkeslager 1y agoYes! I've always been annoyed by this.
- marcosdumay 1y agoYes, by whatever clauses I also meant repeating ones. SQL has a conceptual issue with repeating group by clauses, so maybe not that one (or maybe we should fix the conceptual issues). But any other, including the limiting and offset ones.