6 ms·

The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs

• toto444 4y ago
  Every time SQL is mentioned on HN someone comes to complain about FROM coming after SELECT. I use SQL every day and not a single time have I found reason to complain about it. Can you give a bit more detail about what's wrong with it being like it is ? EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.
  • deleted 4y ago
    [deleted]
  • Hinrik 4y ago
    Like the commenter alluded to, it allows accurately constrainted type-ahead. If a query starts with "SELECT FROM my_table " and expects one or more column names at that point, your IDE can already suggest the column names from my_table (and not from any other table).
  • cogman10 4y ago
    Imagine a table `Foo` with the schema `FooId, name, date, favorite_color, active` Now, you want to pull the ID for the latest `foo` for a specific date, but you don't know any of the column names. The modern workflow looks like this You write `SELECT * FROM Foo` then you say, "Ok, now I can get autocomplete" `SELECT FooId FROM Foo` "Ok, now I can write the where clause" `Select FooId From Foo WHERE date=?` It becomes an exercise in moving the cursor around just to get the autocomplete going. If you are really familiar with the schema, not a problem. But if you just remember a few details about it, then you are stuck in this weird back and forth cursor moving thing. That's why it'd be more ergonomic to have something like `From Foo Select FooId where date=?` Because you never need to move your cursor and you could get all the autocomplete you need at the right times. This becomes especially true when writing joining statements FROM Foo f JOIN Bar b ON f.FooId = b.FooId SELECT f.FooId WHERE b.active = 1
  • spprashant 4y ago
    If you exploring a set of tables you have never touched before, it really neat if you could just type in: FROM tablename t SELECT t.<press tab> and some form of autocomplete mechanism, either prefills all the column names from table "t" or suggests the list of columns and/or types associated with it. This is much better than having to: 1. Run a SELECT with LIMIT statement just to get an idea of the layout. 2. Point and click through the IDE treeview. Honestly, I don't think it helps a whole lot beyond this functionality, but I can see why folks who are accustomed to thinking in functional pipelines (from -> select -> map -> filter -> collect) can prefer this way of querying. I think PRQL is one attempt at building something this way.[1] [1]. https://github.com/prql/prql https://github.com/prql/prql
    • noirscape 4y ago
      DESCRIBE TABLE is a command that pretty much does exactly this (explain what a table contains) and it's a part of MySQL. If you use PostgreSQL then you can use \d instead. I'm sure the other RDBMSes have their own equivalent (except for maybe SQLite but if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong).
      • ldldldk 4y ago
        SQLite absolutely has production level applications, it's much more than a toy. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
      • john61 4y ago
        in sqlite3 it is .schema
      • The_Colonel 4y ago
        It's kind of funny to see a claim that the most widely deployed database in the world is useful only for toy projects. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
        • 4y ago
  • baq 4y ago
    you seem to be too used to it to notice... you start to type SELECT some, columns and can't get autocomplete until you add FROM afterwards, so you either type the query inside out (SELECT FROM table and go back to after SELECT) or just give up.
  • alexvoda 4y ago
    As sibling comments have mentioned, this is a valid complaint. It is useful to remember that SQL is an old language already, and there are plenty of warts that in all of this time have been observed. The same way C is old, and there are things to be apreciated about fresh attempts like Zig, Rust, Swift, Nim.
  • emaginniss 4y ago
    Everyone else is mentioning it from an IDE perspective, but let's also think about logically from a language perspective. When you start a FROM clause and add some JOINs, a few WHERE conditions and maybe GROUP BY, you are building a virtual view of a series of tables, columns, and aggregations. You could even define this data set as an ephemeral table. What you do with that data set afterwards might vary depending on the need, but the data set might not change. Depending on the application, you might select different columns from the data set. We do this naturally using a WITH clause at the beginning of a query. WITH (combine a whole bunch of stuff) as dataset SELECT a, b, c FROM dataset This approach just says: FROM tables... WHERE ... SELECT a, b, c To me, it does make a lot of sense. This is also the paradigm that some of the graph databases use.
    • SideburnsOfDoom 4y ago
      Agreed, it is not just an "IDE related thing", it is also a logical arrangement of thoughts thing, a readability thing.
      • kaba0 4y ago
        And as another commenter noted, the underlying relational algebra is also not in agreement, so it is definitely not logical. I believe they wanted to mimic human language statements, but that goal hurt more than it helped.
    • spullara 4y ago
      It makes way more sense honestly. It also looks a lot like a functional pipeline if you set it up that way.
    • tremon 4y ago
      You could even define this data set as an ephemeral table Exactly! This is where SQL hurts me the most: not being able to store (partial) query expressions in variables for later reuse. The only way to do this is by creating explicit views (requires DDL permissions) or executing the partial query into a temporary table (which is woefully inefficient for obvious reasons).
  • mamcx 4y ago
    > I now understand that it is an IDE related thing not something fundamental to the language. No, is fundamental issue to the language! The relational model is clear. You START with a relation and then compose with relational operators that return relations. ie: rel | project Sql do it weird. Is like in OO, where instead of define a class THEN define the properties, you define the properties THEN define the class. And this fundamental issue with the language goes deeper. The rules are ad-hoc for each sub-operator despite the fact using relational model MUST make it simply to compose. So, you have rules for HAVING, GROUP BY, ORDER BY, WHERE, SELECT and so on and none are like the others, are different in small but annoy ways...
    • wwweston 4y ago
      Having learned Prolog before SQL, it was weird when it clicked that both were relational languages, but SQL decided to hide that underneath a natural language facade and the inconsistencies that come with it.
    • jolmg 4y ago
      Having SELECT come first makes sense to me because it's the only part of the statement that's required. FROM and everything else is optional. Also when reading a statement, you're mostly interested in what the returned fields are rather details like where they came from or how they're ordered. It kind of makes sense to put it at the start. Maybe other syntax forms have their benefits, specially when writing, but I don't think SQL's choice is completely senseless either.
      • tadfisher 4y ago
        SELECT itself should be optional. Languages with expressions are fairly intuitive, e.g. "int x = foo.bar;" where "foo.bar" is equivalent to the "SELECT bar FROM foo;" SQL statement. I don't breathe SQL every day, so I'm struggling to come up with a case where removing SELECT results in parsing ambiguity.
        • jolmg 4y ago
          You mean the keyword; I meant the clause. "FROM foo" is optional to the syntax.
  • isitmadeofglass 4y ago
    > thing not something fundamental to the language. It is fundamental to the language. The evaluation order is from,where,group by, having, select, order by, limit. Everything in perfect order is, select except.
