SQL language proposal: JOIN FOREIGN • Hacker News Board

Re: SQL language proposal: JOIN FOREIGN

#131

Post by Tostino » Thu, Dec 30, 2021, 10:30 PM UTC

JoelJacobson wrote: post #74

jimbobmcgee wrote: post #64

Earlier quoted context omitted.

I'm often interested into what goes into changes to committee-driven standards. To an outsider, proposing a change seems to require one to be part of a shady cabal of Big-5 employees, skilled in the art of hiding subtle, privacy-invading features into inscrutable, plain-text RFCs. That or subjecting yourself to 30K+ what-abouters who deform your suggestion into something unrecognisable. It's refreshing to see a strai…

> To an outsider, proposing a change seems to require one to be part of a shady cabal of Big-5 employees, skilled in the art of hiding subtle, privacy-invading features into inscrutable, plain-text RFCs. I thought so too. Initially I just tried to get in contact with someone at the Swedish Institute for Standards (SIS), to see if it would be possible to send a proposal to someone in the SQL committee, which I thought…

I was following this proposal on the -hackers thread, glad to see it getting traction here. I find it really interesting to read that, it'll be even more interesting to see how it plays out longer term. I'd love to see some more progress made with the SQL language / syntax, and having someone in the community actively engaging with the committees seems like a great way forward.

Re: SQL language proposal: JOIN FOREIGN

#132

Post by Guest42 » Thu, Dec 30, 2021, 10:30 PM UTC

Tostino wrote: post #123

Guest42 wrote: post #78

Is this not something that should be written at the app level rather than dml?

Foreign keys in general? I am not sure I understand what you mean...

Not something I’ve ever thought about but I think that it could be done via macro,

I have written tsql functions in c# and imagine that other dialects have analogous functionality.

It seems as though the point is to cut down on sql code, it’d also be possible to query the foreign keys and create a data structure that could feed a function for joins.

I haven’t thought out the specifics but think this type of approach would be more practical than changing the sql standard.

Re: SQL language proposal: JOIN FOREIGN

#133

Post by slt2021 » Thu, Dec 30, 2021, 10:31 PM UTC

not a good idea, constaints are one thing and joins are another thing. plus I can join on conditions other than =.

the problem that author is trying to solve can be easily solved by a view:

1. Declare a view with all necessary JOINs once

2. select from view only what you need, aggregate what you want

3. Optimizer will throw out unnecessary stuff and optimize query while all JOIN logic will be declared only once and will be hidden inside the view

plus each DBMS has its own flavor of SQL and will have its own query optimizer nuances when dealing with joins, especially nested via CTEs/views/lateral queries,etc.

Re: SQL language proposal: JOIN FOREIGN

#134

Post by JoelJacobson » Thu, Dec 30, 2021, 10:37 PM UTC

matteote wrote: post #93

This reminds me of KEY JOIN in Sybase which, unlike this syntax, does not require to specify the foreign key. I find Sybase's syntax to be too implicit, and challenging to port to other dialects; JOIN FOREIGN with explicit PK reference looks much better. I wonder how it is expected to work with non-table references (views, CTEs, subqueries), especially when the columns involved in the foreign key (on either side) are…

> I wonder how it is expected to work with non-table references (views, CTEs, subqueries)

It’s not, it’s only for the special but common case of joining two tables based on a foreign key.

Re: SQL language proposal: JOIN FOREIGN

#135

Post by DevKoala » Thu, Dec 30, 2021, 10:39 PM UTC

JoelJacobson wrote: post #85

Upitor wrote: post #61

In my opinion this proposal seems only to consider simple cases, but there are many not-so-simple relationsship types: Consider a ‘sales’ table which includes columns [time] and [sold_by_employee_id], and a periodized ‘employee’ table which includes columns [employee_id], [valid_from] and [valid_to] columns. There is a perfectly valid relationsship between the two tables, but you cant join them using only equal-state…

Nice example! The join you describe would remain as a JOIN ON. This is per design. Quote from the proposal: "The idea is to improve the SQL language, specifically the join syntax, for the special but common case when joining on foreign key columns." ... "If the common simple joins (when joining on foreign key columns) would be written in a different syntax, the remaining joins would visually stand out and we could fo…

But how could you accurately tell if some queries join on the foreign key, but were written by someone without knowledge of the new specification?

Re: SQL language proposal: JOIN FOREIGN

