3 ms·
Does SQL Join allow equality with operators like ilike, like,... or custom equality function ? select * from a inner join b on mycustomfunction(a, b);
by revskill 8y ago
Does SQL Join allow equality with operators like ilike, like,... or custom equality function ?
select * from a
inner join b
on mycustomfunction(a, b);
- hobs 8y agoGenerally 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.
- sk1pper 8y agoYou can create indexes on expressions, like mycustomfunction(a), in Postgres at least.
- mhotchen 8y agoGood 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.html https://www.postgresql.org/docs/current/indexes-expressional... [1] https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...
- TeMPOraL 8y agoSQLite has it too (caveats, like always, apply): https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html.
- hobs 8y agoThat's cool, but then I'd assume you have to make an index for each function. In general if a person is asking basic questions about sargability, I will steer them away from functions on a predicate (it's usually my last choice.) Often you can just think a bit harder about your set based logic and make an index that's reusable by other queries. In other SQL's you can also make a materialized view, which may be able to persist some of these things, its just like keeping n+1 copies of your data to satisfy a different set of relations/projections.