You need to state what you want (select a, b, c) before you tell it from where to get it (from). And no tooling can predict that.
So switching this, moving from and joins in front of select, might be everything needed to fix sql.
Step 1: build the dataset you want (FROM and JOIN) with all columns.
Step 2: filter out the rows you don't want (WHERE).
Step 3: choose which columns/values you want (SELECT).
Maybe it's just me but this model makes so much more sense to me! I'm sure not every developer has the same way of thinking, but it sure does seem more logical to me.
var result = from s in stringList where s.Contains("Tutorials") select s;
Linq query syntax is just dumb
I can't stand the non-extension-method Linq syntax: the _only_ place where it offers a readability improvement over ext-methods is using `join` - but I hardly ever do that in Linq anyway.
Also, in both my code and yours, `result` will be a lazy-evaluated `IEnumerable<String>` which may be undesirable - which means it's probably a good idea to use `.ToList()` to materialize it - which means having to use ext-methods anyway - and mixing both syntaxes in the same expression is aesthetically atrocious.
No love for let?
from item in items
let frob = Expenseive(item.P1)
where frob > 3
selec new { frob, item }...and ValueTuples are superior to Anonymous Types because you can actually return them from a function or use them as parameters - whereas Anonymous Types cannot cross method-call boundaries (excepting using generics for pass-through).
Anonymous Types in C# were a massive mistake. They should be [Obsolete]'d, IMO.
Hah! I tell the juniors this all the time :D. This seems to be basically the consensus among the C# community these days as far as I can tell as well.
Read: "you create new heap-allocated closures which wreck your Linq expression's runtime performance"
Or:
"you create Linq queries that cannot be translated into SQL"
——
On a related note, it’s interesting just how unpopular so many new C# language features are (just by looking at the numbers of Thumbs-down reactions on the GitHub Issues/PRs - stuff like top-level Main. It feels like C#’s LDT wants to be like Swift, but without Swift’s willingness to ditch ill-conceived features after a few years… but I think C# would be well-served by taking an axe to some language-features by now - like keyword-Linq and CLS-compliance (honestly, do any ISAs today still lack hardware support for unsigned ints?)
I think there are some otherwise-seldom-used linq overloads for doing just that. Maybe on Join() or GroupJoin() or something like that. Query expressions use them for compilation to keep closure use down, but I don't know too much about it.
Anyway, if top-level statements are wrong, I don't ever want to be right. I had no idea there was any controversy on that. It's trivial to add the boiler-plate back in if you want it.
The main thing that bugs me is that Expression<> is stuck with language features that existed in C#4, even when there are trivial lowerings. e.g. `is not null` could be `!= null`. (This example ignores operator implementations, but most IQueryables ignore more than that already) Even better, add AST node types for the new language features.
Top level statements and the new HostBuilders are fantastic, they're much cleaner and easier to follow than the previous mess. Add Minimal APIs to the mix and C# is finally a viable choice for spinning up something quickly.
I've been just showing that *approach* of starting SQL query from "from" part is viable, because that's how Microsoft timplemented in one of two LINQ "API"s"
I'm not trying to convince anyone to use LINQ's Query Syntax.
Linq is a (reasonably) compromised, non-referentially-transparent, implementation of a restricted form of relational-algebra with side-effects permitted, so I'm not comfortable describing Linq as "monadic".
Whereas Haskell's `do` is strictly monadic.
To me, this is a slight annoyance with an easy workaround. If the SQL spec gets updated to allow switching the clauses around, I'll be pleased. But I'm not about to change languages over it.
Is it ideal? No. But it's muscle memory now, so...
¯\_(ツ)_/¯
So, I had to move to VS Code for Vue and Svelte and as I didn't want to use 2 different IDEs I canceled my JB subscription. So far VS Code is pretty good for Goland and Rust as well, but as you said, I struggle to find something as good as DataGrip for SQL. Maybe I should just get a DataGrip license (single tool).
Edit: I forgot another issue ... DataGrip didn't support the latest SQLite version for a very long time, as the maintainer for the open source Java client wasn't working on it. When I dared to ask the JB support a second time for the status after a while, I got a stroppy reply from the responsible Jetbrains engineer, as how I dare asking twice and there's nothing Jebrains could do when the single person maintainer of an open source lib is doing other stuff with his life. Crazy.
Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.
An experienced person won't do that. For any moderately complex SQL query, before writing it I already have in mind the several jointures I'll need, since I usually know the tables and FK I'm working with. It's like following the edges of a graph, all in my head. But I don't know all the fields of these tables, so I rarely write their names from memory. So I have to write "SELECT 1 FROM …" and then go back to that "1" once my FROM is complete. That's not the end of the world, but it does smell.
I’m very experienced and do that kind of thing all the time.
Also, your “SELECT 1” technique seems like practically the same thing, except I guess you discover column names from autocomplete tooling rather than query output. (To my mind the difference is inconsequential.)
I typically keep all JOINs I've ever used on that schema in a single file, one per line.
Before writing a new query I can just copy paste some JOINs, simply skimming through table names like lego bricks.
That way it's surprisingly easy to beam from domain problem to a new query that uses 10 or 20 tables.
I've just realized that it might be all archaic now, in LLM era.
You have a library of useful joins that you've checked for correctness. Saving the library as reusable code would be even more useful.
But if you mean any other kind of experience or expertise, you are wrong. Those do not correlate with how a person assembles a query.
What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry.
My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.
I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language.
We're migrating from Sybase SQLAnywhere to MSSQL, and not being able to use aliases in WHERE, GROUP BY and ORDER BY is such a pain in the behind.
Almost all our non-trivial queries have to be nested multiple levels due to this, which doesn't exactly help readability.
My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance.
> The three window functions will also share the data layout, which will improve performance.
https://duckdb.org/docs/sql/window_functions#window-clauses
I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.
Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.
It's been an alternative in Postgres for decades.
TABLE foo
LIMIT 100;
d3w (`d`elete `3 w`ords) cannot be highlighted / indicated in any way ahead of time. If the motion specifier came first, it could be.
A good mindset to have in regards to Vim is, "Vim can do anything, even make you coffee". The trick with Vim is actually figuring out /how/ to do it.
I highly recommend you start with the VimCasts[1] video series. They're short, 5 minute, videos. With each covering a specific functionality of Vim. They straight to the point, and the author provides samples code for all videos.
I think something that might work better is adding a “commit” signal to operations. So you type `d3w` and the editor highlights the next three words with a strike through or red or whatnot. Then you can hit enter to commit the delete or escape to cancel it (cursor resets to start position, highlight goes away).
You can also use that with ex commands, like if you do `vap:s/foo/bar/g` you will replace `foo` with `bar` only within the block of code you visually highlighted with `ap` (which I remember as “a paragraph).
So if you want “delete three words”, do `d3w`. If you want “highlight three words” do `v3w` and then issue another command like `d` to do something with what you highlighted.
I mean, if you hit v first, that's exactly what it does.
Or is this just reversing the order so it's {motion}{verb} instead of {verb}{motion}?
It's also generally lean and follows the Unix philosophy, e.g. by using shell script, pipes, and built-in Unix utilities to do complex operations, rather than inventing a new language (vimscript) for it.
(Not affiliated with the creator, but kakoune has been my daily driver for years now.)
The situation could certainly be better, but at least this works today.
Consider `SELECT 0 AS some_num`. This does not have a FROM clause.
While this example seems contrived, there are several examples of queries where the FROM clause is not just a listing of tables. Especially when moving into larger projects in SQL.
You know I think you're right.
I suppose it could be nice if the user could specify clauses in an arbitrary order but it’d certainly add complexity.
I don’t find it difficult to jump around a bit from clause to clause while writing a query. In fact, it’s incredibly rare to write a query straight through and have it do what you want it to do.
FROM users WHERE name LIKE 'a%' SELECT name, id, location
(I should refresh the page before posting)
After parsing and analysis steps, the system is going to do what it does with the statement, no?
The syntax is for the user, not the system.
This is paramount for performance.
If autocompletion is your issue, just get a better client, it's perfectly possible to autocomplete field names even before specifying database or table name.
The problem is that SQL forces you to think about what to select before you even say where you're selecting from. There's nothing a client can do to recommend columns if it doesn't know where you're selecting from. It's pretty cumbersome to have to SELECT * FROM x and then go back and erase the * to actually get auto-completions.
It basically forces you to tell it what you want before you even know what the options are.
I know that from the interpreter's standpoint, it doesn't matter which one is written first.
What I meant is that as a human, if you have to think first of the columns you want to bring in, it will guide you towards the joins that you need and only those, rather than thinking "let me join all those tables because I need _some_ data from the entities inside".
My point about "autocompletion is still position" was not connected to the first part of my comment.
I just added it as a separate point, to say "you can have autocomplete no matter the order in which you write your query"...
That's an exaggeration. Usually I just start with Select * and build all the necessary joins. For that the tooling support works without problems and when I select the columns in the end it works too.
It makes more sense to have the select last.