Mysql CONCAT returns NULL if any field contain NULL

Detailed explanation: handling NULLs with CONCAT

The use of CONCAT can result in NULL if any contributing field is NULL. There are alternatives that handle NULL values more gracefully. Let's dive in:

• COALESCE(column, '') can be used in place of IFNULL, this is ANSI SQL standard.

  SELECT CONCAT(COALESCE(column1, ''), COALESCE(column2, ''), COALESCE(column3, '')) AS result
  FROM my_table;
  

• CONCAT_WS is a lifesaver. It excludes NULL values and inserts a separator for you. Great for CSV creation:

  SELECT CONCAT_WS(',', column1, column2, column3) AS csv_result 
  FROM my_table;
  # Result: a CSV-formatted string that doesn't fear NULLs!
  

  Notice something off? Check the first value in the result. If it's NULL, start CONCAT_WS with an empty string:

  SELECT CONCAT_WS('', column1, column2, column3) AS result 
  FROM my_table;
  # Result: a string that starts right, even if first column is a NULL party.
  

Advanced usage: more graceful NULL handling

SQL's capabilities go way beyond the basics. Let's explore some advanced uses:

Sway with CONCAT_WS

Leverage CONCAT_WS's ability to omit NULLs:

SELECT CONCAT_WS('-', column1, column2, column3) AS dashed_result
FROM my_table;
# Because '-' feels left out without NULLs to play with.


Master your separators

No rule states you must stick with a comma! Get creative:

SELECT CONCAT_WS(' | ', column1, column2, column3) AS piped_result
FROM my_table;
# Who said fences '|' don't make good neighbors?


Constructing complex strings with NULLs

For a more complex string, you need the right functions and NULL handling:

SELECT CONCAT_WS(' ', 
                 COALESCE(prefix, ''), 
                 COALESCE(first_name, ''), 
                 COALESCE(last_name, '')
                ) AS full_name
FROM people;
# Result: "Mr. John Doe", not "NULL John NULL". You're welcome.


Essential reading list

Further reading that will hone your skills in the art of NULL handling:

1. MySQL 8.0 Reference Manual: The Bible for understanding NULL.
2. W3Schools: The cliff's notes of SQL, including COALESCE.
3. StackOverflow Discussions: Real developers solving real problems.