Can mysql join occur on different data types

Question

Suppose we have two tables, table A (parent) and table B (children) with a one to many relationship between them, established via parent_id (VARCHAR (100)) field in table B.

The parent_id field's datatype is different from id (INT(11)) field in table A. So the question is can MYSQL JOIN query return the proper records?

I encountered this kind of situation. I am running the following query:

 SELECT p.payment_amount, s.company_name
 FROM payments p 
 LEFT JOIN suppliers s ON p.supplier_id = s.id 
 LIMIT 5

Here one supplier has multiple payments. Now this query is returning me 5 records in which I can see the payment_amount for all 5 records but I can see company_name for only those records which have p.supplier_id one digit length. If in database I modify p.supplier_id to any valid 2 digit id, I cant get the supplier record.

Answer 1

MySQL can join with different data type, but the performance is poor as it has to convert from one type to the other for each row.

I set up a quick SQL fiddle to demonstrate it working, based on your SQL above:-

http://www.sqlfiddle.com/#!2/f7d02a/1

Note that a leading character in the number stored in a string will result in it being evaluated to 0.