Join syntax on MYSQL

Question

For MYSQL syntax I understand the Joins are FROM table JOIN table ON table.column = table.column I have come across other forms of joins that seam to not only not follow that syntax but the two columns do not relate rather they compliment each-other example below

 from coordinates as cod join
      geofences as geo
      on st_contains(geo.simplified_shape, cod.request_point)

For context this is saying st_contains where A contains B so essentially is this satisfying the join if indeed the request point is in the geo fence shape? I know this is a valid syntax this question is more on if someone can illuminate not only the joins within a parentheses and when that can be applicable rather than the = sign is it only in these specific instances and if my line of thinking is correct that the tables can join not because the values are equivalent but because it satisfies the st_contains condition so for example if you used something else other then st_contains how would that look?

Answer 1

This is really equivalent to:

on st_contains(geo.simplified_shape, cod.request_point) <> 0

What is happening here is that MySQL is converting the result to a "boolean". If the function returns a number, then any non-zero number is "true" and zero is "false".

If the returned value is a string, then the string is converted to a number, based on the leading digits. If there are no leading digits, the value is zero. Then this is treated as a boolean.