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

Concept Flow - CONCAT and CONCAT_WS

Start with strings

↓

Apply CONCAT or CONCAT_WS

↓

Check each argument

↓

If CONCAT: Join all strings directly

↓

If CONCAT_WS: Use separator between strings

↓

Return combined string

↓

End

The flow shows how CONCAT joins strings directly, while CONCAT_WS joins strings using a separator between them.

Execution Sample

SQL

SELECT CONCAT('Hello', ' ', 'World') AS greeting;
SELECT CONCAT_WS('-', '2024', '06', '15') AS date_str;

This code joins strings: CONCAT joins without separator, CONCAT_WS joins with '-' as separator.

Execution Table

Step	Function	Arguments	Action	Result
1	CONCAT	'Hello', ' ', 'World'	Join all strings directly	'Hello World'
2	CONCAT_WS	'-', '2024', '06', '15'	Join strings with '-' separator	'2024-06-15'
3	CONCAT	'Hi', NULL, 'There'	NULL treated as empty string, join directly	'HiThere'
4	CONCAT_WS	'-', 'apple', NULL, 'banana'	Skip NULL, join with '-' separator	'apple-banana'
5	End		No more input, return results	Execution complete

💡 All arguments processed, concatenation complete

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Result	''	'Hello World'	'2024-06-15'	'HiThere'	'apple-banana'	Final concatenated strings

Key Moments - 2 Insights

Why does CONCAT_WS skip NULL values but CONCAT does not?

CONCAT_WS is designed to ignore NULLs and not add separators for them, as shown in execution_table rows 3 and 4. CONCAT treats NULL as empty string and includes it.

What happens if the separator in CONCAT_WS is NULL?

If the separator is NULL, CONCAT_WS returns NULL immediately, because the separator is required. This is not shown in the table but is important to remember.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of CONCAT_WS('-', '2024', '06', '15') at step 2?

A '2024-06-15'

B '20240615'

C '2024,06,15'

D '2024 - 06 - 15'

💡 Hint

Check the 'Result' column in row 2 of execution_table

At which step does CONCAT treat NULL as an empty string?

A Step 1

B Step 3

C Step 4

D Step 2

💡 Hint

Look at the 'Arguments' and 'Action' columns in step 3 of execution_table

If you change the separator in CONCAT_WS to NULL, what would happen?

A It treats NULL as empty string separator

B It joins strings without separator

C It returns NULL immediately

D It throws a syntax error

💡 Hint

Refer to key_moments explanation about separator behavior in CONCAT_WS

Concept Snapshot

CONCAT(str1, str2, ...) joins strings directly.
CONCAT_WS(separator, str1, str2, ...) joins strings with separator.
CONCAT_WS skips NULL values; CONCAT treats NULL as empty string.
If separator in CONCAT_WS is NULL, result is NULL.
Use CONCAT for simple joins, CONCAT_WS to add separators cleanly.

Full Transcript

This visual execution shows how CONCAT and CONCAT_WS work in SQL. CONCAT joins all strings directly, including empty strings for NULLs. CONCAT_WS joins strings using a separator and skips NULL values to avoid extra separators. The execution table traces examples step-by-step, showing inputs, actions, and results. Key moments clarify common confusions about NULL handling and separator behavior. The quiz tests understanding of these behaviors referencing the execution visuals.