5 ms·

I really like SQL, and think its one of the most important languages a dev needs to learn (all you managers, you could learn some SQL too!). However to syntax i

I really like SQL, and think its one of the most important languages a dev needs to learn (all you managers, you could learn some SQL too!). However to syntax is often very restrictive. As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. When working with big tables, its super annoying to select all but one column. So why not have syntax for this? Eg SELECT !username from users could expand to all columns without username. I feel SQL has so many low hanging fruits from a user perspective, but its very slow moving, and the standard is even more slow to make progress. So SQL with sugar that compiles down to vanilla SQL has a market imho. I will study PRQL more and see if i could use it on a daily basis.

whateveracct 4y ago
> As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. Haskell's esqueleto SQL eDSL actually enables just this BUT acting like this is a big deal is Wadler's Law for sure.
- phplovesong 4y ago
  Sure, multiple ORMs and query builders allow this, but when writing pure SQL thats dynamic its annoying to have to check for some condition before knowing "Should i use WHERE or AND next". This is traditionally solved with an hard coded WHERE 1 = 1 in the beginning of the query. Still its a wart, even tho a small one. Point being, there are many other small things like this that (can) make SQL a burden, eg the select all but <n+1> columns.
mmsimanga 4y ago
DuckDb has the feature to exclude columns in the SELECT statement [0] [0]https://duckdb.org/2022/05/04/friendlier-sql.html https://duckdb.org/2022/05/04/friendlier-sql.html
snthpy 4y ago
> As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. PRQL does this: from employees filter country_code == "USA" # Each line transforms the previous result. filter age > 50 compiles to: SELECT * FROM employees WHERE country_code = 'USA' AND age > 50 -- Generated by PRQL compiler version 0.3.1 (https://prql-lang.org)
- Sammyadems1 4y ago
  What about an OR in the where? or Parens with multibuls ANDs and ORs
snthpy 4y ago
> Eg SELECT !username from users could expand to all columns without username. This should be landing in PRQL soon. See ongoing discussion here: [an exclude clause, that would select all columns except whats specified #172](https://github.com/PRQL/prql/issues/172 https://github.com/PRQL/prql/issues/172) The syntax we seem to be converging to is like you suggested: select ![foo,bar] would select all columns except "foo" and "bar". The aim is to support this at least for SQL dialects that support it like DuckDB, BigQuery, ... For other dialects we might just error out initially. There is a goal of supporting working connected to a live database in which case the source table schema could be read and the appropriate SELECT clause generated. This would not be robust to schema changes such as future column additions though. At the moment the SQL is generated in an offline setting though so this is not possible.
- phplovesong 4y ago
  Thats nice to know! If you are a PRQL dev i have a suggestion for you/your team. Make PRQL language agnostic. In a real life scenario many people are writing SQL not in the app layer, but more ad-hoc for stats, reporting, analysis, examining the data etc. What i mean by this is: If i write an app that has embedded SQL i dont really care to much for the possible verbosity. This code will be used more than once, so im OK with taking the extra effort. Also all languages has an query-builder / ORM so the benefit of something like PRQL is possibly not big enough to merit it as an additional dependency. My suggestion: Make PRQL a cli tool that can be used by allowing users to connect to a database in a similar fashion as something like usql (https://github.com/xo/usql https://github.com/xo/usql), I would find the most power in a tool like PRQL is this setting: Ad hoc SQL queries. This would open the editor integration. With a tool like this i could open vim, and write PQRL in a window, and pipe it via something like Slime to a open PQRL session. This would be pure money!
  - aerzen 4y ago
    So something like a language server, but connected to a database and able to execute queries? That's an interesting idea. While working on the compiler, I try to separate different components so things like this are possible. Currently, we are focusing on the language itself and also put some though into intermediate representation of the query (RQ - relational query). We are conservative about expanding the scope, because building database connectors and execution engines would take too much focus off the language and the compiler. But I'd lie if I said that I don't want to dig into it.
albertopv 4y ago
SQL was meant to be like readable english, so two or more WHERE didn't made sense. Also select all columns but one may not be future proof is column are added or removed, you really don't know what columns that query is going to read, useful for oneshot query, but not much more.
- phplovesong 4y ago
  Meh, thats not a valid argument. What happens if i `select id, name from users` and later someone drops the name column. Similarly if i `select * from users` and use name the same problem/bug arises. Dropping a column without the application code refactor is obviously a developer issue, not a SQL one.
  - ilitirit 4y ago
    You're right that it's a "dev/dba problem", but the reason for not using `select !<column>` is exactly the same as not using `select *`. If you specify exactly what you want, there's less of a chance of your ORM-based app breaking because it received an extra or too few columns. I'm sure most people who work with SQL DBs have been bitten this before. Also, while it could also be useful, over the years I've started moving away from things like using `!` to negate things. It makes it slightly harder to read and it ruins the symmetry of my code. And lord knows SQL doesn't need any more of that. It's probably the only language I use often where I have to place my commas at the start of the line; where I need to add a first filter as `1 = 1` etc just to make it easier to comment out things quickly. What I personally want most is the ability to move the FROM clause to the top (this is a no-brainer) and better aliasing rules. The alias I used in my SELECT should be accessible everywhere in the statement.
  - albertopv 4y ago
    Select * is something no one should be doing in real code. Select [list of columns] should the way to query and doing so you know exactly what columns are read. That's one of the reasons I dislike ORM like hibernate. I advocate the least surprises principle, select * or select !column are doors to surprises. What if someone add a geometry column with tons of data (real example happened to me)? You are going to read tons of data that probably you don't need.
jj_ 4y ago
In BigQuery you can write: SELECT * EXCEPT (username) FROM users https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_except https://cloud.google.com/bigquery/docs/reference/standard-sq...
tome 4y ago
> When working with big tables, its super annoying to select all but one column What's the use case for that? In fact, I can hardly understand the benefit of projecting at all, except at the very end. Why would you want to do it? Is there a performance benefit?
- chaps 4y ago
  I would love this addition in SQL. My work is largely exploratory with SQL. 99% of my queries will never get saved anywhere. At the end of the day it's just syntactic sugar, but sometimes when writing reports you sometimes don't want to include a single column like a sequential key, because it's not relevant information to the end user or exploration. Another reason is that some text columns are long enough that just absolutely destroy readability in things like psql. Often I just want to get a feel for what's in the data and removing those annoying text fields to make psql readable is a pain. You have to run a `select * from table limit 0`, copy that into a text file, then `s/ +\| +/, /g` the columns list, then remove the annoying field... then throw it back into your query. Takes about 45 seconds to do all that and that adds up!
  - tome 4y ago
    Right, I understand why you'd want to project at the very end of the query, i.e. suppress some columns from being shown. Other than that, I don't see the point, unless there's a performance impact. Keeping columns around doesn't actually change the result of the query in any way.
    - chaps 4y ago
      You've definitely missed the point because you're concerned about the "very end of the query". I'm describing both a reporting and more importantly as far as I'm concerned an explorative process that's disrupted by the silly need to list out all columns. Performance is irrelevant.
      - tome 4y ago
        Yes, I know I've missed the point. That's why I asked my question in the first place! My question was: > What's the use case for that? In fact, I can hardly understand the benefit of projecting at all, except at the very end. Why would you want to do it? Is there a performance benefit? and I meant it literally. It's not an oblique, rhetorical way of saying "you don't need to do this". It was a genuine question! You've answered that it's convenient when doing exploratory work. Thank you. That helps me understand better.
michaelcampbell 4y ago
> why would WHERE clauses need to have a AND instead of multiple WHERE clauses Semi related. Python's for comprehensions allow multiple if's. some_list = [ x for x in things if x % 2 == 0 if my_function(x) > 0 if some_other_global_predicate() ... ] And this is shown in the python docs. I've had PR's rejected for using this since they wanted "and". Drove me up a wall as this IMO reads as easily if not more easily than the "and" version, and (again, IMO) is more for humans where the "&&" is more for the computer.
aabbcc1241 4y ago
I made a DSL that compiles to SQL. It allows multiple where statements, and you can write where, having, group by, limit and orders in any other. You can also write select statement in nested syntax similar to graphql, and have it auto generate join table statements. You can use it as a npm package or try it on this playground https://better-sql.surge.sh https://better-sql.surge.sh

whateveracct 4y ago

> As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. Haskell's esqueleto SQL eDSL actually enables just this BUT acting like this is a big deal is Wadler's Law for sure.

phplovesong 4y ago
Sure, multiple ORMs and query builders allow this, but when writing pure SQL thats dynamic its annoying to have to check for some condition before knowing "Should i use WHERE or AND next". This is traditionally solved with an hard coded WHERE 1 = 1 in the beginning of the query. Still its a wart, even tho a small one. Point being, there are many other small things like this that (can) make SQL a burden, eg the select all but <n+1> columns.

mmsimanga 4y ago

DuckDb has the feature to exclude columns in the SELECT statement [0] [0]https://duckdb.org/2022/05/04/friendlier-sql.html https://duckdb.org/2022/05/04/friendlier-sql.html

snthpy 4y ago

> As an example why would WHERE clauses need to have a AND instead of multiple WHERE clauses. PRQL does this: from employees filter country_code == "USA" # Each line transforms the previous result. filter age > 50 compiles to: SELECT * FROM employees WHERE country_code = 'USA' AND age > 50 -- Generated by PRQL compiler version 0.3.1 (https://prql-lang.org)

Sammyadems1 4y ago
What about an OR in the where? or Parens with multibuls ANDs and ORs

snthpy 4y ago

> Eg SELECT !username from users could expand to all columns without username. This should be landing in PRQL soon. See ongoing discussion here: [an exclude clause, that would select all columns except whats specified #172](https://github.com/PRQL/prql/issues/172 https://github.com/PRQL/prql/issues/172) The syntax we seem to be converging to is like you suggested: select ![foo,bar] would select all columns except "foo" and "bar". The aim is to support this at least for SQL dialects that support it like DuckDB, BigQuery, ... For other dialects we might just error out initially. There is a goal of supporting working connected to a live database in which case the source table schema could be read and the appropriate SELECT clause generated. This would not be robust to schema changes such as future column additions though. At the moment the SQL is generated in an offline setting though so this is not possible.

phplovesong 4y ago
Thats nice to know! If you are a PRQL dev i have a suggestion for you/your team. Make PRQL language agnostic. In a real life scenario many people are writing SQL not in the app layer, but more ad-hoc for stats, reporting, analysis, examining the data etc. What i mean by this is: If i write an app that has embedded SQL i dont really care to much for the possible verbosity. This code will be used more than once, so im OK with taking the extra effort. Also all languages has an query-builder / ORM so the benefit of something like PRQL is possibly not big enough to merit it as an additional dependency. My suggestion: Make PRQL a cli tool that can be used by allowing users to connect to a database in a similar fashion as something like usql (https://github.com/xo/usql https://github.com/xo/usql), I would find the most power in a tool like PRQL is this setting: Ad hoc SQL queries. This would open the editor integration. With a tool like this i could open vim, and write PQRL in a window, and pipe it via something like Slime to a open PQRL session. This would be pure money!
- aerzen 4y ago
  So something like a language server, but connected to a database and able to execute queries? That's an interesting idea. While working on the compiler, I try to separate different components so things like this are possible. Currently, we are focusing on the language itself and also put some though into intermediate representation of the query (RQ - relational query). We are conservative about expanding the scope, because building database connectors and execution engines would take too much focus off the language and the compiler. But I'd lie if I said that I don't want to dig into it.

albertopv 4y ago

SQL was meant to be like readable english, so two or more WHERE didn't made sense. Also select all columns but one may not be future proof is column are added or removed, you really don't know what columns that query is going to read, useful for oneshot query, but not much more.

phplovesong 4y ago
Meh, thats not a valid argument. What happens if i `select id, name from users` and later someone drops the name column. Similarly if i `select * from users` and use name the same problem/bug arises. Dropping a column without the application code refactor is obviously a developer issue, not a SQL one.
- ilitirit 4y ago
  You're right that it's a "dev/dba problem", but the reason for not using `select !<column>` is exactly the same as not using `select *`. If you specify exactly what you want, there's less of a chance of your ORM-based app breaking because it received an extra or too few columns. I'm sure most people who work with SQL DBs have been bitten this before. Also, while it could also be useful, over the years I've started moving away from things like using `!` to negate things. It makes it slightly harder to read and it ruins the symmetry of my code. And lord knows SQL doesn't need any more of that. It's probably the only language I use often where I have to place my commas at the start of the line; where I need to add a first filter as `1 = 1` etc just to make it easier to comment out things quickly. What I personally want most is the ability to move the FROM clause to the top (this is a no-brainer) and better aliasing rules. The alias I used in my SELECT should be accessible everywhere in the statement.
- albertopv 4y ago
  Select * is something no one should be doing in real code. Select [list of columns] should the way to query and doing so you know exactly what columns are read. That's one of the reasons I dislike ORM like hibernate. I advocate the least surprises principle, select * or select !column are doors to surprises. What if someone add a geometry column with tons of data (real example happened to me)? You are going to read tons of data that probably you don't need.

jj_ 4y ago

In BigQuery you can write: SELECT * EXCEPT (username) FROM users https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_except https://cloud.google.com/bigquery/docs/reference/standard-sq...

tome 4y ago

> When working with big tables, its super annoying to select all but one column What's the use case for that? In fact, I can hardly understand the benefit of projecting at all, except at the very end. Why would you want to do it? Is there a performance benefit?

chaps 4y ago
I would love this addition in SQL. My work is largely exploratory with SQL. 99% of my queries will never get saved anywhere. At the end of the day it's just syntactic sugar, but sometimes when writing reports you sometimes don't want to include a single column like a sequential key, because it's not relevant information to the end user or exploration. Another reason is that some text columns are long enough that just absolutely destroy readability in things like psql. Often I just want to get a feel for what's in the data and removing those annoying text fields to make psql readable is a pain. You have to run a `select * from table limit 0`, copy that into a text file, then `s/ +\| +/, /g` the columns list, then remove the annoying field... then throw it back into your query. Takes about 45 seconds to do all that and that adds up!
- tome 4y ago
  Right, I understand why you'd want to project at the very end of the query, i.e. suppress some columns from being shown. Other than that, I don't see the point, unless there's a performance impact. Keeping columns around doesn't actually change the result of the query in any way.
  - chaps 4y ago
    You've definitely missed the point because you're concerned about the "very end of the query". I'm describing both a reporting and more importantly as far as I'm concerned an explorative process that's disrupted by the silly need to list out all columns. Performance is irrelevant.
    - tome 4y ago
      Yes, I know I've missed the point. That's why I asked my question in the first place! My question was: > What's the use case for that? In fact, I can hardly understand the benefit of projecting at all, except at the very end. Why would you want to do it? Is there a performance benefit? and I meant it literally. It's not an oblique, rhetorical way of saying "you don't need to do this". It was a genuine question! You've answered that it's convenient when doing exploratory work. Thank you. That helps me understand better.

michaelcampbell 4y ago

> why would WHERE clauses need to have a AND instead of multiple WHERE clauses Semi related. Python's for comprehensions allow multiple if's. some_list = [ x for x in things if x % 2 == 0 if my_function(x) > 0 if some_other_global_predicate() ... ] And this is shown in the python docs. I've had PR's rejected for using this since they wanted "and". Drove me up a wall as this IMO reads as easily if not more easily than the "and" version, and (again, IMO) is more for humans where the "&&" is more for the computer.

aabbcc1241 4y ago

I made a DSL that compiles to SQL. It allows multiple where statements, and you can write where, having, group by, limit and orders in any other. You can also write select statement in nested syntax similar to graphql, and have it auto generate join table statements. You can use it as a npm package or try it on this playground https://better-sql.surge.sh https://better-sql.surge.sh