But, ultimately, SQL was designed around the idea discrete function calls rather than unit operations (with continued efforts to try and hack on the latter after the fact), so in that sense, the operator going first does make more sense.
SELECT [FROM <from-expr> COLUMNS] <column-expr>
"SELECT 1" would still be valid, and you'd still have the commands first, but you could also get the benefits of IDE autocomplete for columns by specifying the table before the columns.
A little wordier though.
(SQL is full of corner cases e.g. EXTRACT(WEEKDAY FROM field) etc. Putting the FROM first, or supporting multiple chained WHERE clauses or allowing WHERE before JOIN and applying to the preceding projection etc is all possible in an SQL parser that chooses to allow some relaxations. Personally, I am really irritated that I can't have HAVING without GROUP or QUALIFY without window functions etc, as I often construct queries programmatically.)
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 query.
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.
I have a very smart SQL IDE with great intellisense, but when I type "SELECT", it can't help me because it has no idea what I want. Being able to type "FROM table SELECT" would be way friendlier for humans, because IDEs could offer me immediately columns I'm very likely interested in.
`SELECT 1;` would still be written `SELECT 1;` since there are no table sources there.
> SELECT 1;
Something like Oracle's "dual" table would handle this corner case. Or just selecting a value from itself:
FROM 1 SELECT 1;
FROM ::integer SELECT 1;
> Intelligent tooling can and does already solve this.
It can progressively guess as you type, but it can't provide an upfront list of alternatives for autocompletion. That is an annoyance.
DuckDB has an implicit "SELECT *" if you just give it "FROM tablename", which fits your example.
Which I guess makes sense if it has an implicit `SELECT *`.
What would the implicit projection of `FROM DUAL;` be?
from values() select 1
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?
But similarly I've written a lot of C# LINQ statements at this point and its from-first pattern seems to flow naturally enough that I don't see anything wrong with it (and a lot right, C# definitely has a great autocomplete experience under LINQ).
But generally SQL is often very nicely translatable to English at least for me and so long as the SQL isn’t too overly complex.
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 :-)
A star schema usually contains one table to one entity mapping.
Dynamic tables for custom field on the other hand are usually multi entity tables. In this case, it would be a large junk drawer with various unrelated folders stuffed inside with everything from report cards, to keys to toys, to take out menus. It would have the toys entities, take out menu entities, keys entities, etc inside a mixed table.
Ah but that’s by convention! If we want to be super pedantic, they’re all just relations. The drawer table describes a relation between values. And SELECT just defines a new relation! You’re creating an ad-hoc “table”.
That’s why “SELECT socks FROM (SELECT socks, pants FROM drawer)” works just fine.
Ok yes my brain has definitely been broken by years of SQL. I’m not the right person to understand newbies anymore.
To a SQL Jedi order matters not.
Codd's original vision would have seen that expressed as something like:
RANGE DRAWER D
GET W (D#,SOCKS,PANTS)
DELETE D:(D.MONEY=1) FROM ℝ SELECT 1
ORDER DESC
COUNT(*)
> SELECT COUNT(*) FROM ℤ;
ℵ₀
> SELECT COUNT(*) FROM ℝ;
POWER(2,ℵ₀)
> EXEC sp_configure 'continuum hypothesis','1';
> RECONFIGURE;
> SELECT COUNT(*) FROM ℝ;
ℵ₁
This is valid SQL.
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 difficult for the parser also. Even hairier when it comes to subqueries.I do however, strongly agree that the most common scenario for user workflow is to choose tables first, then choose columns from those tables. I don't know if changing the language is really the answer. Intelligent tooling can and does already solve this. What I typically do is start all my queries by selecting *, then I go back and fill in the columns last.