Re: SQL "like" from a subselect

Hi Roger,

JOINs are simply looking for a true or false expression result.

While the common / simple use for a JOIN is to compare columns from two
tables for equality, you can perform JOINs based on calculations /
expressions, even complex ones. If the join ON clause evaluates to TRUE,
the JOIN occurs. If the ON clause evaluates to FALSE, the JOIN does not
occur.

While I don't think you'd ever do this, here is a valid JOIN:

left join SOME_TABLE
on dayofweek_iso( CURRENT_DATE ) = 1

That will perform a JOIN only if the current day is a Monday. Any other
day, the JOIN fails.

Note how no columns from any tables are present in the ON clause.

Think of the ON clause as an expression that simply evaluates to true or
false, and you'll gain more control over your SQL. Keep in mind that use
of expressions in ON clauses can lead to performance issues, in some cases.

Mike

date: Mon, 30 Nov 2015 16:59:49 +0000
from: Roger Harman <roger.harman@xxxxxxxxxxx>
subject: Re: SQL "like" from a subselect

Join on a "like"? Haven't seen that before. Maybe later this week when I
have some free time (Ha!). Month-end today.......

Thanks.