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

111–120 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#111

Post by coderzach »

icambron wrote: post #32
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.

I wish it just had a "group by all the stuff I selected without aggregation" shortcut

yeah, this is the right answer. Are there any examples where this isn't what you want? I guess GROUPING SETS and ROLLUP, but those could be special.

Re: SQL language proposal: JOIN FOREIGN

#112

Post by recursive »

blondin wrote: post #75
hnthrowaway0315 wrote: post #6

What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).

i would rather it written as SELECT * BUT or SELECT * EXCEPT or even SELECT ALL BUT. SQL has always been that language that is easy to read. even when you don't understand what the queries are doing. adding a cryptic syntax like "-column" would make it less readable.

The given syntax is obviously a no-go. It's already unary minus.

Re: SQL language proposal: JOIN FOREIGN

#113

Post by degenerate »

thom wrote: post #16

Of all the myriad indignities of SQL, this isn't near the top of my list. I also don't like making the names of objects like foreign keys and indexes first class concerns in your queries, that's a whole new layer of cognitive overhead.

Agree. I'd much prefer they worked on the way MySQL determines what indexes to use on a multi-table join, so there could be less emphasis on the need to ANALYZE TABLE and FORCE INDEX when the DB is seemingly being dumb, but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up. Troubleshooting full table scans on large sets of data is a nightmare.

Re: SQL language proposal: JOIN FOREIGN

#114

Post by Sesse__ »

orthoxerox wrote: post #94
xyzzy_plugh wrote: post #82

Earlier quoted context omitted.

Isn't this just SELECT DISTINCT ...?

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)?

Re: SQL language proposal: JOIN FOREIGN

#115

Post by criticaltinker »

lolive wrote: post #103

Coming from the graph database / semantic web area, I would propose foreign key/ primary key relationships in the DB to be detailed and named in a schema description, and then queries reference those relationships by name to define the needed joins.

Yup totally agree - OP is an interesting proposal and has provoked a lot of quality commentary regarding the tradeoffs of clarity vs conciseness, implicit vs explicit, FK performance, etc.

But IMO you’ve raised the important long term consideration - do graph based schemas and query languages obviate the need to model foreign keys explicitly? If this JOIN FOREIGN proposal is an incremental step forward, what’s the next big leap?

Re: SQL language proposal: JOIN FOREIGN

#116

Post by gavinray »

cpill wrote: post #72
benjiweber wrote: post #59

Earlier quoted context omitted.

NATURAL JOIN is great because it's like a relational AND. How else will you join with Table Dee and Table Dum? https://twitter.com/benjiweber/status/1476629550608101384 It's just risky if you don't design your schema with relational algebra in mind.

I'm 20 years I have never seen anyone use relational algebra. schemas have to be self documenting albeit only in the real world.

SQL is relational algebra, the core of relational algebra is Selection, Projection, Cross-products, Unions, and Set Differences.

SQL is a language that implements Relational Algebra/Relational Calculus

Re: SQL language proposal: JOIN FOREIGN

#117

Post by rtpg »

Smotko wrote: post #79

It might be just me, but I feel like remembering the foreign key name is more difficult than remembering the columns that you need in the ON clause. Especially since you can usually find the column names by just seeing the data in the table (select * from x) wheres seeing the foreign key names is much harder (show create table x?). Also, if you use an ORM it will usually generate foreign key names that are almost imp…

In a universe where foreign key index names are important we would specify better names.

I think stuff like “documents_by_user” as foreign key names and explicit index usage would improve peoples awareness of how indices get used and would generally be a positive

Re: SQL language proposal: JOIN FOREIGN

#118

Post by magicalhippo »

zeroimpl wrote: post #102
magicalhippo wrote: post #43

Earlier quoted context omitted.

I had a similar suggestion the other day[1], except I chose what to me sounded more English-sounding: JOIN films f USING FOREIGN KEY Here the explicit foreign key, films_did_fkey in this case, could be specified after FOREIGN KEY. This would be similar in syntax to when you force an index for a select statement, at least in the DB we use. [1]: https://news.ycombinator.com/item?id=29687565

Yeah I think something like this is the way to go. The table name needs to be listed in a consistent manner like all other joins, and I don't think the syntax should be different depending on which order the tables are listed in. With the above proposal, it seems the foreign key name could be left out in the common case of there being only one fkey between the two tables too.

> With the above proposal, it seems the foreign key name could be left out in the common case of there being only one fkey between the two tables too.

Yes, this was indeed my intention. The common case with only a single matching foreign key constraint would not require being explicit, but one could be if needed in a natural way.

    JOIN films f USING FOREIGN KEY films_did_fkey

Re: SQL language proposal: JOIN FOREIGN

#119

Post by tapas73 »

btilly wrote: post #17

Please, no. A common problem in data warehousing is that a design with lots of foreign keys becomes slow to load. A standard solution is to move the checks for referential integrity elsewhere, then drop the foreign key constraint. This massively improves load performance. This syntax change means that this solution can't be used because you have no idea what random queries out there might rely on the specific existen…

Lets have disabled FK then.

postgre, seems to not have it, but the proposal could include also "disabled FK" part.

teradata, oracle, sql server already have option for FK "disable/no check".

https://docs.teradata.com/r/eWpPpcMoLGQcZEoyt5AjEg/df1PvVh6e... https://docs.microsoft.com/en-us/sql/relational-databases/ta... https://docs.oracle.com/cd/B28359_01/server.111/b28310/gener...

Re: SQL language proposal: JOIN FOREIGN

#120

Post by antender »

I, personally, feel like this is a rather pointless addition to an already somewhat bloated language. It it isn't saving that much typing (we have NATURAL JOIN's already, nobody is using them), is kind of inconsistent with other language principles (we should be able to join anything to anything if we need to, see SQL-89) and limits JOIN flexibility (you don't have to join with "=" operator, JOIN's supports different types of conditions and are equivalent to WHERE clause)
Post reply on HN