[toto444]

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.

[spprashant]

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

[andylynch]

In SSMS and almost certainly many other SQL editors Intellisense works on the select list as soon as you have a working from clause, the only caveat being you can’t autocomplete columns reading when writing a statement purely left to right.

But I think that’s not a big deal at all, and the SQL approach has a certain advantage in putting the type of action (select/update/delete/drop etc) front and centre, which is really quite helpful.

[tomjakubowski]

SELECT FROM widgets VALUES id, factory_origin_id, frobbable_knobs

[noirscape]

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

[jjav]

> except for maybe SQLite

.schema

> if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong

That's an uninformed statement. SQLite is extremely solid production quality code. Of course it's not the universally applicable database solution, nothing is. Sometimes you need Cassandra or its kind, often MySQL|Postgres, other times SQLite is the correct answer.

[The_Colonel]

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

[bmacho]

I guess there are more toy projects than serious ones.

[The_Colonel]

Yeah, I mean MacOS, Android, iOS, Chrome, Firefox ... all typical toy projects.

[bot41]

sheesh lets hope there are no vulnerabilities in there

[colejohnson66]

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

[The_Colonel]

Most of those tests are generated. It's wrong to focus on this metric IMO.

[matheusmoreira]

> if you're using SQLite outside of toy environments or hobby projects, you're doing it wrong

https://www.sqlite.org/mostdeployed.html

> Every Android device

> Every iPhone and iOS device

> Every Mac

> Every Windows 10 machine

> Every Firefox, Chrome, and Safari web browser

> Every instance of Skype

> Every instance of iTunes

> Every Dropbox client

> Every TurboTax and QuickBooks

> PHP and Python

> Most television sets and set-top cable boxes

> Most automotive multimedia systems

> Countless millions of other applications

Look at all those toys.

[emptyparadise]

everyone is doing it wrong :(

[ErwinSmout]

Not necessarily.

If you ponder the importance of proper (robust, reliable, dependable) data management for data that keeps nuclear plants going, for farmaceutical research data, for anything happening on the financial markets, for medical records, for data concerning payroll and the like, etc. etc. then you might appreciate that all the stuff mentioned in the list is indeed really "just toys".

[tremon]

You're missing the point: until you have typed the FROM TableName t in your IDE, SELECT t.<press tab> can not do autocompletion. That has nothing to do with whether SQL supports querying metadata (the ANSI portable way would be through INFORMATION_SCHEMA btw, not DESCRIBE). It's a consequence of the brain not responding to think-ahead queries by the IDE.

[__float]

This may be true, but you've missed the point that this is about aiding autocompletion while you're writing the query the first time.

[bombcar]

Couldn’t the IDEA understand

FROM table SELECT

And when done switch the code to be correct?

[andy81]

Every RDMBS has this, including SQLite.

[john61]

in sqlite3 it is .schema

[ldldldk]

SQLite absolutely has production level applications, it's much more than a toy.

https://www.sqlite.org/whentouse.html

[emaginniss]

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.

[tremon]

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

[briHass]

Isn't that what a common table expression is? Basically a pseudo temp-table to break down queries. Of course, they also allow recursion, which you can't do with a temp table.

[tremon]

Yes, but a common table expression is still bound to only one query. You can use it multiple times within the same query, but you still can't save a common table expression in a variable and re-use it in multiple queries.

This is what I'd what to do if common table expression really were common:

  SELECT c1, c2
  FROM DifficultJoinStructure
  AS myCte;

  WITH myCte
  SELECT c1, c2
  WHERE SomeCondition(c1);

  WITH myCte
  SELECT c1, c2
  WHERE SomeCondition(c2);

[TristanBall]

So.. a view then?

Granted most environments effectively treat views as DBA/Sysadmin owned objects, especially where end users/apps are effectively sharing one, or a small number, of user accounts.

But given user=schema aspect several of the traditional databases, I get the impression the original intent might have been a little more laissez fair?

Of course the same can be said for tables, and that was perhaps a little idealistic!

Views aren't always quite as composable as you'd like either, or maybe I'm just scarred by the particular DB engines I use most.

So I actually agree with you, but unfortunately SQL requires that the "WELL AKSHWELLY" be followed by one or more "BUT" clauses.

[SideburnsOfDoom]

Agreed, it is not just an "IDE related thing", it is also a logical arrangement of thoughts thing, a readability thing.

[kaba0]

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]

It makes way more sense honestly. It also looks a lot like a functional pipeline if you set it up that way.

[mamcx]

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

[jolmg]

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.

[mamcx]

> SELECT come first makes sense to me because it's the only part of the statement that's required.

Only *IN SQL*.

You don't need it on the relational model, heck, no even in any other paradigm:

    1

That is!. (aka: SELECT 1)

So this:

    SELECT * FROM foo

is because SQL is made weird. More correctly, this should be only:

    foo

Also, SELECT is not required all the time, you wanna do:

    foo WHERE .id = 1
    foo ORDER BY .id
    foo ORDER BY .id WHERE .id = 1 //Note this is not valid in SQL, but because SQL is wrong!

But you probably think this as weird, because SQL in his peculiar implementation, that is, ok for one-off, ad-hoc query, and in THAT case, having the list of fields first is not that bad.

But now, when you see it this way, you note how MUCH nicer and simpler it could have been, because then each "fragment" of a SQL query could become *composable*.

But not on SQL, where the only "composition" is string concatenation, that is bad as you get.

[tadfisher]

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.

[toto444]

> I'm struggling to come up with a case where removing SELECT results in parsing ambiguity

It's actually useful to the person reading the code. It clearly defines where a statement starts, what it does and makes reading a query close to reading English. Show a SELECT FROM WHERE query to someone who does not know SQL and the person will understand it. It might be a bit harder if you remove the SELECT.

[tremon]

It clearly defines where a statement starts

And this is another example of the ad-hoc problems of SQL: query terminators (;) are optional. If they weren't, there would be no abiguity where a statement would start: it's the first word after the previous terminator.

[mamcx]

> I'm struggling to come up with a case where removing SELECT results in parsing ambiguity.

Oh! Is super-ambiguous! Make the parser and enjoy it!

Lets make this more concrete:

     city SELECT id
     city ORDER id
     city id <-- Order or select???

You could then "favor" projection as the most important than the others. Ok, so:

     city city city

Which is the table, or the field?

[tadfisher]

I didn't suggest removing ORDER or FROM. This still makes sense:

    id FROM city ORDER BY id

A plus is that sub-selects have more natural, expression-like syntax:

    id FROM (city WHERE elevation > 1000)

[jodrellblank]

How do you know if you are SELECTing or DELETEing?

[jolmg]

You mean the keyword; I meant the clause. "FROM foo" is optional to the syntax.

[cmrdporcupine]

Select doesn't mean that tho. Not in the relational model. It refers to the filtering clause.

Following their stupid English syntax, but using the proper verbs it should rightfully be "PROJECT x, y FROM foo SELECT WHERE a = b"

[wwweston]

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.

[cmrdporcupine]

Yeah. And SQL does it very weird because it mixes up the meaning of "selection". Selection doesn't mean what most people seem to think it means. In the relational algebra "select" is really what is happening in the "where" clause. Projection is the name for the thing that chooses the attributes ("columns") to put in the final result.

[cogman10]

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

[Hinrik]

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

[alexvoda]

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.

[baq]

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.

[isitmadeofglass]

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