4 ms·
I don't understand this comment. x JOIN y ON x.id=y.id How is this advanced? The fact that there's an endless number of pitfalls[1] is a characteristic of th
by doublesCs 6y ago
I don't understand this comment.
x JOIN y ON x.id=y.id
How is this advanced?
The fact that there's an endless number of pitfalls[1] is a characteristic of the operation you're trying to achieve, not a deficiency of SQL. In fact if anything I would say that SQL does a great joke at letting you tweak how you want all those edge cases to be handled.
[1] E.g. What happens if some records are missing on one or the other sides of the join? What happens if ids are not unique?
- goto11 6y agoI would be convenient if you could say "x INCLUDE y" or something like that and the engine would figure out the join by foreign/primary keys. Might seem like a small improvement, but if you write lots of queries it would be a significant time saver.
- doublesCs 6y agoIf you do x JOIN y Postgres automatically uses same-name columns for the join. So in the example I gave you, actually "ON x.id=y.id" is redundant. I just added it because if I hadn't someone else would've replied "oh but that only works if you name the columns the same name" Also, in any less than trivial scenario you will have to think how to handle the edge cases. But again, that's a property of the operation, not of the language.
- goto11 6y agoOh yeah, that is a "natural join". Problematic since it depends on the names of the columns. I can see how it is convenient though. I would like something like this, just based on declared foreign key relationships rather than column names.
- doublesCs 6y agoI could say the same thing about your suggestion: Problematic since it depends on what you set as primary/foreign key. I can see how it is convenient though.
- nsonha 6y agoForeign key is a formal semantic while same column name is just a convention
- goto11 6y agoWhat is problematic about that? Column names are just names, there is no guarantee that value in one table corresponds to a value in another table just because the columns have same name. Private/foreign key declarations enforces consistency so it is not possible to have an orphan foreign key.
- rspeele 6y agoAgreed, not just a time-saver but a self-documenting/maintaining advantage. In concept you could change the underlying keys that relate a Foo to a Bar, and still have your queries work. And prevents dumb typos/brainfarts like "join... on Foo.Id = Bar.Id" mistakenly written instead of "on Foo.Id = Bar.FooId".