4 ms·

My main caveat here, is that often the person starting a select knows what they want to select before they know where to select it from. To that end, having au

• Zedseayou 1y ago
  Can you help me understand this situation? Almost always what I want to select is downstream of what I am selecting from, I can't write anything in SELECT until i understand the structure/columns available in FROM.
  • taeric 1y ago
    "I want 'first_name, last_name, and average(grade)' for my report." That is easy enough to state without knowing anything about how the data is normalized. Back when I worked to support a data science team, I actually remember taking some of their queries and stripping everything but the select so that I could see what they were trying to do and I could add in the correct parts of the rest.
    • mamcx 1y ago
      Even assuming that this is plausible way a user could think about it (that can be understood) it shows why is bad Think: I have 20 tables with the column `id` "I want 'id,id,id'" is bad UX, and is what here is being argued, then when the syntax guide you: "I want 'FROM a: id'" is better
      • taeric 1y ago
        I don't think anyone would ever say they want "id, id, id"? They would say they want "customer_id, order_id, item_id" or some such. You would then probably say, "we just use 'id' for the id of every item..." but many places that I've worked actually explicitly didn't do that for essentially this reason. Natural joins on "foo_id" "just work" if you don't give every table the same "id" column.
        • mamcx 1y ago
          That is what the syntax allow. And is the user that need to "patch" the meaning using other things like alias, renames or hopefully, proper names for fields (by the way "id, id, id" happens a lot in the wild!) Is like "to control mutation I only need to append "mut" to the name!
      • necovek 1y ago
        Current SELECT syntax does allow one to "SELECT user.id as user_id, product.id as product_id..." which can then even autocomplete the FROM for you from your query "declaration" (a-la function declaration, in particular its return type).
    • jayd16 1y ago
      This assumes I have all the column memorized but not all the tables? Even in your example, first and last could refer to student or teacher. But presumably you know you're looking for student data before knowing the exact columns.
      • taeric 1y ago
        No, this assumes you know what you want for a query. Which, seems largely fair? Like, how would you send this question to someone? Or how would you expect it to be sent to you? If your boss doesn't tell you from what table they want some data, do you just not answer? And sure, there could be ambiguities here. But these are not really fixed by listing the sources first? You would almost certainly need to augment the select to give the columns names that disambiguate for their uses. For example, when you want the teacher and the student names, both. And this is ignoring more complications you get from normalized data that is just flat out hard to deal with. I want a teacher/student combination, but only for this specific class and term, as an example. Whether you start the from at the student, the class, or the term rosters feels somewhat immaterial to how you want to pull all of that data together for why ever you are querying it.
        • jayd16 1y ago
          If my boss asks me for a zip code, I'm going to ask "for what?" If they ask for "address for a customer" I can go to the customer table and look up what FKs are relevant and collect all possible data and then narrow down from there.
          • taeric 1y ago
            I'd assume they would ask for "aggregate sales by month to zip codes," or some such. Which, you'd probably get from a reporting table, and not bother trying to do the manual aggregate over transactional data. (That is, OLAP versus OLTP is a very real divide that you can't wave away.) Realistically, I strongly suspect you could take this argument either direction. If you have someone making a query where they are having to ask "what all tables could I start from?" you are in for some pain. Often the same data is reachable from many tables, and you almost certainly have reasons for taking certain paths to get to it. Similarly, if they should want "person_name", heaven help them. Such that, can you contrive scenarios where it makes sense to start the query from the from clause? Sure. My point is more that you almost certainly have the entire query conceptualized in your mind as you start. You might not remember all of the details on how some things are named, but the overall picture is there. Question then comes down to if one way is more efficient than the other? I have some caveats that this is really a thing hindered by the order of the query. We don't have data, of course, and are arguing based on some ideas that we have brought with us. So, would I be upset if the order was reversed? Not at all. I just don't expect that would actually help much. My memory is using query builders in the past where I would search for "all tables that have customer_id and order_id in them" and then, "which table has customer_id and customer_address" and then... It was rarely (ever?) the name of the table that helped me know which one to use. Rather, I needed the ones with the columns I was interested in.
• jayd16 1y ago
  Seems nonsensical. Column names have no meaning without the table. Table is the object, columns are the properties. What language goes Property.Object? All popular languages have this wrong?
  • 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?
• Izkata 1y ago
  Likewise when skimming code, having the attributes on the left and details on the right makes it much easier to see the data flow in the larger program - it's the same order as "var foo = bar()".
• jimbokun 1y ago
  I can’t think of an example where I knew the columns I wanted to select before I knew which table I wanted to select them from.
  • necovek 1y ago
    Did you ever not do things like select name, min(price) from product join product_price on product.id = product_price.product_id where you start with "I need a product name and minimum price" before thinking about where they come from? The more one uses SQL to filter for, the more you'll think about what you want to achieve vs how (which comes after): in a sense, the SELECT is your function return type declaration, and I frequently start my functions with the declaration.
    • masklinn 1y ago
      > where you start with "I need a product name and minimum price" before thinking about where they come from? Sure, and pretty much every time the names I wrote up were not the ones in the table so that was a complete waste of time. > the SELECT is your function return type declaration That might be true if “select” only contained aliases, bur that’s not the case at all, so what it is is complete nonsense.
      • taeric 1y ago
        This presumes that you never pick the wrong table, as well? :D My argument would be largely that you are doing a search of all of the tables for the columns that you want. With having to know the way to join the necessary tables along the way. And I want to be clear, I don't think this is necessarily "the way." I more think that it is almost certainly an iterative process that can be started at either place, and will require bouncing between the two quite often. For instance, yes, you can autocomplete column names from tables that you have put in the from. What if you can't find the column you are anticipating there? Go back and rescan the table names hoping you can guess which table should have the column you want? Then go back to autocomplete for columns to see if expected value appears? Or go to a bit more of a global search for columns? Either way should be fine.