sql - Joining on columns of different type?

Question

If one column is of type int and say has a value 10. The other column is of type varchar and has a value of '10'. Is it safe to join on these values (mySql), and would I get the same result as if both were of type int and value 10?

I need to have them of different types because in one table the column is an autoincrementing key (so it must be int). The other column (in a different table) may occasionally contain values with letters so I must make it varchar. Can you forsee any huge problems with this or maybe I should rethink my schema?

Answer 1

There are two problems with this approach:

1. MySQL will not use indexes on such join - which may or may not be a huge deal depending on your circumstances (table size / query / etc...)

2. Database design-wise this smells fishy. Perhaps it's my poor imagination :-) but I can't think of an example where something like this would be justified. If it's a primary key in one table, it should be foreign key in another. Can you explain what you're trying to do in more detail?