5 ms·

SELECT id, name, author Quick, what is this query about? What's ironic is that I think you have it backwards: the columns are the implementation detail, not th

SELECT id, name, author Quick, what is this query about? What's ironic is that I think you have it backwards: the columns are the implementation detail, not the table. The table is the context: you can't change that without having to change everything else. But columns are the last step, the selection after the filters, joins, etc. They can be changed at any time without affecting the logic.

taeric 5y ago
This is... An odd choice. I'd assume I'm not without context looking at a query to know why I would want those columns. And the auto complete story is backwards. Often I know what columns I want, but I'm not clear what table I need to get them from. Such that, if you make a smarter suggest in the from to only include tables that have the columns, I'd be much happier.
- sanderjd 5y ago
  Just throwing in another point of anecdata onto this pile: "Often I know what columns I want, but I'm not clear what table I need to get them from" does not make sense to me. I don't relate at all to their being a global namespace of columns, rather than a namespace of tables, each with its own columns specific to its context.
  - taeric 5y ago
    I challenge this. I accept that there are ambiguities, but I assert that you can go really fast by just telling someone to fetch a few columns by name. I further assert that if your database is filled with "Id" and "name" columns, instead of "department_name" and similar, you are probably as likely to mess up a join as any benefit you get from the name being short. (And really, what advantage is there in short names nowadays?) That all said. I worded my take too strongly. My point should have been that auto suggest should not be confined in either direction.
    - sanderjd 5y ago
      I think we have just done most of our data work in different environments. When I'm trying to query stuff, the first question is "which service's database is that in?", so I can guess "user_service" (or whatever I think it is called), but I have no idea what they call anything in their schema, but now that the autocomplete system knows what table I'm interested in, it can help me figure that out.
      - taeric 5y ago
        I'm used to emacs, with a global namespace. Such that I'm used to searching all variables globally. Feels that searching all columns would be just as easy, all told. That said, I want to be clear that I think both methods are valid and work.
        sanderjd 5y ago
        Yeah, it's just that I don't know what the columns are called. The namespaces (databases and tables) are what guides me to the columns, not vice versa. I can think back to projects / companies where this may have been different, basically just with fewer different distinct schemas, but it just isn't my recent experience. (I appreciate your magnanimity in these later comments by the way.)
BeefWellington 5y ago
It's query asking for the id, name, and author fields. Very straightforward, I have no idea how this is confusing. > The table is the context: you can't change that without having to change everything else. Except even in the provided single-table example this isn't true - you're getting subselected/CTEd results. No functional joins are demonstrated unfortunately. For example: from employees left_join positions [id=employee_id] ...is equivalent to... SELECT * FROM employees LEFT JOIN positions ON id = employee_id No data is selected from positions in either example, and it's unclear on why we're joining that table (other than just for the heck of it). It's not a workable example.
- phailhaus 5y ago
  You restated the query; I was asking what it's about. Is it a query across publications? Or is it a query over news articles? That context changes everything: how the query is written, what it can be joined with, how it can be filtered, how it is used, etc. Putting the FROM clause first means that you immediately have context to understand the rest of the query.
  - BeefWellington 5y ago
    We can both play this game though: FROM articles What fields am I expecting in the resultset? Sure I have some context, I know it'll be about articles, but I have no idea what actual data I care about. You're arguing that: FROM articles SELECT id, name, author Is substantially superior to: SELECT id, name, author FROM articles I don't see them as markedly different with such a small example. HOWEVER, where the difference comes in is the "at a glance what am I getting in my resultset" data that is much easier to see in the latter, second easiest in the former, and not at all present in the linked article. I think what this boils down to is what you (any reader, not you specifically) individually expect to need knowledge of when you're writing SQL. In most cases when sitting down to write a brand new piece of code to pull some data from the database, putting the list of tables involved in the query first matters most to some, whereas putting the list of fields to expect in the resultset matters most to others (I put myself in this camp). For what it's worth, as I've stated elsewhere while I don't prefer it and would find it annoying to debug personally, I do recognize that the idea of SQL that allows you to list FROM / JOIN / etc. first is very appealing to some. What I think is completely off is the near-obfuscation of the examples in the linked article.

