Joins In Steps
zindlerb.com
Joins In Steps
1–10 of 28 posts
Re: Joins In Steps
#2Does SQL Join allow equality with operators like ilike, like,... or custom equality function ?
select * from a inner join b on mycustomfunction(a, b);
Re: Joins In Steps
#3Does SQL Join allow equality with operators like ilike, like,... or custom equality function ? select * from a inner join b on mycustomfunction(a, b);
Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b)
And if you do this, it will be incredibly slow because you cant use any indexes in this case.
Re: Joins In Steps
#4Does SQL Join allow equality with operators like ilike, like,... or custom equality function ? select * from a inner join b on mycustomfunction(a, b);
Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b) And if you do this, it will be incredibly slow because you cant use any indexes in this case.
You can create indexes on expressions, like mycustomfunction(a), in Postgres at least.
Re: Joins In Steps
#5Does SQL Join allow equality with operators like ilike, like,... or custom equality function ? select * from a inner join b on mycustomfunction(a, b);
Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b) And if you do this, it will be incredibly slow because you cant use any indexes in this case.
Good news! Many database systems support indexes on functions, including on user-defined functions. The only one I've used is Postgres[0] but apparently MySQL added support recently as well[1]
[0] https://www.postgresql.org/docs/current/indexes-expressional...
[1] https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...
Re: Joins In Steps
#6I think about my self as quite SQL-savvy person, I used to optimize quite complex queries and is able to read plans for Oracle, Postgres and MySQL.
And yet, I've got not idea why would anybody need right join.
Have you guys ever had a case when you'd need a right join? I've been to the field for 15 years and yet to see people using right join in the wild.
Like the last example in this link - why would you do that? Most probably your business logic focuses on dogs, something like "find dogs with no owner" or something, In this case it is much more readable and straight forward to go with left join or even with sub-select where you'd have something like 'select * from dogs where owner_id not in (select id from owners)'.
Have you used right joins and if you have can you explain the use case?
Re: Joins In Steps
#7Earlier quoted context omitted.
Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b) And if you do this, it will be incredibly slow because you cant use any indexes in this case.
Good news! Many database systems support indexes on functions, including on user-defined functions. The only one I've used is Postgres[0] but apparently MySQL added support recently as well[1] [0] https://www.postgresql.org/docs/current/indexes-expressional... [1] https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...
SQLite has it too (caveats, like always, apply): https://www.sqlite.org/expridx.html.
Re: Joins In Steps
#8I think about my self as quite SQL-savvy person, I used to optimize quite complex queries and is able to read plans for Oracle, Postgres and MySQL. And yet, I've got not idea why would anybody need right join. Have you guys ever had a case when you'd need a right join? I've been to the field for 15 years and yet to see people using right join in the wild. Like the last example in this link - why would you do that? Mo…
I think that I've almost used a right join before, but not for anything like a legitimate use case. I was attempting to out-think an in-house DB's optimizer that was doing a hash join in the wrong direction. There may have been some rough spots in some of that DB's specialized SQL extensions that played into the problem. I think I ended up using a full outer join plastered with optimizer hints. Like you said, if you're using a right join, probably not unlikely that you're doing something wrong.
Re: Joins In Steps
#9I think about my self as quite SQL-savvy person, I used to optimize quite complex queries and is able to read plans for Oracle, Postgres and MySQL. And yet, I've got not idea why would anybody need right join. Have you guys ever had a case when you'd need a right join? I've been to the field for 15 years and yet to see people using right join in the wild. Like the last example in this link - why would you do that? Mo…
I struggle to think of time I used a right join in the 20-odd years I've used SQL.
The example shows a case where you'd want foreign keys to enforce referential integrity, though FKs aren't fashionable these days. You shouldn't be able to have an entry for owner_id 8 in the Dogs table without a corresponding Owner.
Re: Joins In Steps
#10I really feel like we need a lower level expression language than SQL, one based on actual query plans rather than a declarative thing.
Having that, and helping people learn that, would make it much harder to not “get” how joins and the like end up working