Hacker News Reader

HNHacker News

Top New Best Ask Show Jobs

Comment by revskill | Hacker News Reader

Full thread

revskill·

Does SQL Join allow equality with operators like ilike, like,... or custom equality function ?

select * from a inner join b on mycustomfunction(a, b);

View on HN

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.

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.

That'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.

Reply on news.ycombinator.com