41–50 of 137 posts
HoneySQL lets us define queries with maps, like {:select [:col1 :col2] :from :table}, and turns that into SQL. In a better world, SQL would be structured data like HoneySQL, and the strange SQL syntax we know and love would be a layer on top of that, or wouldn't exist.
Linq does this [0], maybe partially because it's SQL-like but built for working with enumerables instead so they could do what they wanted. One other quirk of SQL ordering that always gets me is that SET comes before WHERE in UPDATE. I always get terrified that I'll run without the WHERE or without selecting the WHERE. Thankfully a good database tool like DataGrip will yell at you if you try to do a modifying operati…
This terrifies me as well. The workaround is to write the WHERE clause before the SET clause. If you inadvertently submit the query partway, it will be invalid and it's not a big deal.
And if you screw up, well that's what ROLLBACK is for.
Earlier quoted context omitted.
You need some way to put an alias on tables so you can join a table more than once though I guess that can be optional, say SELECT table.attribute or FROM table SELECT attribute or FROM table as a,table as b WHERE a.x=b.y SELECT a.z
Absolutely! I'm just generally saying that SQL could be more succinct and composable - it's all a combination of project, filter and other primitive operations from relational algebra. SQL is rather verbose, and the question of whether FROM or SELECT should come first is just paint on the object.
https://www.postgresql.org/docs/current/queries-with.html#QU...
and boy is it an awkward syntax. Circa 2008 I was getting interested in the "semantic web" and wasn't so happy with RDFS and OWL and thought Datalog would be a useful approach and it was an obscure topic then. 10 years later people struggling w/ SQL and other query languages revived it because it seems so much conceptually clean than alternatives.
Similarly there is something that looks terribly half-baked about triggers, stored procedures, etc. in SQL and I've long thought something based on production rules could be cleaner but the world just hasn't cared.
[item for item in items if item.include]
The order is confusing.
More broadly, foreach loops are also written in the wrong order.
More reasonable:
foreach(items as item)
FROM is not a command, it's a parameter, and an optional one at that. This is valid SQL. SELECT 1; The SQL commands are SELECT, UPDATE, INSERT, etc. Therefore, those commands should be the first thing in an instruction. If you have a file full of SQL, you probably want all the lines to start with those commands. Gonna be pretty weird to read if you have both SELECT and UPDATE lines that start with FROM. Probably diff…
that may very well be the case but perhaps there could be an evolution of the language because for newcomers the idea that the from clause is evaluated/executed/defined first is a pain point. Newbies I help sometimes wonder why things they reference in the SELECT part aren't visible/available in the FROM clause and that's because one is selecting the result of FROM ... JOIN ... WHERE etc anyway
Maybe my brain is broken by years of SQL and from learning English as a second language. But isn’t this supposed to follow a fairly mundane English sentence structure? “Select socks and pants from drawer”.
If you started saying “from drawer select socks and pants”, wouldn’t that feel like a weird sentence structure to most people?
Also SELECT should be the last clause (at least for row-oriented DBMS, for columnar databases it might come before WHERE, GROUP BY, ORDER BY to hint which columns we want to select to query on). Row-oriented: FROM table_name WHERE condition GROUP BY ... HAVING ... ORDER BY ... SELECT column1, column2, ...; Columnar: FROM table_name SELECT column1, column2, ... ORDER BY ... WHERE condition GROUP BY ... HAVING ...;
No, it should come after WHERE, GROUP BY but before ORDER BY. From my favorite SQL tutorial[1]: The lexical ordering is: SELECT FROM WHERE GROUP BY HAVING UNION ORDER BY while the logical order is: FROM WHERE GROUP BY HAVING SELECT UNION ORDER BY [1] https://blog.jooq.org/10-easy-steps-to-a-complete-understand...
But you need first to order by column to get the rows in the desired order, while SELECT just indicates which column from the rows to return.
source table -> filtered rowset -> grouped rowset -> filtered grouped rowset -> ordered rows -> ordered rows with selected columns only
Source: I was working several years as a Human Query Planner for the columnar DB ;)
I would like coffee from Starbucks. I don't say, from Starbucks I would like coffee
"Let's go to Starbucks and get coffee, soda, danishes, sandwhiches, soda, and a coffee cup."
It would be confusing to mention all the various items first and the store near the end.
"Let's get coffee, soda, danishes, sandwhiches, soda, and a coffee cup from Starbucks."
This aside, the fact that doing the FROM near the end prevents autocompletion, is plenty enough reason to change the ordering in my opinon.
Earlier quoted context omitted.
This terrifies me as well. The workaround is to write the WHERE clause before the SET clause. If you inadvertently submit the query partway, it will be invalid and it's not a big deal.
I'll usually write it as a SELECT, to be sure my WHERE is correct, then I'll convert it to an UPDATE. And if you screw up, well that's what ROLLBACK is for.
Earlier quoted context omitted.
that may very well be the case but perhaps there could be an evolution of the language because for newcomers the idea that the from clause is evaluated/executed/defined first is a pain point. Newbies I help sometimes wonder why things they reference in the SELECT part aren't visible/available in the FROM clause and that's because one is selecting the result of FROM ... JOIN ... WHERE etc anyway
> he idea that the from clause is evaluated/executed/defined first is a pain point Maybe my brain is broken by years of SQL and from learning English as a second language. But isn’t this supposed to follow a fairly mundane English sentence structure? “Select socks and pants from drawer”. If you started saying “from drawer select socks and pants”, wouldn’t that feel like a weird sentence structure to most people?
But the debate is actually coming from something else your example shows nicely. When you select something from a drawer, you select entities. Usually when we select from a relational table, we select properties. In SQL, the drawer is not a thing.
A different way to keep the English-style would be to add a BUT-JUST-[THEIR] clause.
SELECT FROM socks WHERE size = 45 BUT JUST [THEIR] color
All fairly tongue in cheek of course. SQL isn't going to change, so there's really no point in worrying too much about it :-)