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

1–10 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#2

Post by JoelJacobson »

This is the same idea as already posted in this thread https://news.ycombinator.com/item?id=29687134, but with a more in-depth explanation that couldn't fit in the comment field, and with some syntax improvements, such as using "FOREIGN", which is a reserved keyword, instead of the previously suggested "->" notation.

Thanks for all the valuable comments on last proposal. Excited to hear what you think about this update.

Re: SQL language proposal: JOIN FOREIGN

#4

Post by torgard »

I dig it!

I prefer defining tables like this:

    CREATE TABLE category (
        id int GENERATED ALWAYS AS IDENTITY,
        name text
    );
    
    CREATE TABLE post (
        id int GENERATED ALWAYS AS IDENTITY,
        category_id int
            REFERENCES category (id)
                ON DELETE CASCADE
    );
That is, category.id rather than category.category_id. But the USING clause doesn't work with that style, as far as I understand.

This would make my queries nicer.

Re: SQL language proposal: JOIN FOREIGN

#5

Post by hn_throwaway_99 »

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

#8

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…

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

#9

Post by mritchie712 »

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…

yeah, this saves like 10 characters of text in exchange for massive confusion.

Re: SQL language proposal: JOIN FOREIGN

#10

Post by mjevans »

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…

In this example D is the target table, FK's natural relationship could be aliased (specified multiple times under different names).
Post reply on HN