This is something up with which we will not put!
Shouldn't FROM come before SELECT in SQL? (2011)
111–120 of 137 posts
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#112Earlier quoted context omitted.
I'm interested to understand why you think it should differ between OLAP and OLTP? Are you suggesting that columns closer to the top matters for OLAP bc OLAP is columnar? Ultimately, a query planner is going to figure out what happens first, so I can't imagine your point has anything to do with execution.
1. Not every OLAP/columnar/timeseries DBMS has a Query Planner. For example in kdb+/q a developer need to think about correct order of WHERE clauses, know which columns have attributes (kind of "secondary indexes"), and lots of other tricks. The advantage is predictability of execution, the disadvantage - it's too complex. 2. I'm not talking about cases where a Query Planner parses the query language DSL, and compute…
Now that you mention it, I guess I've experienced *similar* issues (more to do with joins, unions, materializations, windows, etc) even when there is a query planner.
Even though the query planner has an optimizer, the plan produced isn't optimal. In some cases I have had to play around with the SQL (resulting in less than ideal SQL) to get the optimizer to do what it should.
This demonstrates the exact point you made:
> The advantage is predictability of execution
In my example, while I ultimately overcame the issues with the sub-optimal plan, there's no assurances about what the query planner/optimizer will come up with tomorrow.
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#113FROM 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…
Your query can also start with "WITH", which isn't a command.
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#114PRQL [1] is a compile-to-SQL relational querying language that puts FROM first. [1] https://prql-lang.org
It would be really nice if PostgreSQL and sqlite implemented this natively as it really feels how SQL should be (re)designed today. It also makes writing complex queries easier as it would be possible to split out actions in multiple steps where you can see what columns and types you have created (and found) so far. Complex SQL is like complex C++: works if you get it right, but no help whatsoever for figuring out wh…
On one hand SQL has some oddities, on the other I'd rather there not be yet more different-but-equivalent ways to do things in SQL. I think the current state is good enough.
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#115Earlier quoted context omitted.
I'm not sure. I think it's fairly common for something like "Now was the column name on this table description_primary or primary_description? I can't remember... Oh well, I'll just SELECT * and figure it out later" to happen. Starting with FROM would at least eliminate that backtracking.
I can't say that you are wrong. I still find it hard to take as a complete argument. For one, you can almost certainly autocomplete all column names with an indicator for the table they come from. For two, you should be far more consistent in how you prefix things like that. :)
Oh for sure. This is definitely one of those "inherited some weird & inconsistent legacy system" situations.
Honestly, it didn't even register as a potential issue in my mind until I had a chance to use LINQ query syntax in C#, and thought it was kind of nice to have the `from` up front. It's a minor annoyance at most, at any rate.
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#116FROM 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…
Your query can also start with "WITH", which isn't a command.
[deleted]
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#117Earlier quoted context omitted.
Even neater. DuckDB has an implicit "SELECT *" if you just give it "FROM tablename", which fits your example.
But interestingly DuckDB doesn't support `SELECT 'FOO' AS BAR FROM DUAL;` or `SELECT 'FOO' AS BAR FROM 1;`. Which I guess makes sense if it has an implicit `SELECT *`. What would the implicit projection of `FROM DUAL;` be?
From my experience of Oracle `FROM DUAL;` projection would give you on a coin flip either an empty table or a stacktrace error from some Java library.
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#118Earlier quoted context omitted.
Nitpick -- SQL was not designed to be used programmatically. Same as shell commands, it's for humans to write manually. Proper API could have way more concise, and much more efficient format to parse. And would avoid a bunch of security issues along the way, same as for shell.
Exactly. I still remember about this "revolutionary" querying language being designed, that would allow for non-technical people to perform complex queries and reports from databases. People: stop trying to mangle SQL "because it would be better..."; nah, SQL is supposed to be the "better" already. The idiosyncrasies it has is because it was designed to be kinda-sorta conversational, modeled as an english language qu…
> SQL is supposed to be the "better" already.
For non-technical people. It is not supposed to be the "better" for the technical people who end up using SQL in practice. QUEL was the "better" for us, being much closer to Codd's vision. But, alas, Oracle won with the business people and Postgres lost.
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#119Earlier quoted context omitted.
In terms of actually writing queries live, like if you're in the CLI client, the order of UPDATE is definitely terrifying. The workarounds of writing it out of order or as a SELECT first are fine... I'd almost like to see a mode the interactive client sets that just rejects any UPDATE without a WHERE, and you'd have to do WHERE 1 or similar to get an "UPDATE everything."
If you are doing an update the first word is actually BEGIN. /* use your PPE */ BEGIN; /* make the change */ UPDATE foo SET bar = ‘baz’; /* sanity check */ SELECT * FROM foo WHERE bar != ‘baz’; /* oops, let’s pretend this never happened */ ROLLBACK;
That might help in dev, but nobody would use transactions when hand manipulating the live production database. ))
Re: Shouldn't FROM come before SELECT in SQL? (2011)
#120PRQL [1] is a compile-to-SQL relational querying language that puts FROM first. [1] https://prql-lang.org
It would be really nice if PostgreSQL and sqlite implemented this natively as it really feels how SQL should be (re)designed today. It also makes writing complex queries easier as it would be possible to split out actions in multiple steps where you can see what columns and types you have created (and found) so far. Complex SQL is like complex C++: works if you get it right, but no help whatsoever for figuring out wh…
I think that Perl taught us that there should only be one correct way to do something. Otherwise you wind up with a write-only language that nobody else can ever maintain.