• taeric 4y ago
  You could improve type ahead by just pouring effort into the IDE. Especially with the amount of resources we have nowadays. Easy enough to have basic typeahead on basically all possible columns when writing the select, and then you could use the columns as a filter on the tables during auto complete. In general, this isn't done. But I don't see any technical reason it can't be done.
  • chrsig 4y ago
    This strikes me as solving the wrong problem. It's not as useful to be able to derive a table from selected columns -- the desire is to complete columns from a table. It's both more intuitive and helps the user more than once.
    • taeric 4y ago
      I disagree. Folks often know what column they want, and have to find the table that best gives it.
    • deleted 4y ago
      [deleted]
  • refactor_master 4y ago
    If you have multiple tables, how do you avoid suggesting a wrong column, before filling out the table name? You could suggest all columns up front, and then afterwards tell the user “this suggestion doesn’t exist”, which would just erode trust in the autocomplete.
    • kaba0 4y ago
      Start typing “SEL”, ide suggests “SELECT _ FROM”, you press enter and the above text is entered, while the cursor is placed after from. You write that part of the query and after pressing enter it will jump back to the select part. This is done already by Jetbrain’s datagrip for example.
    • taeric 4y ago
• mmcdermott 4y ago
  That's a fair point. This comment made me realize my own tendency to write "select * from..." so that I could supply the "from" before going back and replacing "*" with specific columns.
• crazysim 4y ago
  I don't know why my memory jogged to the old ruby on rails screencasts but I suppose there's nothing stopping IDEs from jumping to the FROM section of a query and then returning you back to the SELECT in some sort of "macro"/snippet. It's a hack, I guess.
  • kaba0 4y ago
    That’s exactly what Jetbrain’s Datagrip does.
    • mordechai9000 4y ago
      From what I've seen, it will initially populate autocomplete column name values from all the introspected tables on the current schema search_path, even before the FROM clause is added. Usually this is good enough for me, so I don't think about it. I mostly interact with postgres.
• guenthert 4y ago
  We have transactions to prevent mishaps, no?
• d0mine 4y ago
  Configure your IDE, to help you write SELECT queries. It should be easy to insert a template with jump points in the desired order e.g., a generic YASnippet in Emacs should be able to do it. Thus by the time you are writing the column expression after the SELECT, the FROM table expression would be filled already (enabling scope-dependent column completion).
• vivegi 4y ago
  Several decades ago while using Oracle and SQL*Plus, I used to generate the column list for queries from the data dictionary table (ALL_TAB_COLUMNS). Once I learnt that trick, I never typed the column list ever again. Eventually, I had a library of queries for common tasks. You could use that trick with almost all database engines. IDEs were never a favorite (they were quite limited then). Today, the situation is a lot better.
• Koshkin 4y ago
  FWIW in LINQ, Select comes after From and Where.
  • sedatk 4y ago
    Yeah, Select comes last, and it makes the most sense.
  • jiggawatts 4y ago
    Also in Kusto Query Language (KQL), which is used extensively in Azure.
• deleted 4y ago
  [deleted]
