Shouldn't FROM come before SELECT in SQL? (2011)
stackoverflow.com
stackoverflow.com
This is valid SQL.
SELECT 1;
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.
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 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 :-)
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.
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.
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.
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)> 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?
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.
from values() select 1
`SELECT 1;` would still be written `SELECT 1;` since there are no table sources there.
(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 [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.
FROM ℝ SELECT 1
ORDER DESC
COUNT(*)
> SELECT COUNT(*) FROM ℤ;
ℵ₀
> SELECT COUNT(*) FROM ℝ;
POWER(2,ℵ₀)
> EXEC sp_configure 'continuum hypothesis','1';
> RECONFIGURE;
> SELECT COUNT(*) FROM ℝ;
ℵ₁
[0] https://learn.microsoft.com/en-us/dotnet/csharp/programming-...
FROM foo
WHERE baz = quux
SELECT bar
FROM foo
SELECT bar
WHERE baz = quux
And if you screw up, well that's what ROLLBACK is for.
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."
/* 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;However if you write FROM table SELECT x, the IDE can first provide intellisense for table names while you write your FROM clause. Then it can provide intellisense for the SELECT clause based on columns in the tables/views/etc you listed in your FROM. So it was basically done for better UX.
Eg
Mylist.select(x => …) can determine the type of x, because it has the type of mylist —> List<T>
Select(x => …).from(mylist) and you simply can’t determine x, because C# type analysis can’t go backwards.
So I think it’s less a UX question and more of an absolute requirement for the API to work at all. The alternative would be a SelectFrom(x => …, mylist) function so it can be submitted in one shot, essentially what SQL is doing, but that’s disgusting — who doesn’t love function chaining?
select x => ... from mylist;
mylist.SelectMany(x => ...);
I am surprised that SQL Server hasn't offered Linq syntax as an option for writing queries and stored procedures; could always start such queries with `Using Linq:` prefix so the query engine knows it's not using T-SQL...
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 what mistake you made when messing up. Step-by-step is such a great help.
Row-oriented:
FROM table_name
WHERE condition
GROUP BY ...
HAVING ...
ORDER BY ...
SELECT column1, column2, ...;
FROM table_name
SELECT column1, column2, ...
ORDER BY ...
WHERE condition
GROUP BY ...
HAVING ...;Alternatively, its semantics could change to reflect this ordering, but it would loss expressiveness.
The lexical ordering is:
SELECT
FROM
WHERE
GROUP BY
HAVING
UNION
ORDER BY
FROM
WHERE
GROUP BY
HAVING
SELECT
UNION
ORDER BY
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 ;)
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.
2. I'm not talking about cases where a Query Planner parses the query language DSL, and computes Query Plan out of it. More the case when you kind of have an explicit Query Plan. The best example is FluxQL[1].
--
[1]. https://docs.influxdata.com/influxdb/cloud/reference/syntax/...
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.
Something like 'SaneQL' (which the paper introduces) deserves to succeed outside of the lab. Source is here [1].
https://duckdb.org/2023/08/23/even-friendlier-sql.html#from-...
what a weird thing to say.
[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)
It's a question of ergonomics, just like my favorite chair is not going to be your favorite chair for whatever reason, programming language constructs that bug you doesn't mean it'll bug anyone else.
Your foreach() example bugs the hell out of me, for example. But I'm not campaigning to have it erased from existence, I just don't use languages that model their syntax like that.
foo = 1 if true else 0
I think this was designed because most of the time foo should equal 1, and they want the code to highlight the default.
I don't like it because reading left to right it takes a few characters to realize it is a branching statement.
RANGE OF t IS table
RETRIEVE INTO target (t.column1, t.column2)
WHERE t.condition
SELECT t.column1, t.column2
FROM table t
WHERE t.condition
RANGE OF t1 IS table1
RANGE OF t2 IS table2
RETRIEVE INTO target (t1.column, t2.column) WHERE t1.common = t2.commonSelect, insert, update, delete, merge is the first word and says what you are doing.
If the keyword was in the middle of the query somewhere it would be harder to read.
SELECT FROM scarr
FIELDS carrid, carrname
ORDER BY carrid
INTO TABLE @DATA(result1).
SELECT carrid, carrname
FROM scarr
ORDER BY carrid
INTO TABLE @DATA(result2).
3 * 2 + 1
9
(3 * 2) + 1
7
select key value orderby value desc from x
extract key orderby value desc from
select key:first value value:count value by value from a
Unform evaluation order is much easier to remember and extend (no special cases!) and becomes even more natural than PEMDAS with a little practice. The APL family is hardly unique in this approach to precedence: Smalltalk has uniform infix precedence, Forths are uniformly postfix (unless you get goofy with parsing words), Lisps are uniformly prefix.
a = 1 + 2
a = (1 + 2)When you get 1000 line SQL files or 100s of SQL files, code management pain makes you yearn for FROM ... SELECT ... (three cheers for data build tool!)
SELECT table.attribute
FROM table SELECT attribute
FROM table as a,table as b WHERE a.x=b.y SELECT a.zhttps://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.
I'd say, getting the data (1) and triggers/procedures (2) are totally different domains with regarding to syntax. For select (i.e, 1), I have my ideas, but for (2) I've no clue. For (2), now that I think about it, I would say there are two additional levels of syntax that need to be solved: first, in addition to "getting data" you need also to modify it, so there need be syntax for that part (i.e the UPDATE part of SQL); and second, how to you connect these modifiers to events that happen, this is yet another domain of syntax IMO. (This latter feels like a general purpose programming language already, so maybe build it in to any of them which have great syntax already?)
FROM Table AS t WHERE t.Condition SELECT t.col1, t.col2, ...
SELECT t.col1, t.col2, ... FROM Table AS t WHERE t.Condition
Loop -> SELECT-FROM-WHERE
Table -> Collection
AS t -> Loop instance variable
WHERE -> condition on instances
foreach x in Collection
if x.field == value:
continue
// Do something with x, for example, return in a result set
In Python, loops also specify the collection first:
for x in Collection:
[(x.col1, x.col2) for x in Collection if x.field2 == value]
The link to https://www.lib.umn.edu/collections/special?id=291 is dead, though?
The first is much more naturally spoken.
"Saca las manzanas verdes del refrigerador"
Seems like the first example, vs:
"Del refrigerador, saca las manzanas verdes"
That sounds a bit stilted to me.
Green apples, from the refrigerator, select.
In contrast, the father of SQL, Alpha, allowed: "From the refrigerator, select the green apples, insert more milk, and remove any expired items."
A simple way to demonstrate this is how we ended up with cities that have massively different pronunciation even though the spelling is similar.
Most of these arguments that it should work some way logical to the DB make too many assumptions about how the internals of the database work and don't think about how the query optimizer/planner might work very differently than the way a query is organized.
SQL is really old now, 50+ years, assumptions about how it worked or work are probably not that relevant across its entire history.
I first learned it almost 30 years ago now, it was definitely taught back in the day that SQL was one of those odd languages that was designed assuming you "wouldn't need an engineer" to write it. Laughable but would explain why engineers might not find the syntax logical.
So, does it somewhat complicate the logic? Of course. It is by no means impossible, though, and unless you are using super generic column names everywhere, the search space for what tables to suggest will be helped by knowing what columns you are looking for.
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.
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.
SELECT Employee.Name, Address.Street
WHERE ...
GROUP BY
HAVING
ORDER BY
FROM Employee AS EMP , Address as A
SELECT EMP.Name, A.Street
WHERE ...
GROUP BY
HAVING
ORDER BY
SELECT/DELETE/INSERT etc. are commands, it makes sense to me that if I were writing a parser I would start with the imperative that will determine the rest of the path through the parser. I'm speculating, but I think I'm right, given this is late 70's/early 80's technology and resources were much less abundant.
If I remember correctly from all those Byte mags back then, having understandable queries was an important factor.
It would probably be a tough change to push through for the standard committee... a great one for users though, even if it takes 10+ years until you can use it in production.
I end up always starting with the generic form select * from db, and then go from there, as code completion then works if there is at least one db.
Here's an example of a KQL query that I have in my browser...
Things
| where DeviceTags has "Installed"
| order by LastHeardFromTimeStamp desc
| take 10
car_data <-
mtcars %>%
subset(hp > 100)
I still just live with SELECT … FROM. Too many decades of experience have locked in this habit.
Before you know what you can SELECT you should know FROM where does the data come.
Except maybe cases where there is no data source: SELECT 1; or SELECT NOW();
I don't think the standard SQL allows those. Even though I do personally prefer this form to the standard one.
Anyway, if we are serious about maintaining SQLness, it would be something like:
SELECT FROM people COLUMNS id, name
And that reduced form would become SELECT COLUMNS 1
Yes I did mean reversed.
Look at Linq in C#, there they implemented it correctly and it's just awesome to use. It's so much more flexible and fun.
from ...
where ...
join ...
where ...
group ... by ...
where ...
select ...
I don't say, from Starbucks I would like coffee
In standard SQL syntax you’re essentially asking for what you want at your house then driving to a Starbucks.
More readable but doesn’t follow the execution order at all.
"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.