161–170 of 260 posts
The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see…
Earlier quoted context omitted.
You could improve type ahead by just pouring effort into the IDE. Especially with the amount of resources we have nowadays. Easy enough to have basic typeahead on basically all possible columns when writing the select, and then you could use the columns as a filter on the tables during auto complete. In general, this isn't done. But I don't see any technical reason it can't be done.
This strikes me as solving the wrong problem. It's not as useful to be able to derive a table from selected columns -- the desire is to complete columns from a table. It's both more intuitive and helps the user more than once.
Earlier quoted context omitted.
You could improve type ahead by just pouring effort into the IDE. Especially with the amount of resources we have nowadays. Easy enough to have basic typeahead on basically all possible columns when writing the select, and then you could use the columns as a filter on the tables during auto complete. In general, this isn't done. But I don't see any technical reason it can't be done.
If you have multiple tables, how do you avoid suggesting a wrong column, before filling out the table name? You could suggest all columns up front, and then afterwards tell the user “this suggestion doesn’t exist”, which would just erode trust in the autocomplete.
I once read an article about SQL and how reordering the sections of a query would make it more ergonomic for users (iirc things like specifying what you want first and then how to present it last, more like a pipeline). I searched many times over the years but have not been able to find it again.
https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...
Earlier quoted context omitted.
But thats just it, having many silos for connected information is just not the way we humans do it, nor necessary. A single conceptually indexed space/time hypergraph (as we humans do it) for any part of the world is all you need, scalable up to many billions of edges for that one part of the world.
having many silos for connected information is just not the way we humans do it What do you mean? I can think of many silos containing connected information: municipal residence records, marriage registries, birth records, police reports, tax records, medical records (for each hospital) are all silos connected by a citizen's identity.
Earlier quoted context omitted.
If you have a business need to represent "empty" or "n/a" or "declined to answer" or something like that, use a specific value for that. NULL does not mean anything. Or, it means nothing. It's just NULL. Once I got that into my head, SQL became less frustrating.
A better option in many cases is to check the primary key. e.g. select questions.Id , questions.Text , answers.Id , answers.Text from questions left join answers on answers.QuestionId = Questions.Id answers.Id is non-nullable as a primary key, so if answers.Text is null but answers.Id is not null they've declined to answer.
In your example it is very likely that the primary key is the clustering key, so will be present in the non-clustered index that I assume will be on answers.questionId, making my point moot, but if for some unusual reason neither Id nor questionId were the clustering key checking Id may result in extra reads being needed.
In DBMSs without clustering keys implemented similarly to SQL Server, there may be such concerns in all cases.
Earlier quoted context omitted.
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).
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
Earlier quoted context omitted.
You’re not obliged to pull everything in one request. You can issue several requests.
Sure. You're also not obligated to include a WHERE-clause in the query you send to your database. You can do the filtering in the application.
Earlier quoted context omitted.
I'm not familiar with Ecto, dplyr, or DBT, but I would love an ML-like language to replace SQL. I'm imagining being able to pass a table (or any table-oriented data, like a sub-query) to functions that would type-check columns and would return table-oriented or scalar data. I'm not sure if this is actually possible in practice, but one can dream. For instance, a "top 10" function that could be re-used on any table (a…
Not 100% sure about what you're suggesting, but wouldn't it be easier to pass your functions to your table/sub-query? And that's exactly what you're able to do in most of the modern data warehouse services such as Snowflake. Inferences can be contained within internal/external user defined functions. This is very reminiscent of made the big-data/map-reduce movement so notable, sending your query to the data instead o…
The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see…
FWIW in LINQ, Select comes after From and Where.