Menu
I am trying to do an inexact join (I am not sure what the proper term is) where I could perform pattern matching. Basically, instead of doing a JOIN like this:
.... JOIN .... ON (t1.col = t2.col)
I would like to do do something like:
.... JOIN .... ON (t1.col ILIKE %(t2.col)% )
The second example is obviously not proper syntax. Is there a way to do something like that?
954
LIKE and ILIKE allow pattern matching within character-based column data. Their syntax is identical, but LIKE is case-sensitive, while ILIKE is case-insensitive.
The keyword ILIKE can be used instead of LIKE to make the match case insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension. The operator ~~ is equivalent to LIKE , and ~~* corresponds to ILIKE .
The Regular Expressions in PostgreSQL are implemented using the TILDE (~) operator and uses '. *” as a wildcard operator. As you can see in the figure above, we have used Regular Expression in PostgreSQL using the TILDE (~) operator and the wildcard '.
“Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there's no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan.
.... JOIN .... ON t1.col ILIKE '%' || t2.col || '%'
Please note that as written, AFAIK, PostgreSQL won't be able to use any indexes to speed up the join processing.
158
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
