Simple inner join using a column that is in lowr case in one table, upper in the other, How?

Maneksh, be aware that in Sybase ASE that version guarantees a table scan, unless one is using ASE 15.0+ and has built a functional index on upper(Table_1.userid).

garyinmiami2003, let us know if there's any possibility that Table_1 has any lower case. If not, the first version has a chance of using an index, but the second does not. Broadly, one wants WHERE clauses to look like this:

     WHERE   [column on its own]  [relational operator]  [expression whose value can be known at compile time]

Relational operator means "=", "<", ">", "<=", ">=", "IN", "EXISTS". Specifically it excludes "!=", "<>", "!<", "!>". "IS NULL" is ok.

Expression whose value can be known at compile time means not a local variable.

It's a bit more complicated than the above, but those are the main rules. In this specific case, we want to avoid any functions on the LHS of the WHERE clause if possible.

400  points went to Joe.  He responde first.  Maneksh added value to the solution.  Maneksh added value to the answer.  I hope you all think the points were awarded fairly.

Yep ,

I completely agree with you Joe. There is always the risk of table scan in that query.

Sorry for not mentioning it.

Thanks
Maneksh

Just to clarify - without a specific functional index, putting a function on the LHS of a WHERE clause doesn't just have a "risk" of a table scan... it will guarantee it.