3 ms·

I'm assuming you are largely just not thinking this one through? We are not modeling the domain, we are describing some data we want to select. Without knowin

by taeric 1y ago

I'm assuming you are largely just not thinking this one through? We are not modeling the domain, we are describing some data we want to select. Without knowing how it is modeled, I can give a brief "top line" for expected select statements on many data models. "I want average_weather, year, zip_code", "I want year, college_name, degree_name, median_graduate_salary, mean_graduate_salary", "..." I don't think it is tough to describe many many reports of data that you would want in this way. Is it enough for you to flat out get the answer? No, of course not. But nor is it enough to just start all queries at what possible tables you could use as a starting point.

debazel 1y ago
How would you even know it's possible get the data before you've chosen a table? Your example is also not a complete select statement, you would need to go back and add the actual aggregate functions, and oops the zip_code column was actually called zip, so we need to remap that as well. You can almost never finish a select statement before you have inspected the tables, so why not just start there immediately?
- taeric 1y ago
  Often times you don't know it is possible to get some data without consulting the table? Worse, you probably need to consult the views that have been made by any supporting DB team before you go off and build your own rollups. Unless you like not taking advantage of any aggregate jobs that are running nightly for reporting. And to be clear, I'm all for complaining about the order of a select statement, to a very large degree. It is done in such a way that you have to jump back and forth as you are constructing the full query. That can feel a bit painful. However, I don't know that it is just the SELECT before FROM that causes this jumping back and forth and fully expect that you would jump around a fair bit even with the FROM first. More, if I am ever reworking a query that the system is running, I treat the SELECT as the contract to the application, not the FROM. There is a bit of "you should know the database before you can expect to send off a good query", but that really cuts to any side of this debate? How do you know the tables you want so well, but you don't know the columns you are going to ask for?
  - debazel 1y ago
    > How do you know the tables you want so well, but you don't know the columns you are going to ask for? Simply because there are a lot more columns than there are tables. Of course, I sometimes forget the exact table name as well. However, this is mostly not an issue as the IDE knows all table names before anything has been entered. By simply entering `FROM user`, the autocomplete will list all tables that contain `user`, and I can complete it from there. I cannot do the same with the column selection unless I first write the FROM part. And even if I do know exactly which columns and tables I want I would still want autocomplete to work when creating the SELECT statement. Rather than typing `zip_code`, I can most likely just type `z<TAB>`. That is why 99% of my select queries start as `SELECT * FROM mytable`, and then I go back to fill in the select statement. And it's not just me, all colleagues I've worked with does exact same thing. For larger, more complicated queries, you'll have to go back and forth a lot as you join tables together, that is unavoidable, but 80-90% of my queries could be finished in one go if the FROM part came first.
    - taeric 1y ago
      But "more columns than there are tables" is also why I put the thing about, "the total number of columns most people would need autocomplete for are rather limited?" I could see an argument on how this mattered back at the origin of SQL, but today? It is entirely conceivable that you can autocomplete the column names with it updating the from as necessary for the columns you have selected. You could then tab to the from and pick alternative tables with natural joins prefilled between the tables. With very minimum effort. I actually seem to recall this was done in some of the early dedicated SQL tools. It is rather amusing how much we handicap ourselves by not using some things older tools built.
jimbokun 1y ago
How did you know it was zip_code and not ZipCode? Maybe in this case you know the same naming conventions are enforced across all tables. But in general it’s difficult to know the exact column name without looking it up first.
- lelanthran 1y ago
  > How did you know it was zip_code and not ZipCode? I think you're missing the point; you start off with your goal, regardless of the column name. No one has the goal "we want columsn from this specific table", it's always "we want these columns in the final output".
  - prollings 1y ago
    Right, but which columns? The goal absolutely IS to select columns from a table. ie. my goal is not to select created_at, my goal is to select Foo's created_at.
    - lelanthran 1y ago
      > The goal absolutely IS to select columns from a table. No. The goal is to have an output column $FOO, where $FOO is meaningful and might not even be in the database in the first place. > my goal is not to select created_at, my goal is to select Foo's created_at. Then your goal is to get a value out of a specific table, namely `Foo`, presumably because the end-user wants to see some value in the results. The end-user getting the value has neither an interest in nor knowledge of your schema. `Foo.created_at` is no more meaningful in the result sets than an unadorned `created_at`. For this specific example, the end-user might want a column in the output called `age` (if it came from an inventory table`, or `duration` (if it came from a metrics tables), or perhaps `expiry` (if it is a table containing perishable stock). These are all a column in a Foo table, but the request that caused you to write the query in the first place does not mention table at all. You get their requirement as either `age`, `duration`, `expiry` or similar. That is what you are starting from: "a need for a specific piece of data". You are not starting from "a need from a specific table", because the information needed is coming from someone who neither knows nor cares what your schema looks like.

