13 ms·

SQL shows it's age by having exactly the same problem. Queries should start by the `FROM` clause, that way which entities are involved can be quickly resolved

• layer8 1y ago
  While I agree, some SQL editors do provide code completion for the SELECT clause if you type the FROM clause first.
  • pjmlp 1y ago
    That is my trick as well, but feels backwards, by now there could be a variant of SQL supporting FROM first.
• folkrav 1y ago
  I legitimately never even thought of this, and it feels very obvious in retrospect…
• pzmarzly 1y ago
  You may find PRQL interesting. https://prql-lang.org/ https://prql-lang.org/
• dleeftink 1y ago
  PSQL (and PRQL) use this ordering, and a similar pipe/arrow notation has recently been added to BigQuery. Check out the DuckDB community extensions: [0]: https://duckdb.org/community_extensions/extensions/psql.html https://duckdb.org/community_extensions/extensions/psql.html [1]: https://duckdb.org/community_extensions/extensions/prql.html https://duckdb.org/community_extensions/extensions/prql.html
  • geysersam 1y ago
    In duckdb you can also start with `FROM .. SELECT ..` without using the PSQL extension. But I haven't found a good editor plugin that is actually able to use that information to do completions :/ If anyone knows I'd be happy to hear it
    • howardbannister 1y ago
      The duckdb local ui does that I believe: duckdb -ui More here: https://duckdb.org/2025/03/12/duckdb-ui.html https://duckdb.org/2025/03/12/duckdb-ui.html
  • paradox460 1y ago
    And in elixir land we've had similar pipes and composition through ecto, for the major rdbms
  • account42 1y ago
    An unfortunate name when PSQL is already a commonly used shorthand (including the official client command name) for a competing SQL-based database engine.
  • mikeen 1y ago
    Google actually started pipe syntax, https://news.ycombinator.com/item?id=41338877 https://news.ycombinator.com/item?id=41338877 Now it is supported by DuckDB, BigQuery, Spark SQL, Firebolt, and probably others. I use it more and more (with BigQuery).
• Arnavion 1y ago
  Yes, C#'s DSL that compiles to SQL (LINQ-to-SQL) does the same thing, `from` before the other clauses, for the same reason that it allows the IDE code completion to offer fields while typing the other clauses.
• shadowgovt 1y ago
  It's true. I call authoring SQL "holographic programming" because any change I want to make frequently implies a change to the top and bottom of the query too; there's almost never a localized change. C++ has this issue too due to the split between header declarations and implementations. Change a function name? You're updating it in the implementation file, and the header file, and then you can start wondering if there are callers that need to be updated also. Then you add in templates and the situation becomes even more fun (does this code live in a .cc file? An .h file? Oh, is your firm one of the ones that does .hh files and/or .hpp files also? Have fun with that).
• cultofmetatron 1y ago
  ecto's sql dsl basicly fixes this. here's one I just wrote in my codebase from(l in Blinq.Reservations.OrderCycleLinks) |> join(:left, [l], r in Blinq.Reservations.Reservation, on: l.reservation_id == r.id) |> select([l, r], %{ order_cycle_id: l.order_cycle_id, customer_id: r.customer_id }) |> where([l, r], l.order_cycle_id in ^order_cycle_ids and r.assignment_type == :TABLE) |> Repo.all()
• sgarland 1y ago
  It's written that way because it stems from relational algebra, in which the projection is typically (always?) written first. >The order should be FROM -> SELECT -> WHERE, since SELECT commonly gives names to columns, which WHERE will reference. Per the SQL standard, you can't use column aliases in WHERE clauses, because the selection (again, relational algebra) occurs before the projection. > You could even avoid crap like `SELECT * FROM table`, and just write `FROM table` and have the select clause implied. Tbf, in MySQL 8 you can use `TABLE <table>`, which is an alias for `SELECT * FROM <table>`.
  • kerkeslager 1y ago
    > It's written that way because it stems from relational algebra, in which the projection is typically (always?) written first. Okay, so what? We're not obligated to emulate the notational norms of our source material, and it is often bad to do so when context changes.
    • sgarland 1y ago
      I was explaining why it is the way that it is. If you'd like your own version of a parser, here's Postgres' [0]. Personally, I really like SQL's syntax and find that it makes sense when reading it. [0]: https://github.com/postgres/postgres/tree/master/src/backend/parser https://github.com/postgres/postgres/tree/master/src/backend...
      • jagged-chisel 1y ago
        There was not argument about how much sense it makes. There was an argument for improving readability by placing the table names first. Lots of people “like” things because they are familiar with them. And that’s a fine enough reason. But if you step out of your zone of familiarity, can you find improvements? Are you willing to forgo any prejudice you may possess to evaluate other suggestions? Just a little willingness to see another perspective is all anyone asks.
      • 9rx 1y ago
        > If you'd like your own version of a parser, here's Postgres' [0]. Funnily enough, if you pull up a version of Postgres' parser prior to the 1995 release, you'll find that it puts the relation first.
