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.


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].