4 ms·

Always start with the end in mind, first what your goal is then how to achieve it. Also, i don't actually see the problem because you never write a query in a

by ako 3y ago

Always start with the end in mind, first what your goal is then how to achieve it. Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.

LunicLynx 3y ago
Exactly, you could actually drop the `select` in this case and just say `from table limit 10` don't state what you don't need. What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry. My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.
- ayhanfuat 3y ago
  That's valid DuckDB syntax
  - mcdonje 3y ago
    DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases. I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language. https://duckdb.org/2022/05/04/friendlier-sql.html https://duckdb.org/2022/05/04/friendlier-sql.html
    - zX41ZdbW 3y ago
      Most of the improvements in the article, including unrestricted aliases, were added in ClickHouse and subsequently influenced DuckDB. They still have to implement many usability and language improvements from ClickHouse.
      - mcdonje 3y ago
        Didn't know that, thanks.
    - magicalhippo 3y ago
      > GROUP BY aliases We're migrating from Sybase SQLAnywhere to MSSQL, and not being able to use aliases in WHERE, GROUP BY and ORDER BY is such a pain in the behind. Almost all our non-trivial queries have to be nested multiple levels due to this, which doesn't exactly help readability.
      - bavell 3y ago
        Ugh, I'll be having to do this same migration soon for one of my clients. Not looking forward to it. Good to know!
    - fbdab103 3y ago
      I would love if base SQL were improved. On the other hand, we are just slapping lipstick on a pig. It is already a gargantuan mess which needs a path to replacement. Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.
    - chrisjc 3y ago
      Agreed... Their SQL is awesome and seemingly only getting better. My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance. > The three window functions will also share the data layout, which will improve performance. https://duckdb.org/docs/sql/window_functions#window-clauses https://duckdb.org/docs/sql/window_functions#window-clauses I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.
  - ttfkam 3y ago
    Because the DuckDB folks used the Postgres query parser. It's been an alternative in Postgres for decades.
- ttfkam 3y ago
  In Postgres, it's TABLE foo LIMIT 100; No SELECT with columns and no FROM keyword.
idoubtit 3y ago
> 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

by ako 3y ago

LunicLynx 3y ago

Exactly, you could actually drop the `select` in this case and just say `from table limit 10` don't state what you don't need. What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry. My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.

ayhanfuat 3y ago
That's valid DuckDB syntax
- mcdonje 3y ago
  DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases. I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language. https://duckdb.org/2022/05/04/friendlier-sql.html https://duckdb.org/2022/05/04/friendlier-sql.html
  - zX41ZdbW 3y ago
    Most of the improvements in the article, including unrestricted aliases, were added in ClickHouse and subsequently influenced DuckDB. They still have to implement many usability and language improvements from ClickHouse.
    - mcdonje 3y ago
      Didn't know that, thanks.
  - magicalhippo 3y ago
    > GROUP BY aliases We're migrating from Sybase SQLAnywhere to MSSQL, and not being able to use aliases in WHERE, GROUP BY and ORDER BY is such a pain in the behind. Almost all our non-trivial queries have to be nested multiple levels due to this, which doesn't exactly help readability.
    - bavell 3y ago
      Ugh, I'll be having to do this same migration soon for one of my clients. Not looking forward to it. Good to know!
  - fbdab103 3y ago
    I would love if base SQL were improved. On the other hand, we are just slapping lipstick on a pig. It is already a gargantuan mess which needs a path to replacement. Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.
  - chrisjc 3y ago
    Agreed... Their SQL is awesome and seemingly only getting better. My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance. > The three window functions will also share the data layout, which will improve performance. https://duckdb.org/docs/sql/window_functions#window-clauses https://duckdb.org/docs/sql/window_functions#window-clauses I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.
- ttfkam 3y ago
  Because the DuckDB folks used the Postgres query parser. It's been an alternative in Postgres for decades.
ttfkam 3y ago
In Postgres, it's TABLE foo LIMIT 100; No SELECT with columns and no FROM keyword.

idoubtit 3y ago

> 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