11 ms·
Shouldn't FROM come before SELECT in SQL? (2011)
- HackerThemAll 3y agoOh of course, that would be the single most needed fix for the language, enabling intellisense and aligning with real query execution order. 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 ...
- datadrivenangel 3y agoSELECT ... FROM ... is more natural for smaller SQL. 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!)
- kreetx 3y agoHow about `table.attribute` entirely, instead of `SELECT attribute FROM table`?
- PaulHoule 3y agoYou 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
- kreetx 3y agoAbsolutely! 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.
- PaulHoule 3y agoLately I've been developing a system which represents something like a file tree in SQL tables and using https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE 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.
- kreetx 3y agoI've been on the lookout for new languages as well (though haven't created anything myself - unlike you). 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?)
- jjice 3y agoLinq 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 operation without a WHERE. [0] https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/standard-query-operators-overview https://learn.microsoft.com/en-us/dotnet/csharp/programming-...
- Someone 3y agoSQL could also have done what they wanted, for example allowing both FROM foo WHERE baz = quux SELECT bar and even (the equivalent linq wont compile because ‘baz’ isn’t there anymore when the ‘where’ runs) FROM foo SELECT bar WHERE baz = quux However, SQL predates smart code completion being an expected language feature, so they went for the feature “looks like normal American English” (“from the kitchen, can you get me the scales?” is less common than “can you get me the scales from the kitchen?” or “can you get me the scales? They’re in the kitchen”) instead of “make a grammar where smart code completion works well”.
- Fradow 3y agoThis 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.
- SoftTalker 3y agoI'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.
- noSyncCloud 3y agoIf you have to use TSQL like me, you can use BEGIN/ROLLBACK TRAN with an OUTPUT clause to easily confirm, then just change it to COMMIT. I actually like this workflow quite a lot
- cmdlineluser 3y agoDuckDB does this. https://duckdb.org/2023/08/23/even-friendlier-sql.html#from-first-in-select-statements https://duckdb.org/2023/08/23/even-friendlier-sql.html#from-...
- nemacol 3y agoDoes not seem weird to me that you would start with the type of query you are writing. Select, 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.
- vaibhavkul 3y agoYou have a good point, but the problem that not knowing the table to select beforehand does seem valid. What if the FROM clause is called SELECT and the SELECT clause is called PROJECT? (So it will read something like SELECT <table> PROJECT <columns>?)
- lupire 3y ago"The syntax is meant to resemble English", obviously. The link to https://www.lib.umn.edu/collections/special?id=291 https://www.lib.umn.edu/collections/special?id=291 is dead, though?
- sverhagen 3y agoEnglish readability is only true for the simplest of queries. That goes overboard quickly as complexity increases. So I'd happily give that up for the from-select order if it means better code completion.
- doubled112 3y ago"Select green apples from the refrigerator" vs "from the refrigerator, select green apples" The first is much more naturally spoken.
- bigfudge 3y agoIn English. I think other languages would have different word orderings. German for one, and I think maybe Russian too.
- reportgunner 3y agoBut SQL is using english words, what does german and russian have to do with it ?
- snarfy 3y agoThe second version is how you would say it in Spanish.
- taeric 3y agoI'm actually assuming most of the time you would leave off the "from" entirely in spoken language. It is implied or completely obviated by lack of choices, no?
- 3y ago
- jimlikeslimes 3y agoMy mental model was always that the order of keywords mirrored the order the database engine applied them. So WHERE is processed first and SELECT last.
- deleted 3y ago[deleted]
- baq 3y agotry EXPLAIN ANALYZE (or just plain EXPLAIN) and see for yourself!
- defaultcompany 3y agoIt’s not really mirrored (assuming you meant reversed). Here’s a page describing the two orders in relation to each other from a wiki I wrote: https://github.com/weinberg/SQLToy/wiki/Two-Key-Concepts https://github.com/weinberg/SQLToy/wiki/Two-Key-Concepts
- jimlikeslimes 3y agoOh wow, the wiki / project looks fantastically interesting. I've bookmarked it for a deeper read in the future. Yes I did mean reversed.
- nivertech 3y agoAlso 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 ...;
- marcosdumay 3y agoNah, in the row one, it should come before the order by. Alternatively, its semantics could change to reflect this ordering, but it would loss expressiveness.
- em500 3y agoNo, 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-understanding-of-sql/ https://blog.jooq.org/10-easy-steps-to-a-complete-understand...
- nivertech 3y agoYour example for a row-oriented just adds UNION. 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 ;)
- deleted 3y ago[deleted]
- chrisjc 3y agoI'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.
- x2bool 3y agoYes, it should. 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();
- marcosdumay 3y ago> 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
- ht85 3y agoIf you ever need column name autocomplete, writing an UPDATE statement and changing it after is an ok workaround. 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.
- Grazester 3y agoI would like coffee from Starbucks. I don't say, from Starbucks I would like coffee
- b86754 3y agoRight but that’s not how SQl executes unfortunately. You go to Starbucks then ask for what you need. SQL does a Cartesian product of all the tables you need, then filters with the ON clause. Only then can you “order” what you want in SELECT. 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.
- infamia 3y agoUnless I'm doing SELECT *, I almost always want quite a few pieces of data. Therefore it is more natural to say: "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.
- MightyBuzzard 3y ago[dead]
- derhuerst 3y agoPRQL [1] is a compile-to-SQL relational querying language that puts FROM first. [1] https://prql-lang.org https://prql-lang.org
- silvestrov 3y agoIt 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 what mistake you made when messing up. Step-by-step is such a great help.
- hot_gril 3y agoOn 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.
- hot_gril 3y agoThough I can see a strict subset of SQL being useful. First I'd axe the timestamp type in favor of always using timestamptz, cause those two things don't mean what most people think.
- dotancohen 3y agoI 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.
- pjmlp 3y agoAzure query language's Kusto, lously based on a mix of F# and SQL, does it, as does LINQ. 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.
- Delphiza 3y agoYou're probably getting downvoted just because of Azure, but you are right. ADX/Kusto and KQL is an extremely powerful query language that answers exactly what OP questions about SQL. 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 KQL also takes ideas from R's Tidyverse and magrittr package. It takes datasets and pipes them into a new function. Like this... car_data <- mtcars %>% subset(hp > 100) From the Microsoft auto downvoters out there, all Azure dashboards and infrastructure analytics run on KQL (think Graphana, but on Azure). There are billions of KQL queries executing continuously, so it is absolutely a good example of a non-SQL query language that is active and mature.
- pjmlp 3y agoI don't care, at least on Azure there are real people to talk to, when support is needed. :)
- systems 3y agowell, FROM should not be needed unless you are aliasing table names So ideally it would be SELECT Employee.Name, Address.Street WHERE ... GROUP BY HAVING ORDER BY or FROM Employee AS EMP , Address as A SELECT EMP.Name, A.Street WHERE ... GROUP BY HAVING ORDER BY We should always use fully qualified names is the select And optionally ommit them in further down clauses such as WHERE or ORDER BY if the names are unambigious
- mikece 3y agoI have always appreciated how Linq's syntax sets the data context first, then the filtering/grouping, and FINALLY selecting what columns should be in the result set. Not only does this make IDE hinting much easier but it's also more logical: "From the set of all left-handed NBA players who are from Europe and shorter than 201 cm, give me their month of birth." 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...
- kuriho 3y agoSAP ABAP's OpenSQL allowes you to write both: SELECT FROM scarr FIELDS carrid, carrname ORDER BY carrid INTO TABLE @DATA(result1). and SELECT carrid, carrname FROM scarr ORDER BY carrid INTO TABLE @DATA(result2). https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapfields_clause.htm https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/a...
- Apreche 3y agoFROM 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 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.
- hot_gril 3y agoYour query can also start with "WITH", which isn't a command.
- deleted 3y ago[deleted]
- dave4420 3y agoAlthough WITH clauses come first.
- gigatexal 3y agothat 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
- Swizec 3y ago> 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?
- tomconnors 3y agoIt's always frustrating to me that the order matters at all. I understand SQL can be complex to parse, but surely we are smart enough to come up with a parser that doesn't care whether FROM is before or after SELECT? 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.
- throwaway13337 3y agoPython's syntax also rattles me for the same reason. [item for item in items if item.include] It's almost exactly like a sql statement. The order is confusing. More broadly, foreach loops are also written in the wrong order. More reasonable: foreach(items as item) In the West, we read left to right. Presenting undefined terms before defined ones burdens the mind.
- dylan604 3y agoI !loved the coding style of (9 == $a) to protect against the common error of ($a = 9)
- rangerelf 3y agoSpeak for yourself, I've always loved Python's list comprehension syntax, it makes ALL the sense. For ME. 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.
- tmp_eSGPhx 3y agoagreed it is personal; this python snippet is an example that I've always had a distaste for, but which others love 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.
- chasil 3y agoSomebody could add this to the wiki, in the Criticisms section. https://en.wikipedia.org/wiki/SQL#Criticisms https://en.wikipedia.org/wiki/SQL#Criticisms
- vincnetas 3y agoThere is a hammer. Some people are skilled and proficient when using the hammer, some new people sometimes hit their fingers with the hammer. Do not try to replace all hammers in the world with something better, make a better hammer and leave old hammer alone, then if it's good enough, proficient people will adopt it and use it and new people will save their fingers.
- refset 3y agoOn page 4 of "A Critique of Modern SQL And A Proposal Towards A Simple and Expressive Query Language" [0] (recently published at CIDR 2024) there's a great diagram covering this and many other "semantical ordering" confusions with SQL. It's an interesting paper in general, by a couple of the biggest names in modern database research (...though admittedly perhaps not language design). Something like 'SaneQL' (which the paper introduces) deserves to succeed outside of the lab. Source is here [1]. [0] https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf [1] https://github.com/neumannt/saneql/ https://github.com/neumannt/saneql/
- efitz 3y agoIs the purpose of the syntax to maximize human readability or to make parsing efficient? 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.
- deleted 3y ago[deleted]
- rangerelf 3y agoTo maximize human readability. If I remember correctly from all those Byte mags back then, having understandable queries was an important factor.
- taeric 3y agoI still largely reject the code completion complaint. Specifically, it is very common to know what columns you want before you know what tables have said columns. This is especially true on normalized schemas where you are almost certainly having to do some joins to get all that you want. 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.
- dvlsg 3y agoI'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.
- taeric 3y agoI 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. :)
- dvlsg 3y agoOh 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.
- RodgerTheGreat 3y agoThe Lil[0] scripting language, like most APL-derived languages, has uniform operator precedence; expressions evaluate right-to-left unless you introduce parens: 3 * 2 + 1 9 (3 * 2) + 1 7 Lil includes an integrated query syntax which loosely resembles SQL. Queries begin with a command (select, update, extract), contain intermediate clauses in any order (where, orderby, by), and conclude with "from": select key value orderby value desc from x The order of evaluation of clauses matches precedence of other operators: "from" executes, then every intermediate clause, right-to-left, then finally the command and any column expressions or aggregations. Since queries are expressions, rather than statements (like all control structures in lil), having "from" last means that you can chain "subqueries" without nesting them: extract key orderby value desc from select key:first value value:count value by value from a I think this approach is nicely internally-consistent; the only real downside is that, as with SQL, this syntax is not ideal for IDE-driven auto-completion. [0] https://beyondloom.com/tools/trylil.html https://beyondloom.com/tools/trylil.html
- dylan604 3y agoWTF is right-to-left evaluation's purpose? It breaks the most fundamental rules of arithmetic. As a human that has been taught those rules, I'd never come up with 9 as an answer. Why did humans create a programming language that would?
- RodgerTheGreat 3y agoTraditional mathematical notation has precedence rules optimized for expressing polynomials. Programming languages- especially in the APL family- tend to have a richer collection of primitive verbs, and an "ideal" tower of binding precedence for all of them would be extremely complex, difficult to remember, and often unhelpful. 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.
- Lightbody 3y agoNRQL (New Relic Query Language) supports SELECT … FROM as well as FROM … SELECT. I still just live with SELECT … FROM. Too many decades of experience have locked in this habit.
- anon291 3y agoSQL is a mess. It's actually a fairly well-thought-out and straightforwards language. However, in the quest to make it 'human-readable', it's very unintuitive to compose.
- deepsun 3y agoThen let's stop using it for the wrong purpose. Any DBMS can have two interfaces -- SQL for human interaction, and proper API for machines to talk. More secure and efficient.
- anon291 3y agoI completely agree. I've written several DSLs that are much better for machine use, and they've gained traction, but to really shift the industry would require involvement from vendors, who seem uninterested.
- asavinov 3y agoThe sequence FROM Table AS t WHERE t.Condition SELECT t.col1, t.col2, ... might be more natural than the traditional SELECT t.col1, t.col2, ... FROM Table AS t WHERE t.Condition If we compare it with how loop are described in programming languages: Loop -> SELECT-FROM-WHERE Table -> Collection AS t -> Loop instance variable WHERE -> condition on instances In Java and many other PLs, we write loops as follows: foreach x in Collection if x.field == value: continue // Do something with x, for example, return in a result set So we first define the collection (table) we want to process elements from. Then we think about the condition they have satisfy by using the instance variable. And finally in the loop body we do whatever we want, for example, return elements which satisfy the condition. In Python, loops also specify the collection first: for x in Collection: Python list comprehension however uses the traditional order: [(x.col1, x.col2) for x in Collection if x.field2 == value] Here we first specify what we want to return, then collection with condition.
- pruetj 3y agoI used to work on an Ingres database using QUEL that did have FROM before SELECT, just with different key words. We would use something like: RANGE OF t IS table RETRIEVE INTO target (t.column1, t.column2) WHERE t.condition Instead of SELECT t.column1, t.column2 FROM table t WHERE t.condition I still find the joins to be more readable: RANGE OF t1 IS table1 RANGE OF t2 IS table2 RETRIEVE INTO target (t1.column, t2.column) WHERE t1.common = t2.common
- tmp_eSGPhx 3y agoThis is something up with which we will not put!