MySql - concat in query with left outer join
Solution 1
If I understand you correct you might need something like this
SELECT t1.name t1_name,
t2.name t2_name
FROM table1 t1 LEFT JOIN
table2 t2 ON CONCAT(t1.country, '.', t1.id ) = t2.code
Solution 2
For anyone pulling his hair out because of the poor performance of ON CONCAT()
joins: make sure you cast your non string values to CHAR
it improves the performance astronomically:
SELECT t1.name t1_name,
t2.name t2_name
FROM table1 t1 LEFT JOIN
table2 t2 ON CONCAT(t1.country, '.', CAST(t1.id AS CHAR) ) = t2.code
Explanation hides in MySQL docs for CONCAT() function:
…If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast…
Credit for this goes to Aurimas Mikalauskas.
Bob
Updated on July 09, 2022Comments
-
Bob almost 2 years
I don't get it. I've tried:
SELECT table1.name, CONCAT( country, '.', id ) AS table1.code, table2.name FROM tabel1 LEFT OUTER JOIN table2 ON ( table1.code = table2.code )
I need to combine
country
andid
tocountry.id
becausetable2.code
has this schema.Thanks in advance.
-
Sparky over 9 yearsYour Fiddle link goes to a blank fiddle.
-
peterm over 9 years@Sparky This fiddle was created more 1.5 years ago. It looks like SQLFiddle dropped old fiddles.
-
Sparky over 9 yearsI realize that. I just thought you'd like to know so you can update your answer to either remove or fix the fiddle.
-
Robert Saylor over 5 yearsThis same method also works in Symfony using Doctrine.
-
Fangxing over 5 yearsIn case someone wants to set COLLATE:
SELECT CAST('test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin