CONCAT and CONCAT_WS in SQL - Deep Dive

Overview - CONCAT and CONCAT_WS

What is it?

CONCAT and CONCAT_WS are SQL functions used to join multiple strings into one. CONCAT simply joins strings together without any separator. CONCAT_WS joins strings but inserts a separator between them, like a comma or space. These functions help combine text data from different columns or values into a single string.

Why it matters

Without these functions, combining text from different parts of a database would be slow and error-prone. You would have to manually add separators and handle missing values, which can cause messy or incorrect results. CONCAT and CONCAT_WS make string joining easy, clean, and reliable, improving data readability and reporting.

Where it fits

Before learning CONCAT and CONCAT_WS, you should understand basic SQL SELECT queries and string data types. After mastering these functions, you can explore more advanced string functions like SUBSTRING, REPLACE, and formatting techniques for reports or data cleaning.

Mental Model

Core Idea

CONCAT joins strings directly, while CONCAT_WS joins strings inserting a chosen separator between them.

Think of it like...

Imagine writing a shopping list: CONCAT is like writing items one after another without spaces, while CONCAT_WS is like writing items separated by commas or spaces so the list is clear.

┌─────────────┐       ┌─────────────┐
│ String 1    │       │ Separator   │
└─────┬───────┘       └─────┬───────┘
      │                     │
      │                     │
      ▼                     ▼
  ┌─────────────────────────────┐
  │ CONCAT: String1 + String2 + ... │
  └─────────────────────────────┘

  ┌─────────────────────────────┐
  │ CONCAT_WS: String1 + sep + String2 + sep + ... │
  └─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Basic CONCAT usage

Concept: Learn how CONCAT joins two or more strings directly.

Use CONCAT to join strings without any separator. For example, CONCAT('Hello', 'World') returns 'HelloWorld'. You can join columns or literals this way.

Result

SELECT CONCAT('Hello', ' ', 'World'); -- Output: 'Hello World'

Understanding CONCAT is the first step to combining text data simply and quickly.

2

Foundation

Basic CONCAT_WS usage

Concept: Learn how CONCAT_WS joins strings with a separator.

CONCAT_WS takes a separator as the first argument, then joins the rest of the strings inserting that separator between them. For example, CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry') returns 'Apple, Banana, Cherry'.

Result

SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry'); -- Output: 'Apple, Banana, Cherry'

Knowing CONCAT_WS lets you create readable lists or combined strings with clear separators.

3

Intermediate

Handling NULL values in CONCAT

🤔Before reading on: do you think CONCAT skips NULLs or returns NULL if any input is NULL? Commit to your answer.

Concept: CONCAT treats NULL as an empty string and continues joining other strings.

If any argument to CONCAT is NULL, it is treated as empty, so CONCAT('Hello', NULL, 'World') returns 'HelloWorld' without errors or NULL result.

Result

SELECT CONCAT('Hello', NULL, 'World'); -- Output: 'HelloWorld'

Understanding NULL handling prevents surprises when combining data that may have missing values.

4

Intermediate

Handling NULL values in CONCAT_WS

🤔Before reading on: does CONCAT_WS include separators for NULL values or skip them? Commit to your answer.

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

If any argument after the separator is NULL, CONCAT_WS ignores it and does not add extra separators. For example, CONCAT_WS(', ', 'Apple', NULL, 'Cherry') returns 'Apple, Cherry'.

Result

SELECT CONCAT_WS(', ', 'Apple', NULL, 'Cherry'); -- Output: 'Apple, Cherry'

Knowing CONCAT_WS skips NULLs helps create clean lists without extra separators.

5

Intermediate

Using CONCAT and CONCAT_WS with columns

Concept: Apply CONCAT and CONCAT_WS to combine values from table columns.

You can join columns from a table to create full names or addresses. For example, CONCAT(first_name, ' ', last_name) or CONCAT_WS(', ', city, state, country).

Result

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; SELECT CONCAT_WS(', ', city, state, country) AS location FROM users;

Using these functions with columns is essential for real-world data formatting and reporting.

6

Advanced

Performance considerations with CONCAT functions

🤔Before reading on: do you think CONCAT and CONCAT_WS have significant performance differences? Commit to your answer.

Concept: CONCAT and CONCAT_WS are efficient but using many arguments or large strings can affect query speed.

While these functions are optimized, joining many large strings repeatedly in queries can slow down performance. Indexing and query design matter more for large datasets.

Result

No direct output, but query speed may vary based on usage.

Understanding performance helps write efficient queries and avoid slow reports.

7

Expert

Unexpected behavior with empty strings and separators

🤔Before reading on: does CONCAT_WS add separators for empty strings or only for NULLs? Commit to your answer.

Concept: CONCAT_WS adds separators for empty strings but skips NULLs, which can lead to unexpected extra separators.

For example, CONCAT_WS(', ', 'Apple', '', 'Cherry') returns 'Apple, , Cherry' with an empty field between commas. This differs from NULL handling and can affect output formatting.

Result

SELECT CONCAT_WS(', ', 'Apple', '', 'Cherry'); -- Output: 'Apple, , Cherry'

Knowing this subtlety prevents formatting bugs in reports and data exports.

Under the Hood

CONCAT and CONCAT_WS are string functions implemented inside the SQL engine. CONCAT simply appends each string argument in order, treating NULL as empty. CONCAT_WS first reads the separator argument, then iterates over the remaining strings, skipping NULLs and inserting the separator between non-NULL values. This logic happens during query execution, often optimized for speed.

