8 ms·
The main issue with sql is, that it is the wrong way around, which eliminates all tooling support. You need to state what you want (select a, b, c) before you
by LunicLynx 3y ago
The main issue with sql is, that it is the wrong way around, which eliminates all tooling support.
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.
- XorNot 3y agoFROM users SELECT name, id, location WHERE name LIKE 'a%'; You know I think you're right.
- frogulis 3y agoGo further: `FROM users WHERE name LIKE 'a%' SELECT name, id, location` - after all, you can WHERE on things that aren't projected by SELECT
- deleted 3y ago[deleted]
- 6510 3y agoA human would grab the paper with the right table on it then search for the row he wants with his finger and finally gets the phone number from the row.
- user3939382 3y agoThere was an article on HN about this maybe 6 months ago. I’m in agreement.
- ellisv 3y agoEh. I’m not convinced by this. I may know what I want to query as often or more than knowing where it comes from. 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.
- 6510 3y agoO well, if we are going to be like that?: FROM users WHERE name LIKE 'a%' SELECT name, id, location (I should refresh the page before posting)
- blipvert 3y agoI might be wrong, but there’s nothing to stop a front end UI from accepting this syntax and then writing out canonical SQL to do the actual query, is there?
- valenterry 3y agoExactly, it puzzles me that tooling doesn't do that...
- tester756 3y agoC#'s LINQ (query syntax, not methods) got it right var result = from s in stringList where s.Contains("Tutorials") select s;
- DaiPlusPlus 3y agoor just `var result = stringList.Where( s => s.Contains("Tutorials") )` 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.
- rjbwork 3y ago>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` 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.
- recursive 3y agoI guess I'm not in the C# community. `let` is pretty annoying to reproduce with extension methods. Also, when you use multiple `from` clauses, you get access to all scopes, whereas with `SelectMany` you only get the parameters of your current lambda.
- DaiPlusPlus 3y ago> you get access to all scopes 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"
- Etheryte 3y agoWhile I agree with your point, this isn't really as big of a stopper for tooling as you make it seem. Many programming languages use something akin to import foo from bar and their tooling is just fine.
- ako 3y agoAlways start with the end in mind, first what your goal is then how to achieve it. 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.
- LunicLynx 3y agoExactly, you could actually drop the `select` in this case and just say `from table limit 10` don't state what you don't need. 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.
- ayhanfuat 3y agoThat's valid DuckDB syntax
- mcdonje 3y agoDuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases. I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language. https://duckdb.org/2022/05/04/friendlier-sql.html https://duckdb.org/2022/05/04/friendlier-sql.html
- jojobas 3y agoAin't broke.
- kristiandupont 3y agoGP clearly shows why it is. I think SQL is great but their point is spot on.
- badestrand 3y agoThe wrong order in the statements is a minor inconvenience but the widespread use and having an industry standard is 100x as valuable.
- ellisv 3y agoAgreed. The last thing I need is more flexibility in how people write queries. It would be shortly followed by numerous aggressive query formatting tools. Things would get messy fast.
- jojobas 3y agoThat's just his opinion. SQL does it's job extremely well, is relatively easy to parse for its inherent complexity. That somebody would like FROM before SELECT or WHERE after GROUP BY is of no consequence.
- danielheath 3y agoThis is also my primary issue with vi. 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.
- xyzzy_plugh 3y agov3wd?
- Olreich 3y ago`3w` is a command all its own. There’s an implicit move command baked in. Moving gets pretty clunky if you don’t have first order movement (adding a specifier or something to clear selection). You can mimic this by mapping a single button to <C-v> and disabling all movement commands in normal mode. It’s rough, but may be learnable. 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).
- wryanzimmerman 3y agoIf you use visual mode, you can do `v3w` to highlight three words, and then you can manipulate that selection before you do something with it, like `d` for delete. 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.
- PhilipRoman 3y agoI think these two concepts could be reconciled by having selection be combined with moving. So 3w could select three words but also move the cursor to the end of selection, ready to process another normal mode command. The highlighting would need to be scaled back a little (perhaps a dark gray background), as you don't want your entire screen to light up when moving but I think it's doable.
- wood_spirit 3y agoYeap, simply supporting FROM … SELECT … WHERE … would make sql much more autocompleteable?
- n_e 3y agoIn most tooling, you can write SELECT FROM table, then go back to the select list and have autocomplete work. The situation could certainly be better, but at least this works today.
- smitty1e 3y agoThe query is just a requirements statement. 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.
- psnehanshu 3y agoSure, but the parent is speaking about how it is difficult for tooling to present with options when it doesn't know which table to select from.
- iLoveOncall 3y agoThis is a strength, not a weakness. It forces you to only select what you actually need, and state where it comes from afterwards, to only limit your query to exactly what you need. 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.
- daxvena 3y agoI think you're missing what they're trying to say. You can still select what you actually need in a different order, but changing the order gives more immediate feedback from autocomplete. The order has pretty much zero impact on the performance of a query. A parser would still have to read out the whole query, and it's not expensive to unravel into a more efficient implementation if it would really help. 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.
- iLoveOncall 3y agoYou are missing what I said. 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.
- xboxnolifes 3y agoEven if I know the exact query I want to write, your suggestion does nothing to improve autocomplete for typing it in.
- deleted 3y ago[deleted]
- mrjin 3y agoAlmost exactly what I was going to say, then saw your comments. To me that the biggest problem. What I have been doing to workaround this is to just write select *, then finish the from join etc, go back and fix the result columns.
- mcdonje 3y agoI get around this when hand coding by doing a quick 'SELECT * FROM', adding my joins, then going back and filling in the fields using intellisense. Intellisense doesn't know what columns are available until the FROM clause is handled. 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.
- cfiggers 3y agoThat's exactly what I do too. 'SELECT * FROM', then write the rest of the query, then double back and replace the * at the end. Is it ideal? No. But it's muscle memory now, so... ¯\_(ツ)_/¯
- WuxiFingerHold 3y agoOh, that's a great idea. What editor/ui do you use? Since I've got divorced from Jetbrains I miss Datagrip ...
- willemlabu 3y agoWhy the divorce? DataGrip has been the best database-gui that I've ever used.
- WuxiFingerHold 3y agoI had the all-tools subscription for years (working mainly on Spring Boot and Angular projects) and IntelliJ and Webstorm were fantastic. Then I started Vue projects and later on Svelte and the Webstorm support for both (and Tailwind btw.) has been a disaster. Esp. how they handled the countless issues created by me and many others. Many other small annoyances like no support for international keyboards with dead keys on Linux and esp. how support handled it. 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.
- mabbo 3y agoI agree with you and think this backwards model leads to developers having a poor mental model of what they are doing. 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.
- deleted 3y ago[deleted]
- madsbuch 3y agoThis is definitely something that makes tooling harder. But I think it is because tooling is though up wrongly. 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.
- zaxomi 3y agoIf I could change one thing with sql, this would be it. It makes more sense to have the select last.
- camgunz 3y agoJust start with `SELECT *` and sculpt w/ autocomplete afterwards as you like.
- yencabulator 3y agoSince you express that opinion, I trust you're already aware of this, but just in case: https://prql-lang.org/ https://prql-lang.org/
- croes 3y ago>eliminates all tooling support. 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.