• stickfigure 4y ago
  I started my career in the 90s writing ROLAP engines, and even though I've spent most of my time since doing "web" development, I still seem to end up having to build engines that generate SQL queries that are dozens of lines long. The complaints about SQL composability are real. The grammar is fundamentally pretty irregular. Acceptable for humans to type ad-hoc, crappy for computers to generate. You can like what SQL does for you (I do!) but it's also easy to imagine something a bit better. I thought the article was spot-on. I hope some future SQL x.0 will fix these issues, but also be similar enough to present-day SQL that I don't have to learn a whole new language from scratch.
  • randomdata 4y ago
    > I hope some future SQL x.0 will fix these issues, but also be similar enough to present-day SQL that I don't have to learn a whole new language from scratch. If SQL is to Javascript then something akin to WASM would be nice. Lower level primitives that allow query systems to be built. SQL could be one such system, but not limited to.
• mordechai9000 4y ago
  Maybe we should treat SQL like JavaScript, and use it as a compiler target instead of coding in it directly. /s
  • ilkhan4 4y ago
    I mean, that's pretty much what ORMs do, right? Hasura et al too.
    • mordechai9000 4y ago
      Yes, this is true. Although my experience with ORMs is that there is always a reason to use some kind of escape hatch to run raw SQL directly.
      • scrozart 4y ago
        Agreed. After spending the better part of a decade watching Hibernate a) fail to scale or b) get confused beyond a third level of association, I find myself writing bare SQL and/or refactoring to implement the repository model. ORMs are great for prototyping or if your data model is simple; beyond that you're better off without them.
      • randomdata 4y ago
        Most higher level regular programming languages will allow you to escape to C, and C allows you to escape to assembly, so that's not unusual. In my experience when you need to escape to raw SQL there is still some kind of query builder employed, which too becomes another "compile to SQL" source.
  • dagss 4y ago
    Not sure why you added /s; .. SQL is in many ways exactly lile JavaScript. The only way to run your code where it needs to run, but with some things to be desired language wise. I wish more languages compiling to SQL were more common. (Not ORMs though they just miss the point entirely..)
  • qorrect 4y ago
• BeefWellington 4y ago
  I think this stems from a misunderstanding of the entire point of SQL. It isn't about looking at data in an individual table, it's about retrieving a Result Set for complex queries. All the FROM-first examples I've ever seen are almost universally the simplest query in the world where autocomplete is not a large hurdle anyways because you aren't even bothering with table aliasing. As soon as you do anything even moderately complex (multiple joins, subqueries, calculations, etc.) the advantage of putting FROM first vanishes, and if you're adding a table alias or hard reference to the table you can already see what is in the SELECT list AND in many cases get autocomplete.
  • goto11 4y ago
    Joins would presumably still be in the from-clause, so you get: FROM a JOIN b JOIN c SELECT b._ Here aoutocomplete helps also with the joined tables. Autocomplete would also help in subqueries: FROM a JOIN (FROM b WHERE b._
  • isitmadeofglass 4y ago
    > I think this stems from a misunderstanding of the entire point of SQL. It isn't about looking at data in an individual table, it's about retrieving a Result Set for complex queries. No misunderstanding at all. It’s just more natural to first describe what the sources of the data are, joins etc, and then afterwards which columns you’d like to retrieve, or what calculations you’d like to perform etc. The current way of having select first is just plain dumb. When it comes to reading order, you never actually know what is selected until you’ve read through the from, where, group by and having clauses anyway, so you constantly have to jump back and forth between dart and end to see the context. And it also better matches the sql evaluation order to have from first and select is after all of these. Select also just better fits with order_by and limit since it’s also about restricting results after you’ve gather them all up and stitched them together etc.
    • polishdude20 4y ago
      I feel this also follows the concept of narrowing down from a large set to a smaller set. Using FROM first is the large set and then you're selecting from it.
• tehlike 4y ago
  Check this out https://github.com/prql/prql https://github.com/prql/prql
  • qorrect 4y ago
    Love PRQL :D
  • lostdog 4y ago
    Wow, that looks incredible. It looks like you can pretty much refine your query line by line and see the intermediate results to know what you're working with.
• deleted 4y ago
  [deleted]
• camgunz 4y ago
  You can just put SELECT * FROM stuff and then edit your columns later.
• SnowHill9902 4y ago
  If you don’t wrap your UPDATEs in a transaction you are doing it wrong.
• aspyct 4y ago
  Agreed. I would also love to be able to specify the WHERE just after the FROM but before the JOINs. And also after them: - the first one to filter the rows before joining - the second one to filter after joining
• v0idzer0 4y ago
  Just a few years ago Javascript was pretty awful, so people started writing transpilers (most famously Babel) to add features like arrow functions, async/await, decorators, constants, etc. Typescript is another transpiler that greatly improves the language but outputs regular old, awful javascript. Many of these features even ended up being built into the language. I wonder if it’s time for a transpiler renaissance in SQL? It would be trivial to convert “select id from users” into “from users select id”. I’m sure theres a lot of other cool feautures/sugar you could add while still outputting valid SQL.