SQL language proposal: JOIN FOREIGN • Hacker News Board

Re: SQL language proposal: JOIN FOREIGN

#21

Post by JoelJacobson » Thu, Dec 30, 2021, 7:11 PM UTC

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…

Good point, but addressable:

Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition.

I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably good in general too, but I agree you might need to disable it for some FKs, in some databases, like PostgreSQL before they got FOR KEY SHARE locks.

Re: SQL language proposal: JOIN FOREIGN

#22

Post by ridaj » Thu, Dec 30, 2021, 7:13 PM UTC

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…

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

#23

Post by hnthrowaway0315 » Thu, Dec 30, 2021, 7:15 PM UTC

nvartolomei wrote: post #14

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

Some have support for this. - BQ https://cloud.google.com/bigquery/docs/reference/standard-sq... - CH https://clickhouse.com/docs/en/sql-reference/statements/sele...

Thanks, these really look neat. Unfortunately we only use Vertica :/

Re: SQL language proposal: JOIN FOREIGN

#24

Post by JoelJacobson » Thu, Dec 30, 2021, 7:15 PM UTC

hn_throwaway_99 wrote: post #20

JoelJacobson wrote: post #12

Earlier quoted context omitted.

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.

But that "simple example" is like 95% plus of all my joins, where I'm joining two tables on a foreign key.

Your data model must be a lot simpler than mine, congrats ;)

Re: SQL language proposal: JOIN FOREIGN

#25

Post by cogman10 » Thu, Dec 30, 2021, 7:17 PM UTC

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…

Absolutely agree.

Further, whenever someone is adjusting table performance index tweaks is almost always the first thing to tackle.

Adding foreign keys into the query is just as bad as adding indexes into the query (which, you can do in T-SQL, but generally shouldn't). Indexes can be dropped, changed, or added and you SHOULD be relying on the SQL optimizer to use the most appropriate index.

This feature appears to only save a bit of typing in the best of scenarios. In the worst, an update/drop of a foreign key will end up breaking a bunch of queries, which is insane.

Re: SQL language proposal: JOIN FOREIGN

#26

Post by hn_throwaway_99 » Thu, Dec 30, 2021, 7:17 PM UTC

JoelJacobson wrote: post #21

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…

Good point, but addressable: Simply decouple the relationship definition and referential integrity check, allowing a user to drop the referential integrity check if desired, but keeping the relationship definition. I cannot see why you would not want to at least always store the information a certain table/column(s) references some other table/column(s) in the data model. Enforcing referential integrity is probably g…

At this point you should realize your proposal is a non-starter, and I didn't even realize btilly's objection originally.

I can't think of any other feature in SQL where the rules of the query are actually dependent on something not explicit to the query itself. Even USING and NATURAL are just syntactic sugar that depends on the structure of the table, not on any underlying constraints.

So, what you have proposed, "allowing a user to drop the referential integrity check if desired, but keeping the relationship definition" would be a massive change to tons of SQL tools out there as it's a huge new feature, for some minor syntactic sugar. Ain't gonna happen.

Re: SQL language proposal: JOIN FOREIGN

#27

Post by JoelJacobson » Thu, Dec 30, 2021, 7:23 PM UTC

hn_throwaway_99 wrote: post #19

JoelJacobson wrote: post #8

Earlier quoted context omitted.

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

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.

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

#28

Post by piaste » Thu, Dec 30, 2021, 7:24 PM UTC

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.

Re: SQL language proposal: JOIN FOREIGN

#29

Post by jimbobmcgee » Thu, Dec 30, 2021, 7:25 PM UTC

Is anyone actually likely to implement these based on some random gist on the internet, or are you just screaming into the void?

Because, if you have that kind of clout, I've got an INSERT/SET syntax I'd like to put your way...

Re: SQL language proposal: JOIN FOREIGN

#30

Post by zzzeek » Thu, Dec 30, 2021, 7:30 PM UTC

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…

this was my thought too. but now you need an ALTER CONSTRAINT NON ENFORCING or something like that so that the "constraints" can be present declaratively but don't actually get used.

which then looks a whole lot like you're just introducing macros into SQL where you have some symbolic keywords that expand out into pre-fabricated ON clauses.