Concatenate string with field value in MySQL

Mastering CONCAT in joins

Employing CONCAT with JOIN

To integrate CONCAT in your JOIN conditions, use CONCAT() within the ON clause or SELECT statement:

-- This SQL walks into a bar and sees two tables. It asks, can I join you? 
SELECT tableOne.*, tableTwo.*
FROM tableOne
LEFT JOIN tableTwo ON tableTwo.query = CONCAT('category_id=', tableOne.category_id);


Here, the join is conditioned on a string concatenated with a field value in tableOne.

Clear as day SQL statements

Aliases like t1: and t2: clarify relations within complex SQL queries:

-- Aliases: Making SQL readable since 1974.
SELECT t1.name, t2.description
FROM products AS t1
LEFT JOIN categories AS t2 ON t2.id = CONCAT('CAT_', t1.category_id);


Handiness of SQL mode

In MySQL, you've to adjust sql_mode to 'PIPES_AS_CONCAT' to use || instead of CONCAT(). However, remember, with great power comes great responsibility.

When NULL invades

Remember, if any part of a CONCAT() is NULL, the entire result turns NULL. Run to COALESCE() for rescue here:

-- NULL in CONCAT is like pineapple on pizza. Just no!
SELECT CONCAT(COALESCE(field, 'default'), ' - archives') FROM table;


Juggle multiple columns like a pro

Fancy concatenating multiple columns? CONCAT_WS() separates like a star:

-- How do you organize a space party? You planet with CONCAT_WS!
SELECT CONCAT_WS(' - ', field1, field2, field3) as FullString FROM table;


Minding the performance

Beware, going overboard with concatenation might slow a snail. Keep an eye on the string length and the rows in play.