Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL language proposal: JOIN FOREIGN

gist.github.com

Post reply on HN
Print view View original

191–200 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#191

Post by JoelJacobson »

hn_throwaway_99 wrote: post #5

This is my "Thanks, I hate it" response. Reason being if you use the example they gave: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.films_did_fkey d You need to implicitly know the table that films_did_fkey points to, because 'd' is just a table alias. I can't think of anywhere else in the SQL standard where you can introduce a table alias without explicitly referencing the table. I…

Insightful comment, thanks.

It has been added to the "Drawbacks / Tradeoffs / Remaining issues" section of the proposal.

https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cf...

Re: SQL language proposal: JOIN FOREIGN

#192

Post by yunohn »

bradleyjg wrote: post #161
JoelJacobson wrote: post #159

Earlier quoted context omitted.

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement (as distinct from DDL statements where such names can already appear). That's not allowed. Actually, constraint names already do appear in some DQL statements, such as the quite recently added INSERT INTO ... ON CONFLICT in PostgreSQL [1] INSERT INTO ... ON CONFLICT [ conflict_ta…

PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.

> PostgresSQL isn’t SQL though

What is SQL acc. to you? MS-SQL, MySQL, SQLITE, Bigquery “Standard SQL”?

Re: SQL language proposal: JOIN FOREIGN

#193

Post by icambron »

oweiler wrote: post #33
piaste wrote: post #28

Earlier quoted context omitted.

Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.

Can't you use an alias in a group by?

Some DBs let you do this and it's helpful when one of the things you're selecting is some complex scalar expression, but you still have to list each item in the group by

Re: SQL language proposal: JOIN FOREIGN

#194

Post by icambron »

tpetry wrote: post #152
orthoxerox wrote: post #94

Earlier quoted context omitted.

No. What icambron and I would like is something that can get rid of this group by: select division_name, branch_name, dealer_id, dealer_name, quarter, month, sum(total_paid) from divsions, branches, dealers, transactions where ..... --buncha joins group by division_name, branch_name, dealer_id, dealer_name, quarter, month If I didn't want to group the result by division_name, branch_name, dealer_id, dealer_name, quar…

In PostgreSQL you can GROUP BY on the primary key so you can omit specifying all other columns

I remember when I discovered this-- very helpful feature and I am happy it's there (and sad most DBs don't have it). But if I'm grouping I'm almost necessarily not grouping on at least one of the tables' PKs, so it only goes so far

Re: SQL language proposal: JOIN FOREIGN

#195

Post by bradleyjg »

yunohn wrote: post #192
bradleyjg wrote: post #161

Earlier quoted context omitted.

PostgresSQL isn’t SQL though. Vendor extensions have done all kinds of inelegant or even outright ill advised things from the beginning.

> PostgresSQL isn’t SQL though What is SQL acc. to you? MS-SQL, MySQL, SQLITE, Bigquery “Standard SQL”?

Standards published by ISO/IEC.

Re: SQL language proposal: JOIN FOREIGN

#197

Post by icambron »

Sesse__ wrote: post #114
orthoxerox wrote: post #94

Earlier quoted context omitted.

No. What icambron and I would like is something that can get rid of this group by: select division_name, branch_name, dealer_id, dealer_name, quarter, month, sum(total_paid) from divsions, branches, dealers, transactions where ..... --buncha joins group by division_name, branch_name, dealer_id, dealer_name, quarter, month If I didn't want to group the result by division_name, branch_name, dealer_id, dealer_name, quar…

What if you do SELECT a + b, SUM(x) FROM t1? Should the implicit GROUP BY be on (a,b) or (a+b)?

On a+b. I'm suggesting it simply copies all the non-aggregate select pieces to the group by clause. If I want to group on a and b, I could either select them too or add them to the group by (I'm imagining something like "GROUP BY SELECT, a, b"). Note that selecting them is the natural thing you'd do if you wanted that grouping, because otherwise you wouldn't know what the grouping is when you got the results.

Re: SQL language proposal: JOIN FOREIGN

#199

Post by yunohn »

bradleyjg wrote: post #198
yunohn wrote: post #196

Earlier quoted context omitted.

Sure, but where is that pure standard implemented?

Why does it need to be implemented somewhere?

What do you mean?

If the ISO SQL is not implemented by any SQL DB, and you denounce PostgreSQL/etc version of SQL - what is left over?

Re: SQL language proposal: JOIN FOREIGN

#200

Post by dragonwriter »

slotrans wrote: post #153

This is not actually compatible with SQL semantics. An important constraint on SQL is that a query must run, and produce correct results, relying only on the structure and content of tables . Indexes (can) make queries faster but must not inhibit, or be required for, correctness. The same is true of primary key constraints, foreign key constraints, check constraints, defaults, triggers, partitioning, whether a table…

> This proposal would break that constraint, by elevating the _name_ of a foreign key constraint to an identifier usable in a DQL statement

This doesn't break that constraint in an SQL RDBMS that also implements the relational model, since it is a fundamental element of the relational model that schema metadata is stored as data, and therefore constraint specifications, including names, are included within “content of tables”.

Post reply on HN