4 ms·

> Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. An experienced person won't do that. For

> Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. 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.

jmull 3y ago
> An experienced person won't do that. 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.)
- weinzierl 3y ago
  Me too and I don't even bother with LIMIT 10. However, the "proper" experienced way would be DESCRIBE I guess.
marcosdumay 3y ago
If you mean a developer with deep and recent experience on the exact tables your query will use, then yeah, this one won't do that. 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.
ako 3y ago
I consider myself experienced, started professionally with sql in 1994 with oracle 6, and it really depends on how familiar you are with the database. I often work with databases I’m not familiar with, so exploring the structure by looking at examples of the data is where I start.
kubanczyk 3y ago
> have in mind the several jointures 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.
- lostdog 3y ago
  This is a great idea in practice, but it really points to how sql could improve. You have a library of useful joins that you've checked for correctness. Saving the library as reusable code would be even more useful.

jmull 3y ago

> An experienced person won't do that. 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.)

weinzierl 3y ago
Me too and I don't even bother with LIMIT 10. However, the "proper" experienced way would be DESCRIBE I guess.

marcosdumay 3y ago

If you mean a developer with deep and recent experience on the exact tables your query will use, then yeah, this one won't do that. 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.

ako 3y ago

I consider myself experienced, started professionally with sql in 1994 with oracle 6, and it really depends on how familiar you are with the database. I often work with databases I’m not familiar with, so exploring the structure by looking at examples of the data is where I start.

kubanczyk 3y ago

> have in mind the several jointures 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.

lostdog 3y ago
This is a great idea in practice, but it really points to how sql could improve. You have a library of useful joins that you've checked for correctness. Saving the library as reusable code would be even more useful.