What does SQL Optimizer suggest the use of COALESCE within Joins? (4304863)

What does SQL Optimizer suggest the use of COALESCE within Joins?

The COALESCE function applied in the transformation can be seen as a way to disable the use of an index, hence forcing the database to pick another index to use in a join. Take the following SQL as an example:

select * from TableA, TableB where TableA.ColumnA = TableB.ColumnB

Assumed that both TableA.ColumnA and TableB.ColumnB are indexed. When database received the SQL, it has basically 2 choices:

(1) Use the value in TableA.ColumnA to search of a matching row in TableB using the index on TableB.ColumnB, that will result in a join path TableA driving TableB; or
(2) Use the value in TableB.ColumnB to search of a matching row in TableB using the index on TableA.ColumnA, that will result in a join path TableB driving TableA

Note that since both driving paths are possible for the SQL statement, it will be up to the database to choose either one of them. Database will choose the one it thinks to be the best but what if you want the database to choose option (1)? The transformation that adds the COALESCE will help:

select * from TableA, TableB where COALESCE(TableA.ColumnA, TableA.ColumnA) = TableB.ColumnB

The COALESCE(TableA.ColumnA, TableA.ColumnA) has the same meaning as TableA.ColumnA. However, it disallowed the database to use the index on TableA.ColumnA in the new condition, COALESCE(TableA.ColumnA, TableA.ColumnA) = TableB.ColumnB. As a result, Option (2) is eliminated from database to choose.