Why designed this way?

These functions were designed to simplify common string joining tasks that previously required complex expressions or manual handling. Treating NULLs differently in CONCAT and CONCAT_WS reflects common use cases: CONCAT assumes missing data is empty, while CONCAT_WS avoids extra separators for missing values, improving output cleanliness.

┌───────────────┐
│ Input Strings │
└──────┬────────┘
       │
       ▼
┌─────────────────────┐
│ CONCAT Function      │
│ - Treat NULL as ''  │
│ - Append all strings │
└─────────┬───────────┘
          │
          ▼
   ┌─────────────┐
   │ Output String│
   └─────────────┘


┌───────────────┐
│ Separator +   │
│ Input Strings │
└──────┬────────┘
       │
       ▼
┌───────────────────────────┐
│ CONCAT_WS Function         │
│ - Skip NULL strings        │
│ - Insert separator between│
│   non-NULL strings         │
└─────────────┬─────────────┘
              │
              ▼
       ┌─────────────┐
       │ Output String│
       └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does CONCAT return NULL if any input is NULL? Commit to yes or no.

Common Belief: CONCAT returns NULL if any argument is NULL.

Reality: CONCAT treats NULL as an empty string and continues joining other strings.

Why it matters: Believing CONCAT returns NULL can cause unnecessary NULL checks and complicated queries.

Quick: Does CONCAT_WS add separators for NULL values? Commit to yes or no.

Common Belief: CONCAT_WS adds separators even for NULL values, creating extra separators.

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

Why it matters: Misunderstanding this leads to incorrect string formatting with extra separators.

Quick: Does CONCAT_WS skip empty strings like NULLs? Commit to yes or no.

Common Belief: CONCAT_WS treats empty strings the same as NULL and skips them.

Reality: CONCAT_WS includes empty strings and adds separators for them, unlike NULLs.

Why it matters: This can cause unexpected extra separators and formatting issues in output.

Quick: Are CONCAT and CONCAT_WS interchangeable in all cases? Commit to yes or no.

Common Belief: CONCAT and CONCAT_WS can be used interchangeably to join strings.

Reality: They serve different purposes: CONCAT joins directly, CONCAT_WS inserts separators and handles NULLs differently.

Why it matters: Using the wrong function can produce incorrect or messy output.

Expert Zone

1

CONCAT_WS's skipping of NULL but not empty strings can cause subtle bugs in data exports if not carefully handled.

2

Performance impact of CONCAT functions is usually minimal but can accumulate in large-scale queries with many concatenations.

3

Some SQL dialects have different behaviors or additional string concatenation functions, so knowing your database's specifics is crucial.

When NOT to use

Avoid CONCAT and CONCAT_WS when you need complex string formatting or conditional logic; use CASE statements or string formatting functions instead. For very large text processing, consider application-level handling or specialized text functions.

Production Patterns

In production, CONCAT_WS is often used to build CSV-like strings or full addresses from multiple columns, while CONCAT is used for simple joins like full names. Both are combined with COALESCE to handle NULLs explicitly when needed.

Connections

COALESCE function

Builds-on

Knowing COALESCE helps handle NULL values explicitly before concatenation, improving output control.

String join in programming languages

Same pattern

Understanding CONCAT and CONCAT_WS connects directly to string join methods in languages like Python or JavaScript, showing a universal pattern of combining text.

Natural language processing (NLP)

Builds-on

Concatenating strings cleanly is a foundational step in preparing text data for NLP tasks, linking database functions to AI workflows.

Common Pitfalls

#1 Expecting CONCAT to return NULL if any input is NULL.

Wrong approach:

SELECT CONCAT('Hello', NULL, 'World'); -- Expect NULL but gets 'HelloWorld'

Correct approach:

SELECT CONCAT(COALESCE('Hello', ''), COALESCE(NULL, ''), COALESCE('World', '')); -- Explicitly handle NULLs

Root cause:

Misunderstanding how CONCAT treats NULL as empty string instead of propagating NULL.

#2 Using CONCAT_WS with empty strings expecting them to be skipped.

Wrong approach:

SELECT CONCAT_WS(', ', 'Apple', '', 'Cherry'); -- Output: 'Apple, , Cherry'

Correct approach:

SELECT CONCAT_WS(', ', 'Apple', NULLIF('', ''), 'Cherry'); -- Output: 'Apple, Cherry'

Root cause:

Confusing NULL skipping with empty string handling in CONCAT_WS.

#3 Using CONCAT to join strings with separators manually.

Wrong approach:

SELECT CONCAT(first_name, ', ', last_name); -- Works but manual separator

Correct approach:

SELECT CONCAT_WS(', ', first_name, last_name); -- Cleaner and handles NULLs better

Root cause:

Not knowing CONCAT_WS exists to simplify separator insertion and NULL handling.

Key Takeaways

CONCAT joins strings directly, treating NULL as empty, so it never returns NULL unless all inputs are NULL.

CONCAT_WS joins strings inserting a separator and skips NULL values, but includes empty strings, which can affect formatting.

Handling NULLs explicitly with COALESCE or NULLIF can improve control over concatenation results.

Using CONCAT_WS simplifies creating readable lists or combined fields with separators, especially in reports.

Understanding subtle differences between CONCAT and CONCAT_WS prevents common bugs and messy output in SQL queries.

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