News for hackers. Stuff that matters.
Re:SQL language proposal: JOIN FOREIGN by hn_throwaway_99 on Thursday December 30, 2021 @01:42PM (#29739820)
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. In my opinion making code essentially unreadable unless you have other background information is an antipattern.

Re:SQL language proposal: JOIN FOREIGN by jmull on Thursday December 30, 2021 @01:56PM (#29740000)
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
Re:SQL language proposal: JOIN FOREIGN by magicalhippo on Thursday December 30, 2021 @02:55PM (#29740736)
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

Re:SQL language proposal: JOIN FOREIGN by zeroimpl on Thursday December 30, 2021 @04:44PM (#29741805)
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 by magicalhippo on Thursday December 30, 2021 @05:07PM (#29742073)
> 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 by silon42 on Thursday December 30, 2021 @02:06PM (#29740124)
+1 ... needed at least as an option where FK is not declared
Re:SQL language proposal: JOIN FOREIGN by ridaj on Thursday December 30, 2021 @02:13PM (#29740214)
Another thing, this makes things harder to reverse.

Some engines have join-order-dependent performance, so there are instances where you would want to write

``` FROM orders LEFT JOIN customers USING (customer_id) ```

and others where you'd want to write

``` FROM customers RIGHT JOIN orders USING (customer_id) ```

Swapping join order with current syntax is relatively easy since references in the same FROM clause are interchangeable. But in this proposal, the reference to the joined table isn't written out, so it would be pretty complicated to reverse join order.

Re:SQL language proposal: JOIN FOREIGN by mritchie712 on Thursday December 30, 2021 @01:51PM (#29739935)
yeah, this saves like 10 characters of text in exchange for massive confusion.
Re:SQL language proposal: JOIN FOREIGN by JoelJacobson on Thursday December 30, 2021 @01:55PM (#29739979)
In this simple example yes, but there can be multiple columns in a join.

The syntax proposal i O(1) syntax wise compared to O(2n) for a JOIN ON where n is the number of columns.

Re:SQL language proposal: JOIN FOREIGN by hn_throwaway_99 on Thursday December 30, 2021 @02:08PM (#29740157)
But that "simple example" is like 95% plus of all my joins, where I'm joining two tables on a foreign key.
Re:SQL language proposal: JOIN FOREIGN by JoelJacobson on Thursday December 30, 2021 @02:15PM (#29740238)
Your data model must be a lot simpler than mine, congrats ;)
Re:SQL language proposal: JOIN FOREIGN by JoelJacobson on Friday December 31, 2021 @07:01AM (#29747235)
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 by JoelJacobson on Thursday December 30, 2021 @01:51PM (#29739933)
I think your concern is addressed by the idea further down in proposal; giving the foreign keys the same names as the referenced tables. This example was provided:

SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM films f JOIN FOREIGN f.distributors d

Re:SQL language proposal: JOIN FOREIGN by hn_throwaway_99 on Thursday December 30, 2021 @02:06PM (#29740133)
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.

Re:SQL language proposal: JOIN FOREIGN by mrweasel on Thursday December 30, 2021 @02:39PM (#29740534)
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 it, sometimes a little extra typing now, will save you a lot of trouble later. The proposal claims: "The idea is to improve the SQL language", but is it really better?

Re:SQL language proposal: JOIN FOREIGN by tapas73 on Thursday December 30, 2021 @05:48PM (#29742484)
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 by hn_throwaway_99 on Thursday December 30, 2021 @07:40PM (#29743490)
In my entire software development career, I've never had that problem, nor seen it.

Sure, I've mistyped a column name or left one out. But the failure is then nearly always so catastrophic (the query failed to compile), or the data response then so different from what I'd expect (pages of results instead on 1) that it's not something I've seen that needs fixing.

Re:SQL language proposal: JOIN FOREIGN by JoelJacobson on Thursday December 30, 2021 @02:23PM (#29740369)
There are two asymmetrical cases; one of them needs two pieces of information, the other three.

Having to always specify both tables would syntax wise be redundant, but I agree it’s worth considering it might be a good thing to improve readability, which would help especially in the case where the foreign key isn’t/cannot be given the same name as the referenced table.

Re:SQL language proposal: JOIN FOREIGN by tqi on Thursday December 30, 2021 @02:36PM (#29740494)
1. Just because you can doesn't mean people will. 2. That doesn't work if you need to have multiple keys pointing to the same table (ie owner_id and secondary_owner_id pointing to a users table)
Re:SQL language proposal: JOIN FOREIGN by mjevans on Thursday December 30, 2021 @01:53PM (#29739958)
In this example D is the target table, FK's natural relationship could be aliased (specified multiple times under different names).