How to perform type strict JOIN

Question

I have 2 tables I would like to left join together.
Table A has my reference data with ID of type integer.
Table B stores my data and one of the columns has a loose link to my reference table. This means it can hold the ID from the other table or some arbitrary text.
I would like to perform a query joining them together and displaying the data from reference table only if a record with matching ID is found.

A much simplified example (MCVE):

CREATE TABLE a 
  ( 
     id INT 
  ); 

INSERT INTO a VALUES (5); 

CREATE TABLE b 
  ( 
     id VARCHAR(10) 
  ); 

INSERT INTO b VALUES ('5sdf');

I tried this query:

SELECT * FROM a LEFT JOIN b USING(id)

but the result is 5, which is not what I expected because values are different - http://www.sqlfiddle.com/#!9/6c5965/1

I also tried with explicit ON clause:

SELECT * FROM a LEFT JOIN b ON a.id=b.id

but this one was also joined. - http://www.sqlfiddle.com/#!9/6c5965/2/0

How can I join the two tables with columns of different data types without implicit casting?

(Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Please ask further (non-duplicate) questions in further posts. — philipxy, Jun 20 '19 at 00:53
@philipxy Obviously it is not a duplicate of that one. I didn't ask whether it was a good idea to do it, I asked how it can be done. The other answers did not mention virtual columns or casting number to a string. I searched for an answer, but I could not find a satisfactory one. — Dharman, Jun 20 '19 at 09:52

score 2 · Accepted Answer · answered Jun 19 '19 at 22:43

2

Convert the integer to a string when joining, rather than relying on implicit conversions, which default the other way.

SELECT * FROM a LEFT JOIN b ON CAST(a.id AS CHAR) = b.id;

fiddle

answered Jun 19 '19 at 22:43

Barmar

741,623
53
500
612

How would it affect performance for a large select? – Dharman Jun 19 '19 at 22:45
1

Any join that involves converting datatypes cannot be indexed, so it will have horrible performance. You could solve this by creating an indexed virtual column containing `CAST(id AS CHAR)`. – Barmar Jun 19 '19 at 22:49

How to perform type strict JOIN

1 Answers1