taeric 5y ago

This is... An odd choice. I'd assume I'm not without context looking at a query to know why I would want those columns. And the auto complete story is backwards. Often I know what columns I want, but I'm not clear what table I need to get them from. Such that, if you make a smarter suggest in the from to only include tables that have the columns, I'd be much happier.

sanderjd 5y ago
Just throwing in another point of anecdata onto this pile: "Often I know what columns I want, but I'm not clear what table I need to get them from" does not make sense to me. I don't relate at all to their being a global namespace of columns, rather than a namespace of tables, each with its own columns specific to its context.
- taeric 5y ago
  I challenge this. I accept that there are ambiguities, but I assert that you can go really fast by just telling someone to fetch a few columns by name. I further assert that if your database is filled with "Id" and "name" columns, instead of "department_name" and similar, you are probably as likely to mess up a join as any benefit you get from the name being short. (And really, what advantage is there in short names nowadays?) That all said. I worded my take too strongly. My point should have been that auto suggest should not be confined in either direction.
  - sanderjd 5y ago
    I think we have just done most of our data work in different environments. When I'm trying to query stuff, the first question is "which service's database is that in?", so I can guess "user_service" (or whatever I think it is called), but I have no idea what they call anything in their schema, but now that the autocomplete system knows what table I'm interested in, it can help me figure that out.
    - taeric 5y ago
      I'm used to emacs, with a global namespace. Such that I'm used to searching all variables globally. Feels that searching all columns would be just as easy, all told. That said, I want to be clear that I think both methods are valid and work.
      - sanderjd 5y ago
        Yeah, it's just that I don't know what the columns are called. The namespaces (databases and tables) are what guides me to the columns, not vice versa. I can think back to projects / companies where this may have been different, basically just with fewer different distinct schemas, but it just isn't my recent experience. (I appreciate your magnanimity in these later comments by the way.)

BeefWellington 5y ago

It's query asking for the id, name, and author fields. Very straightforward, I have no idea how this is confusing. > The table is the context: you can't change that without having to change everything else. Except even in the provided single-table example this isn't true - you're getting subselected/CTEd results. No functional joins are demonstrated unfortunately. For example: from employees left_join positions [id=employee_id] ...is equivalent to... SELECT * FROM employees LEFT JOIN positions ON id = employee_id No data is selected from positions in either example, and it's unclear on why we're joining that table (other than just for the heck of it). It's not a workable example.

phailhaus 5y ago
You restated the query; I was asking what it's about. Is it a query across publications? Or is it a query over news articles? That context changes everything: how the query is written, what it can be joined with, how it can be filtered, how it is used, etc. Putting the FROM clause first means that you immediately have context to understand the rest of the query.
- BeefWellington 5y ago
  We can both play this game though: FROM articles What fields am I expecting in the resultset? Sure I have some context, I know it'll be about articles, but I have no idea what actual data I care about. You're arguing that: FROM articles SELECT id, name, author Is substantially superior to: SELECT id, name, author FROM articles I don't see them as markedly different with such a small example. HOWEVER, where the difference comes in is the "at a glance what am I getting in my resultset" data that is much easier to see in the latter, second easiest in the former, and not at all present in the linked article. I think what this boils down to is what you (any reader, not you specifically) individually expect to need knowledge of when you're writing SQL. In most cases when sitting down to write a brand new piece of code to pull some data from the database, putting the list of tables involved in the query first matters most to some, whereas putting the list of fields to expect in the resultset matters most to others (I put myself in this camp). For what it's worth, as I've stated elsewhere while I don't prefer it and would find it annoying to debug personally, I do recognize that the idea of SQL that allows you to list FROM / JOIN / etc. first is very appealing to some. What I think is completely off is the near-obfuscation of the examples in the linked article.