• pjmlp 1y ago
  Kusto, the Azure query language for data analysis uses that form with piping as well. https://learn.microsoft.com/en-us/kusto/query/?view=microsoft-fabric https://learn.microsoft.com/en-us/kusto/query/?view=microsof... Also the LINQ approach in .NET. I do agree, that is about time that SQL could have a variant starting with FROM, and it shouldn't be that hard to support that, it feels like unwillingness to improve the experience.
  • 0x00C0FFEE 1y ago
    I love working with KQL, it’s a very expressive query language.
  • benrutter 1y ago
    Kusto is so much vetter than it has any right to be! Normally I'd run a mile at a cloud provider specific programming language that can't ve used elsewhere, but it really is quite nice! (there are some wierd quirks, but a tonne less than I'd have thought)
• lackoftactics 1y ago
  This was a historical decision because SQL is a declarative language. I was confused for too long, I want to admit, about the SQL order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT As a self-taught developer, I didn't know what I was missing, but now the mechanics seem clear, and if somebody really needs to handle SELECT with given names, then he should probably use CTE: WITH src AS (SELECT * FROM sales), proj AS (SELECT customer_id, total_price AS total FROM src), filt AS (SELECT * FROM proj WHERE total > 100) SELECT * FROM filt;
  • balfirevic 1y ago
    > This was a historical decision because SQL is a declarative language It would be equally declarative if FROM came first.
    • deleted 1y ago
      [deleted]
    • lackoftactics 1y ago
      You are right to point this out, fair point
  • 9rx 1y ago
    > This was a historical decision because SQL is a declarative language. What do you mean? Both ALPHA (the godfather declarative database language created by Codd himself) and QUEL, both of which inspired SQL, put "FROM" first. Fun fact: SQL was originally known as SEQUEL, which was intended to be a wordplay on being a followup to QUEL. Another commenter makes a good case that SQL ended up that way because it was trying to capture relational algebra notation (i.e. π₁(R), where π~select, ₁~column, R~table). Yet relational algebra is procedural, not declarative. Relational calculus is the declarative branch. Although the most likely explanation remains simply that SEQUEL, in addition to being fun wordplay, also stood for Structured English Query Language. In English, we're more likely to say "select the bottle from the fridge", rather than "from the fridge, select the bottle". Neither form precludes declarative use.
• marcosdumay 1y ago
  The order should be starting on FROM, followed by any sequences of whatever clauses (except for FROM), always creating an intermediary result-set. FROM table -- equivalent to today's select * from table SELECT a, 1 as b, c, d -- equivalent to select ... from table WHERE a in (1, 2, 3) -- the above with the where GROUP BY c -- the above with the group by WHERE sum(d) > 100 -- the above with having sum(d) > 100 SELECT count(a distinct) qt_a, sum(b) as count, sum(d) total_d -- the above being a sub-query this selects from
  • sgarland 1y ago
    > always creating an intermediary result-set You want the DB to first run SELECT * FROM <table>, and then start operating on that?
    • dragonwriter 1y ago
      Presumably, the intermediate result sets would necessary be materialized, only logical.
    • kerkeslager 1y ago
      Obviously not, and bringing this up as if it's a gotcha just shows you aren't keeping up with the conversation. Try less to correct people and more to understand people. It's about how humans think about it, not about how the computer executes it.
      • sgarland 1y ago
        How else do you read what they wrote?
        • eyelidlessness 1y ago
          I read it as describing their preferred mental model for declaring a result set, which is different from describing their preferred behavior to produce it. This seems clear to me in wording and context; it’s also broadly consistent with how SQL is understood, and how it is generally implemented.
• 0x696C6961 1y ago
  It doesn't seem like it would be all that difficult to allow that form in a backwards compatible way. Why hasn't this happened?
  • orthoxerox 1y ago
    Because SQL is a whole language family of separate languages grudgingly adopting new features introduced by the notoriously slow standartization committee. BigQuery SQL and Spark SQL (and probably some others) have adopted pipelined syntax, DuckDB SQL simply allows you to write the query FROM-first.
  • juancn 1y ago
    Not sure. It seems a fairly basic syntax change and it could even be easily made backwards compatible. Now we need to get the ANSI SQL committee to standardize it ANSI SQL 2027 or some such.
• taeric 1y ago
  My main caveat here, is that often the person starting a select knows what they want to select before they know where to select it from. To that end, having autocomplete for the sources of columns is far far more useful than autocomplete for columns from a source. I will also hazard a guess that the total number of columns most people would need autocomplete for are rather limited? Such that you can almost certainly just tab complete for all columns, if that is what you really want/need. The few of us that are working with large databases probably have a set of views that should encompass most of what we would reasonably be able to get from the database in a query.
  • Zedseayou 1y ago
    Can you help me understand this situation? Almost always what I want to select is downstream of what I am selecting from, I can't write anything in SELECT until i understand the structure/columns available in FROM.
    • taeric 1y ago
      "I want 'first_name, last_name, and average(grade)' for my report." That is easy enough to state without knowing anything about how the data is normalized. Back when I worked to support a data science team, I actually remember taking some of their queries and stripping everything but the select so that I could see what they were trying to do and I could add in the correct parts of the rest.
      • mamcx 1y ago
        Even assuming that this is plausible way a user could think about it (that can be understood) it shows why is bad Think: I have 20 tables with the column `id` "I want 'id,id,id'" is bad UX, and is what here is being argued, then when the syntax guide you: "I want 'FROM a: id'" is better
        • taeric 1y ago
          I don't think anyone would ever say they want "id, id, id"? They would say they want "customer_id, order_id, item_id" or some such. You would then probably say, "we just use 'id' for the id of every item..." but many places that I've worked actually explicitly didn't do that for essentially this reason. Natural joins on "foo_id" "just work" if you don't give every table the same "id" column.
• delifue 1y ago
  A nitpick: select doesn't declare fields for where. select declare fields for having. it's the schema that declares fields that can be used in where.
• de6u99er 1y ago
  You can always start with the FROM clause and then add the SELECT clause above it if it's a out auto-,completion. I usually start with: ``` select * from <table> as <alias> limit 5 ```
• nurettin 1y ago
  > Queries should start by the `FROM` clause, FWIW that is the approach used by LINQ.
• sshine 1y ago
  SELECT ... FROM ... WHERE ...: S Q L FROM ... SELECT ... WHERE ...: Q S L
• Ultimatt 1y ago
  WITH and CTEs go some way to help with this, and the majority of queries are a lot better for that.
• benhurmarcel 1y ago
  DuckDB accepts just that: https://duckdb.org/docs/stable/sql/query_syntax/from.html#from-first-syntax https://duckdb.org/docs/stable/sql/query_syntax/from.html#fr...
  • qskousen 1y ago
    Off topic, but I really wish DuckDB's FTS extension could add to the index as the table is added to. It's the only thing keeping me on sqlite for a project.
• dotancohen 1y ago
  > The order should be FROM -> SELECT -> WHERE, since SELECT commonly gives names to columns, which WHERE will reference. Internally, most SQL engines actually process the clauses in the order FROM -> WHERE -> SELECT. This is why column aliases (defined in SELECT) work in the GROUP BY, HAVING and ORDER BY clauses, but not in the WHERE clause.
• ARandomerDude 1y ago
  I've always liked the `select...from` order because it helps me understand the goal before reading the logic. In other words, I want to end up with this, and here's how I want to go about getting it.