by taeric 1y ago

debazel 1y ago

How would you even know it's possible get the data before you've chosen a table? Your example is also not a complete select statement, you would need to go back and add the actual aggregate functions, and oops the zip_code column was actually called zip, so we need to remap that as well. You can almost never finish a select statement before you have inspected the tables, so why not just start there immediately?

taeric 1y ago
Often times you don't know it is possible to get some data without consulting the table? Worse, you probably need to consult the views that have been made by any supporting DB team before you go off and build your own rollups. Unless you like not taking advantage of any aggregate jobs that are running nightly for reporting. And to be clear, I'm all for complaining about the order of a select statement, to a very large degree. It is done in such a way that you have to jump back and forth as you are constructing the full query. That can feel a bit painful. However, I don't know that it is just the SELECT before FROM that causes this jumping back and forth and fully expect that you would jump around a fair bit even with the FROM first. More, if I am ever reworking a query that the system is running, I treat the SELECT as the contract to the application, not the FROM. There is a bit of "you should know the database before you can expect to send off a good query", but that really cuts to any side of this debate? How do you know the tables you want so well, but you don't know the columns you are going to ask for?
- debazel 1y ago
  > How do you know the tables you want so well, but you don't know the columns you are going to ask for? Simply because there are a lot more columns than there are tables. Of course, I sometimes forget the exact table name as well. However, this is mostly not an issue as the IDE knows all table names before anything has been entered. By simply entering `FROM user`, the autocomplete will list all tables that contain `user`, and I can complete it from there. I cannot do the same with the column selection unless I first write the FROM part. And even if I do know exactly which columns and tables I want I would still want autocomplete to work when creating the SELECT statement. Rather than typing `zip_code`, I can most likely just type `z<TAB>`. That is why 99% of my select queries start as `SELECT * FROM mytable`, and then I go back to fill in the select statement. And it's not just me, all colleagues I've worked with does exact same thing. For larger, more complicated queries, you'll have to go back and forth a lot as you join tables together, that is unavoidable, but 80-90% of my queries could be finished in one go if the FROM part came first.
  - taeric 1y ago
    But "more columns than there are tables" is also why I put the thing about, "the total number of columns most people would need autocomplete for are rather limited?" I could see an argument on how this mattered back at the origin of SQL, but today? It is entirely conceivable that you can autocomplete the column names with it updating the from as necessary for the columns you have selected. You could then tab to the from and pick alternative tables with natural joins prefilled between the tables. With very minimum effort. I actually seem to recall this was done in some of the early dedicated SQL tools. It is rather amusing how much we handicap ourselves by not using some things older tools built.

jimbokun 1y ago

How did you know it was zip_code and not ZipCode? Maybe in this case you know the same naming conventions are enforced across all tables. But in general it’s difficult to know the exact column name without looking it up first.

lelanthran 1y ago
> How did you know it was zip_code and not ZipCode? I think you're missing the point; you start off with your goal, regardless of the column name. No one has the goal "we want columsn from this specific table", it's always "we want these columns in the final output".
- prollings 1y ago
  Right, but which columns? The goal absolutely IS to select columns from a table. ie. my goal is not to select created_at, my goal is to select Foo's created_at.
  - lelanthran 1y ago
    > The goal absolutely IS to select columns from a table. No. The goal is to have an output column $FOO, where $FOO is meaningful and might not even be in the database in the first place. > my goal is not to select created_at, my goal is to select Foo's created_at. Then your goal is to get a value out of a specific table, namely `Foo`, presumably because the end-user wants to see some value in the results. The end-user getting the value has neither an interest in nor knowledge of your schema. `Foo.created_at` is no more meaningful in the result sets than an unadorned `created_at`. For this specific example, the end-user might want a column in the output called `age` (if it came from an inventory table`, or `duration` (if it came from a metrics tables), or perhaps `expiry` (if it is a table containing perishable stock). These are all a column in a Foo table, but the request that caused you to write the query in the first place does not mention table at all. You get their requirement as either `age`, `duration`, `expiry` or similar. That is what you are starting from: "a need for a specific piece of data". You are not starting from "a need from a specific table", because the information needed is coming from someone who neither knows nor cares what your schema looks like.