Check out the DuckDB community extensions:

[0]: https://duckdb.org/community_extensions/extensions/psql.html

[1]: https://duckdb.org/community_extensions/extensions/prql.html

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

duckdb -ui

More here: https://duckdb.org/2025/03/12/duckdb-ui.html

Now it is supported by DuckDB, BigQuery, Spark SQL, Firebolt, and probably others. I use it more and more (with BigQuery).

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).

  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()

>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>`.

A common misconception (that SQL is a realization of RA instead of barely based on it).

In RA, is in fact `Relation > Operator`

https://cs186berkeley.net/notes/note6/

https://web.wlu.ca/science/physcomp/ikotsireas/CP465/W1-Intr...

https://home.adelphi.edu/~siegfried/cs443/443l9.pdf

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.

[0]: https://github.com/postgres/postgres/tree/master/src/backend...

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.

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.

No one was confused what you were doing.

I'm saying the historical reason for why it is the way it is, doesn't matter. I would hope we design our languages to be maximally clear and useful, not to be maximally full of historical cruft.

There's no accounting for taste, so you're welcome to like whatever you like, but I don't think you liking a backward syntax is particularly persuasive. It sounds more like you're just used to it than that you see any actual benefits to it.

It's inspired by a mish-mash of both relational algebra and relational calculus, but the reason why SELECT comes first is because authors wanted it to read like English (it was originally called Structured English Query Language).

You can write the relational algebra operators in any order you want to get the result you want.

Ultimately, yes, you can express relational algebra in any notation that gets the point across, but the parent is right that

   π₁(R)

   (R)π₁

Π(σ(R)) instead of σ(Π(R))

and not about whether relational algebra uses prefix or postfix notation:

Π(σ(R)) vs. R > σ > Π

SQL's WHERE statement (and others) works totally differently from SELECT in that regard, so it doesn't make much sense to say that "SELECT comes first because relational algebra".

I don’t think I see what you see. From 3.3:

    RANGE PART P
    GET W (P.P#,P.PNAME,P.QOH):(P.QOH<25)

   SELECT p.pnum, p.pname, p.qoh FROM part p WHERE p.qoh < 25

   FROM part p SELECT p.pnum, p.pname, p.qoh WHERE p.qoh < 25

   FROM part p; SELECT p.pnum, p.pname, p.qoh WHERE p.qoh < 25

More likely because this order is closer to typical English sentence structure. SQL was designed to look like English, not relational algebra.

Except this works in most major vendor SQL implementations. And they all support relation aliases in SELECT... Seems the standards have long fell behind actual implementations.

    SELECT id AS foo
    FROM MyTable
    WHERE foo = 1;

    SELECT id
    FROM MyTable
    WHERE id = MAX(id);

You can of course alias columns for SELECT however you’d like, and can also use those aliases in ORDER BY. You can also trivially refactor the above examples to use subqueries or CTEs to accomplish the goal.

    SELECT LOWER(Name) as LName FROM Product where LName like '%frame%';

You can also use "correlated column aliases" (I can't recall the proper name) i.e.

    SELECT
        id AS foo,
        foo || '_1' as foo_n,
        right(foo_n, 1) as foo_ordinal
    FROM MyTable
    WHERE foo = 1;

Don't blame the math for sloppy implementation. Nothing in the math suggest where aliases should be specified. Don't get me wrong it is a coinvent, logical place to put them, but as you say it rather limits their use, and could have been done better.

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.

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;

It would be equally declarative if FROM came first.

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.

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

You want the DB to first run SELECT * FROM <table>, and then start operating on that?

It's about how humans think about it, not about how the computer executes it.

  WHERE sum(d) > 100 -- the above with having sum(d) > 100

SQL has a conceptual issue with repeating group by clauses, so maybe not that one (or maybe we should fix the conceptual issues). But any other, including the limiting and offset ones.

BigQuery SQL and Spark SQL (and probably some others) have adopted pipelined syntax, DuckDB SQL simply allows you to write the query FROM-first.

Now we need to get the ANSI SQL committee to standardize it ANSI SQL 2027 or some such.

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.

I don't think it is tough to describe many many reports of data that you would want in this way. Is it enough for you to flat out get the answer? No, of course not. But nor is it enough to just start all queries at what possible tables you could use as a starting point.

Your example is also not a complete select statement, you would need to go back and add the actual aggregate functions, and oops the zip_code column was actually called zip, so we need to remap that as well. You can almost never finish a select statement before you have inspected the tables, so why not just start there immediately?

And to be clear, I'm all for complaining about the order of a select statement, to a very large degree. It is done in such a way that you have to jump back and forth as you are constructing the full query. That can feel a bit painful.

However, I don't know that it is just the SELECT before FROM that causes this jumping back and forth and fully expect that you would jump around a fair bit even with the FROM first. More, if I am ever reworking a query that the system is running, I treat the SELECT as the contract to the application, not the FROM.

There is a bit of "you should know the database before you can expect to send off a good query", but that really cuts to any side of this debate? How do you know the tables you want so well, but you don't know the columns you are going to ask for?

Simply because there are a lot more columns than there are tables.

Of course, I sometimes forget the exact table name as well. However, this is mostly not an issue as the IDE knows all table names before anything has been entered. By simply entering `FROM user`, the autocomplete will list all tables that contain `user`, and I can complete it from there. I cannot do the same with the column selection unless I first write the FROM part. And even if I do know exactly which columns and tables I want I would still want autocomplete to work when creating the SELECT statement. Rather than typing `zip_code`, I can most likely just type `z<TAB>`.

That is why 99% of my select queries start as `SELECT * FROM mytable`, and then I go back to fill in the select statement. And it's not just me, all colleagues I've worked with does exact same thing.

For larger, more complicated queries, you'll have to go back and forth a lot as you join tables together, that is unavoidable, but 80-90% of my queries could be finished in one go if the FROM part came first.

I actually seem to recall this was done in some of the early dedicated SQL tools. It is rather amusing how much we handicap ourselves by not using some things older tools built.

Maybe in this case you know the same naming conventions are enforced across all tables. But in general it’s difficult to know the exact column name without looking it up first.

I think you're missing the point; you start off with your goal, regardless of the column name. No one has the goal "we want columsn from this specific table", it's always "we want these columns in the final output".

No. The goal is to have an output column $FOO, where $FOO is meaningful and might not even be in the database in the first place.

> my goal is not to select created_at, my goal is to select Foo's created_at.

Then your goal is to get a value out of a specific table, namely `Foo`, presumably because the end-user wants to see some value in the results.

The end-user getting the value has neither an interest in nor knowledge of your schema. `Foo.created_at` is no more meaningful in the result sets than an unadorned `created_at`.

For this specific example, the end-user might want a column in the output called `age` (if it came from an inventory table`, or `duration` (if it came from a metrics tables), or perhaps `expiry` (if it is a table containing perishable stock). These are all a column in a Foo table, but the request that caused you to write the query in the first place does not mention table at all.