#136

Post by Tostino » Thu, Dec 30, 2021, 10:42 PM UTC

Guest42 wrote: post #132

Tostino wrote: post #123

Earlier quoted context omitted.

Foreign keys in general? I am not sure I understand what you mean...

Not something I’ve ever thought about but I think that it could be done via macro, I have written tsql functions in c# and imagine that other dialects have analogous functionality. It seems as though the point is to cut down on sql code, it’d also be possible to query the foreign keys and create a data structure that could feed a function for joins. I haven’t thought out the specifics but think this type of approach…

At that point you are just building a feature into an "ORM" of some sort. The entire point is to allow queries to be written in a more concise way for this simple equality join use case which is (total ballpark figure) ~half of the joins in my system.

Re: SQL language proposal: JOIN FOREIGN

#137

Post by to11mtm » Thu, Dec 30, 2021, 10:42 PM UTC

JoelJacobson wrote: post #101

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

Speaking as someone who has used ORMs in the past and contributes to a LINQ Micro ORM...

It might make tooling 'easier', but since backwards compatibility has to be considered the actual value add is questionable IMO.

Most ORMs/MicroORMs will have tooling that sniffs out the DB Schema including foreign keys, and if you are using those bits (i.e. 'not hand written') most will do the right thing today. I suppose you could include some extra syntax for whatever DSL you're providing users....

IDK. Speaking as someone who is very comfortable in SQL, This feels more like syntactic sugar than anything else.

Re: SQL language proposal: JOIN FOREIGN

#138

Post by JoelJacobson » Thu, Dec 30, 2021, 10:47 PM UTC

zmmmmm wrote: post #89

JoelJacobson wrote: post #41

Earlier quoted context omitted.

There is similar comment in the thread, suggesting both tables should always be specified. Not sure what I prefer yet. The idea is the foreign key name is usually the same as the referenced table, so should be an infrequent problem.

> The idea is the foreign key name is usually the same as the referenced table I think this is where you are hitting some of your turbulence here because lots of ORMs / schema management tools actually generate completely cryptic fk names (sometimes based on the hash of the columns or similar). Personally I think weighing in legacy baggage like that too highly is a bad thing as it creates enormous inertia.

Thanks for explaining, I have no experience of ORMs, always written my queries manually. This was insightful. Thanks also for the word “inertia”, good one, will add to my vocabulary.

Re: SQL language proposal: JOIN FOREIGN

#139

Post by tapas73 » Thu, Dec 30, 2021, 10:48 PM UTC

mrweasel wrote: post #36

hn_throwaway_99 wrote: post #19

Earlier quoted context omitted.

No, my concern is not that you can also specify the table name that explicitly states the name of both tables, it's that you have proposed a syntax where it is possible to leave off the explicit table name. If you got rid of the syntax that uses the underlying "magic" of needing to know which table the foreign key points to, I'd be more amenable.

Yeah, my concern is people in the real world. If you can "magically" join on a foreign key and leave the table name out, or still call the thing anything you want, some smart ass will abuse the live crap out of it and I'll somehow be left to deal with it. I like things to be explicit. Tell me what you're joining and how you want to join it. What is the use case for this? Other than saving some typing, and let's face…

First, very valid concern regarding table name.

> Other than saving some typing,

I would like to point out the corectness aspect of the proposal.

Today foreign keys are enforced during inserts, updates, deletes. (you just can't violate fk. this is so good) But you can violate it in selects. (e.g. mistype column name, forget to include a column)

This proposal (or its adjustment) would allow to use fk also during select. It's like static typing for join conditions.

Re: SQL language proposal: JOIN FOREIGN

#140

Post by Sesse__ » Thu, Dec 30, 2021, 10:49 PM UTC

slt2021 wrote: post #133

not a good idea, constaints are one thing and joins are another thing. plus I can join on conditions other than =. the problem that author is trying to solve can be easily solved by a view: 1. Declare a view with all necessary JOINs once 2. select from view only what you need, aggregate what you want 3. Optimizer will throw out unnecessary stuff and optimize query while all JOIN logic will be declared only once and w…

Your #3 is very optimistic. You can't just remove a join (be it inner, left, or really any other type) just because you didn't refer to any fields from one of the sides, and it's even harder when aggregation is in the mix. There are cases where you can without influencing the result, but they are special-case optimizations and not universally supported across databases.