Joining on Calculated or Derived Values in MySQL
Yes, you can join on a calculated or derived value in MySQL by using expressions or functions directly in the ON clause. However, doing so can impact performance because MySQL may not be able to use indexes on computed values. These joins work logically, but they should be used with caution.
This works, but both DATE() function calls prevent MySQL from using indexes on the datetime columns, resulting in a full scan.
Joining on formatted dates (DATE(), YEAR(), MONTH()).
Joining on calculated values such as ROUND(), LOWER(), or SUBSTRING().
Joining on conditional logic using CASE expressions.
Joining a table with a derived table created using SELECT ... AS alias.
This approach lets you isolate calculations into smaller datasets, but index usage is still limited if functions wrap indexed columns in the main ON clause.
Using functions on indexed columns typically prevents index usage.
Computed joins often force MySQL into full table scans.
Large datasets can experience significant slowdowns.
Prefer precomputed columns (generated columns) when possible.