You get their requirement as either `age`, `duration`, `expiry` or similar. That is what you are starting from: "a need for a specific piece of data". You are not starting from "a need from a specific table", because the information needed is coming from someone who neither knows nor cares what your schema looks like.

Even C when `property` is computed rather than materialized. And CLOS even uses that syntax for method invocation.

The only language I can think of that uses field(record) syntax for record field access is AT&T-syntax assembly.

name(object) makes as much sense as object.name

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.

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

Is like "to control mutation I only need to append "mut" to the name!

Even in your example, first and last could refer to student or teacher. But presumably you know you're looking for student data before knowing the exact columns.

Like, how would you send this question to someone? Or how would you expect it to be sent to you? If your boss doesn't tell you from what table they want some data, do you just not answer?

And sure, there could be ambiguities here. But these are not really fixed by listing the sources first? You would almost certainly need to augment the select to give the columns names that disambiguate for their uses. For example, when you want the teacher and the student names, both.

And this is ignoring more complications you get from normalized data that is just flat out hard to deal with. I want a teacher/student combination, but only for this specific class and term, as an example. Whether you start the from at the student, the class, or the term rosters feels somewhat immaterial to how you want to pull all of that data together for why ever you are querying it.

If they ask for "address for a customer" I can go to the customer table and look up what FKs are relevant and collect all possible data and then narrow down from there.

Realistically, I strongly suspect you could take this argument either direction. If you have someone making a query where they are having to ask "what all tables could I start from?" you are in for some pain. Often the same data is reachable from many tables, and you almost certainly have reasons for taking certain paths to get to it. Similarly, if they should want "person_name", heaven help them.

Such that, can you contrive scenarios where it makes sense to start the query from the from clause? Sure. My point is more that you almost certainly have the entire query conceptualized in your mind as you start. You might not remember all of the details on how some things are named, but the overall picture is there. Question then comes down to if one way is more efficient than the other? I have some caveats that this is really a thing hindered by the order of the query. We don't have data, of course, and are arguing based on some ideas that we have brought with us.

So, would I be upset if the order was reversed? Not at all. I just don't expect that would actually help much. My memory is using query builders in the past where I would search for "all tables that have customer_id and order_id in them" and then, "which table has customer_id and customer_address" and then... It was rarely (ever?) the name of the table that helped me know which one to use. Rather, I needed the ones with the columns I was interested in.

SELECT statments don't just use table names, they can use aliases for those table names, views, subqueries, etc.

The FROM / JOIN blocks are where the structure of the data your are selecting from is defined. You should not assume you understand what a SELECT statement means until you have read those blocks.

I can define the return format of my query in the SELECT statement, then adapt the data structure in the FROM block using subselects, aliases etc — all to give me the shape desired for the query.

If you've ever done complex querying with SQL, you'd know that you'd go back and forth on all parts of the query to get it right unless you knew the relations by heart, regardless of the order (sometimes you'll have to rework the FROM because you changed the SELECT is the point).

You can, but one of those ways is objectively worse for the reasons explained in this thread and in the article.

When you have to read a query out of order to understand it then something is wrong with the structure of the query language.

  select name, min(price) from product join product_price on product.id = product_price.product_id

The more one uses SQL to filter for, the more you'll think about what you want to achieve vs how (which comes after): in a sense, the SELECT is your function return type declaration, and I frequently start my functions with the declaration.

Sure, and pretty much every time the names I wrote up were not the ones in the table so that was a complete waste of time.

> the SELECT is your function return type declaration

That might be true if “select” only contained aliases, bur that’s not the case at all, so what it is is complete nonsense.

My argument would be largely that you are doing a search of all of the tables for the columns that you want. With having to know the way to join the necessary tables along the way.

And I want to be clear, I don't think this is necessarily "the way." I more think that it is almost certainly an iterative process that can be started at either place, and will require bouncing between the two quite often.

For instance, yes, you can autocomplete column names from tables that you have put in the from. What if you can't find the column you are anticipating there? Go back and rescan the table names hoping you can guess which table should have the column you want? Then go back to autocomplete for columns to see if expected value appears? Or go to a bit more of a global search for columns?

Either way should be fine.

I usually start with: ``` select * from <table> as <alias> limit 5 ```

FWIW that is the approach used by LINQ.

FROM ... SELECT ... WHERE ...: Q S L

  > The order should be FROM -> SELECT -> WHERE, since SELECT commonly gives names to columns, which WHERE will reference.