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

121–130 of 206 posts

Re: SQL language proposal: JOIN FOREIGN

#121

Post by Tostino »

cogman10 wrote: post #25
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 on…

While I wish there were a way to easily refactor queries when making these changes, this is not different than it works for renaming/dropping a column..."In the worst, an re-name/drop of a column will end up breaking a bunch of queries, which is insane."

Re: SQL language proposal: JOIN FOREIGN

#124

Post by tapas73 »

I will write a letter to santa asking to get this into sql. During 10 years in data warehouse I constantly wondered why SQL does not have something like that.

Reducing verboseness is nice, but the main perk is the correctness.

Oh.. if I got a cent every time I found a bug in colleagues sql, because of join accidentally multiplying/doubling rows... :-)

Re: SQL language proposal: JOIN FOREIGN

#126

Post by Tostino »

samtheprogram wrote: post #92
thanatos519 wrote: post #71

I dunno. I preferred the SQL-89 syntax.

Same here. I didn’t even realize that was an option. As a relative beginner to SQL, is there a real reason the JOIN syntax was needed at all beyond special inner/outer joins?

Sanity for your coworkers who may have to touch your SQL.

Re: SQL language proposal: JOIN FOREIGN

#127

Post by vosper »

tester756 wrote: post #108

While we are at it let's write SQL queries starting from FROM. `FROM users SELECT *` It'd allow tooling to provide IntelliSense better.

I agree, though I'll give SQL a pass for this because it's old.

But how the Javascript world ever thought that `import { function } from 'library'` was better than `from 'library' import { function }` I'll never know. Python got this right long before anyone was even thinking about adding imports to JS!

Re: SQL language proposal: JOIN FOREIGN

#129

Post by Tostino »

degenerate wrote: post #113
thom wrote: post #16

Of all the myriad indignities of SQL, this isn't near the top of my list. I also don't like making the names of objects like foreign keys and indexes first class concerns in your queries, that's a whole new layer of cognitive overhead.

Agree. I'd much prefer they worked on the way MySQL determines what indexes to use on a multi-table join, so there could be less emphasis on the need to ANALYZE TABLE and FORCE INDEX when the DB is seemingly being dumb, but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up. Troubleshooting full table scans on large sets of data is a nightmare.

Slightly different group of people working on MySQL vs this proposal.

Re: SQL language proposal: JOIN FOREIGN

#130

Post by halayli »

It's a great proposition. However, I would suggest having it as a prefix rather than postfix of the JOIN expression to reduce implementation side effects and avoid ambiguity between keyword 'FOREIGN' and a table called 'FOREIGN'.

example: FOREIGN LEFT JOIN

Post reply on HN