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

101–110 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#101

Post by JoelJacobson »

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…

It would indeed be difficult to remember, but the proposal also suggest changing the default naming convention for foreign keys, to give them the same name as the referenced table.

If using an ORM, I would guess this proposal isn't useful, since then you wouldn't hand-write queries anyway, right? Except when you want to override the queries generated by the ORM? (I'm not an ORM user myself.)

Re: SQL language proposal: JOIN FOREIGN

#102

Post by zeroimpl »

magicalhippo wrote: post #43
jmull wrote: post #15

Earlier quoted context omitted.

Agreed. Maybe something like this instead: SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN distributors d ON FOREIGN f.films_did_fkey and SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d JOIN films f ON FOREIGN films_did_fkey

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.

Re: SQL language proposal: JOIN FOREIGN

#103

Post by lolive »

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.

Re: SQL language proposal: JOIN FOREIGN

#104

Post by JoelJacobson »

mpolichette wrote: post #90
tqi wrote: post #42

Personally, I think SQLs verbose syntax is a good thing, and increases clarity/interpretability. It reduces cognitive load to have things explicitly listed out. Not having the table and column names in the query itself makes it much harder to read and understand queries without prior knowledge of the data model.

I'm with you on this one. I prefer it to be in your face simple to understand over the terse-ness. In the gist example, I actually prefer the SQL-92 approach where we are joining given an explicit comparison condition. Every other implementation seems to be trying to hide details, for what gain? Less typing? In order to use FOREIGN, you will need to know not just what columns a table has, but also their configuration…

> I don't see a lot of harm from adding this syntax however as people are free to not use it and it relies on an existing strict convention.

This is a good argument I will add to the list.

Also interesting to read about un-enforced foreign keys. I haven't used MSSQL myself, the DB in which I heard it's possible, I've only been using PostgreSQL for the last 20 years, and before that MySQL.

I think the problems you describe is an argument against a WITH NOCHECK feature, since it could be misused. Maybe it's necessary in some databases still, but at least in PostgreSQL, the FOR KEY SHARE lock solved all the issues with concurrent updates we had at Trustly. The FOR KEY SHARE was a huge patch [1] written mainly by Alvaro Herrera. Thanks to it, Trustly has never since had any performance problems with foreign keys, and they have AFAIK not needed to drop any foreign keys up until today due to locking/performance problems.

[1] https://www.commandprompt.com/blog/fixing_foreign_key_deadlo...

Re: SQL language proposal: JOIN FOREIGN

#105

Post by benjiweber »

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…

"group by 1,2,3,4,5,6" is at least a little more consise.

Re: SQL language proposal: JOIN FOREIGN

#106

Post by salawat »

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.

...I do... All the time. I'm cleaning up a mess of a namespace collision as we speak. I was the only one who saw it coming. I've spent the better part of a month getting people to believe it's an issue.

Re: SQL language proposal: JOIN FOREIGN

#107

Post by edgyquant »

polygotdomain wrote: post #97
blondin wrote: post #75

Earlier quoted context omitted.

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.

What about SELECT * WITHOUT {columns_you_do_not_want} FROM... EXCEPT is already a keyword and has is used for set-based operations, so I don't think it's good to overload it.

I like this fwiw

Re: SQL language proposal: JOIN FOREIGN

#109

Post by dragonwriter »

piaste wrote: post #28
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).

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.

What I really want is a special phrase MINIMAL GROUPING such that GROUP BY MINIMAL GROUPING includes exactly the items that it would be an error not to include in the GROUP BY.

Re: SQL language proposal: JOIN FOREIGN

#110

Post by exabrial »

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…

I think this is an operator problem. You're using the wrong tool for the job.

TablePlus, SequelAce, the official MySQL client all support cntrl-space autocompletion. I wish we used Postgres, but I imagine the landscape is the same. The big box databases like Oracle, DB2 undoubtedly having this tooling as well.

That being said, here is our fk naming convention: `fk-asset_types->asset_categories` which pretty states what's going on and is easy to remember.

Post reply on HN