CONCAT and CONCAT_WS in MySQL - Step-by-Step Execution

Concept Flow - CONCAT and CONCAT_WS

Start with strings

↓

Apply CONCAT or CONCAT_WS

↓

Check each argument

↓

Join strings

↓

Return combined string

The functions take strings, check each for NULL, then join them into one string, returning the combined result.

Execution Sample

MySQL

SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS('-', '2024', NULL, '06', '15');

These queries join strings: CONCAT joins all including NULL as empty, CONCAT_WS joins with separator skipping NULLs.

Execution Table

Step	Function	Arguments	NULL Handling	Result
1	CONCAT	'Hello', ' ', 'World'	No NULLs, all included	'Hello World'
2	CONCAT_WS	'-', '2024', NULL, '06', '15'	NULL skipped	'2024-06-15'
3	CONCAT	'A', NULL, 'B'	NULL treated as empty string	'AB'
4	CONCAT_WS	',' , NULL, 'X', NULL, 'Y'	NULL skipped	'X,Y'
5	CONCAT_WS	NULL, 'X', 'Y'	Separator NULL returns NULL	NULL
6	CONCAT	NULL, NULL	NULL treated as empty strings	''
7	CONCAT_WS	'-', NULL, NULL	All NULLs skipped, no strings	''

💡 All arguments processed; NULLs handled differently by CONCAT and CONCAT_WS; final string returned.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7
Result String	''	'Hello World'	'2024-06-15'	'AB'	'X,Y'	NULL	''	''

Key Moments - 3 Insights

Why does CONCAT_WS skip NULL values but CONCAT treats NULL as empty strings?

CONCAT_WS is designed to ignore NULLs to avoid extra separators, as shown in execution_table rows 2 and 4, while CONCAT treats NULL as empty strings to join all arguments directly, as in rows 1 and 3.

What happens if the separator in CONCAT_WS is NULL?

If the separator is NULL, CONCAT_WS returns NULL immediately without concatenation, as shown in execution_table row 5.

Why does CONCAT return an empty string when all arguments are NULL?

CONCAT treats each NULL as an empty string, so joining NULL and NULL results in an empty string, as shown in execution_table row 6.

Visual Quiz - 3 Questions

Look at the execution_table row 2. What is the output of CONCAT_WS('-', '2024', NULL, '06', '15')?

A '2024-NULL-06-15'

B '2024-06-15'

C '2024--06-15'

D '2024 06 15'

💡 Hint

Check how CONCAT_WS handles NULLs in the 'NULL Handling' column of row 2.

According to the execution_table, what does CONCAT('A', NULL, 'B') return?

A 'AB'

B 'A NULL B'

C 'A B'

D NULL

💡 Hint

Look at row 3 and see how CONCAT treats NULL arguments.

In which step does CONCAT_WS return NULL because the separator is NULL?

A Step 4

B Step 6

C Step 5

D Step 7

💡 Hint

Check the 'Separator NULL returns NULL' note in the 'NULL Handling' column.

Concept Snapshot

CONCAT(str1, str2, ...) joins all strings, treating NULL as empty.
CONCAT_WS(separator, str1, str2, ...) joins strings with separator, skipping NULLs.
If separator is NULL in CONCAT_WS, result is NULL.
Useful for combining columns or literals into one string.
NULL handling differs: CONCAT includes empty, CONCAT_WS skips.
Syntax: CONCAT('a', NULL, 'b') -> 'ab'; CONCAT_WS('-', 'a', NULL, 'b') -> 'a-b'.

Full Transcript

This lesson shows how CONCAT and CONCAT_WS work in MySQL. CONCAT joins all strings given, treating NULL as empty strings, so NULL does not stop the join. CONCAT_WS joins strings with a separator but skips any NULL values to avoid extra separators. If the separator itself is NULL in CONCAT_WS, the result is NULL. Examples show how these functions handle NULLs differently. This helps when combining text fields or literals in queries.