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.

Practice

1. What is the main difference between CONCAT and CONCAT_WS in SQL?

easy

A. CONCAT joins strings directly; CONCAT_WS adds a separator between strings.

B. CONCAT adds a separator; CONCAT_WS joins strings directly.

C. CONCAT works only with numbers; CONCAT_WS works only with text.

D. CONCAT is used for arrays; CONCAT_WS is used for tables.

Solution

1. Step 1: Understand CONCAT behavior

   CONCAT joins text parts directly without any separator.

2. Step 2: Understand CONCAT_WS behavior

   CONCAT_WS joins text parts but inserts a separator you specify between them.

3. Final Answer:

   CONCAT joins strings directly; CONCAT_WS adds a separator between strings. -> Option A

4. Quick Check:

   Difference = separator usage [OK]

Hint: Remember WS means With Separator [OK]

Common Mistakes:

• Thinking CONCAT_WS adds no separator
• Confusing which function adds separators
• Assuming CONCAT works only on numbers

2. Which of the following is the correct syntax to join columns first_name and last_name with a space using CONCAT_WS?

easy

A. CONCAT_WS(' ', first_name + last_name)

B. CONCAT_WS(first_name, last_name, ' ')

C. CONCAT_WS(' ', first_name, last_name)

D. CONCAT_WS(first_name || ' ' || last_name)

Solution

1. Step 1: Recall CONCAT_WS syntax

   The first argument is the separator string, followed by the columns to join.

2. Step 2: Match syntax to options

   CONCAT_WS(' ', first_name, last_name) correctly uses a space as separator and then the two columns.

3. Final Answer:

   CONCAT_WS(' ', first_name, last_name) -> Option C

4. Quick Check:

   Separator first, then columns [OK]

Hint: Separator is always first argument in CONCAT_WS [OK]

Common Mistakes:

• Putting separator last instead of first
• Using operators inside CONCAT_WS arguments
• Passing concatenated string as single argument

3. Given a table users with columns city = 'Paris', country = 'France', what is the result of this query?

SELECT CONCAT(city, ', ', country) AS location FROM users;

medium

A. 'Paris, France'

B. 'Paris , France'

C. 'Paris,France'

D. 'ParisFrance'

Solution

1. Step 1: Understand CONCAT arguments

   CONCAT joins city, then the string ', ', then country exactly as given.

2. Step 2: Combine values

   city = 'Paris', separator = ', ', country = 'France' results in 'Paris, France'.

3. Final Answer:

   'Paris, France' -> Option A

4. Quick Check:

   CONCAT joins exactly as given [OK]

Hint: CONCAT joins exactly, including spaces in strings [OK]

Common Mistakes:

• Ignoring spaces inside separator string
• Assuming CONCAT adds spaces automatically
• Confusing CONCAT_WS with CONCAT

4. Identify the error in this SQL query:

SELECT CONCAT_WS(first_name, ' ', last_name) FROM employees;

medium

A. Using CONCAT_WS instead of CONCAT

B. Missing separator argument as first parameter

C. Incorrect column names

D. No error, query is correct

Solution

1. Step 1: Check CONCAT_WS syntax

   First argument must be the separator string, but here first_name is used instead.

2. Step 2: Identify correct usage

   Separator should be a string like ' ', then columns follow.

3. Final Answer:

   Missing separator argument as first parameter -> Option B

4. Quick Check:

   Separator must be first argument [OK]

Hint: Separator always first in CONCAT_WS [OK]

Common Mistakes:

• Putting column name as separator
• Swapping argument order
• Assuming CONCAT_WS works like CONCAT

5. You want to create a full address string from columns street, city, and zipcode separated by commas. Which query correctly uses CONCAT_WS to achieve this, and what will be the output if street = '123 Main St', city = NULL, zipcode = '90210'?

hard

A. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, NULL, 90210'

B. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, , 90210'

C. SELECT CONCAT(street, ', ', city, ', ', zipcode) AS address FROM table; Output: '123 Main St, , 90210'

D. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, 90210'

Solution

1. Step 1: Understand CONCAT_WS behavior with NULLs

   CONCAT_WS skips NULL values and does not add extra separators for them.

2. Step 2: Apply to given data

   With city = NULL, CONCAT_WS(', ', street, city, zipcode) returns '123 Main St, 90210' without extra commas.

3. Final Answer:

   SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, 90210' -> Option D

4. Quick Check:

   CONCAT_WS skips NULLs cleanly [OK]

Hint: CONCAT_WS skips NULLs, no extra separators [OK]

Common Mistakes:

• Expecting NULL to appear as text
• Assuming CONCAT_WS adds separators for NULLs
• Using CONCAT which does not skip NULLs