8 ms·

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 com

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
    - bot41 4y ago
      sheesh lets hope there are no vulnerabilities in there
      - colejohnson66 4y ago
        SQLite has an 608 times more lines of code for tests than the original code![0] I’d wager it’s the highest test/production ratio there is. [0]: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
        The_Colonel 4y ago
        Most of those tests are generated. It's wrong to focus on this metric IMO.
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.

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
  - bot41 4y ago
    sheesh lets hope there are no vulnerabilities in there
    - colejohnson66 4y ago
      SQLite has an 608 times more lines of code for tests than the original code![0] I’d wager it’s the highest test/production ratio there is. [0]: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
      - The_Colonel 4y ago
        Most of those tests are generated. It's wrong to focus on this metric IMO.

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.