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