Concatenation with || operator in PostgreSQL - Step-by-Step Execution

Concept Flow - Concatenation with || operator

Start with two strings

↓

Apply || operator

↓

Combine strings end-to-end

↓

Return concatenated string

The || operator takes two strings and joins them together into one longer string.

Execution Sample

PostgreSQL

SELECT 'Hello' || ' ' || 'World' AS greeting;

This query joins three strings: 'Hello', a space, and 'World' into one string 'Hello World'.

Execution Table

Step	Expression Evaluated	Intermediate Result	Action
1	'Hello' || ' '	'Hello '	Concatenate 'Hello' and space
2	'Hello ' || 'World'	'Hello World'	Concatenate previous result with 'World'
3	Return final result	'Hello World'	Output the concatenated string

💡 All parts concatenated, final string returned

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
result	NULL	'Hello '	'Hello World'	'Hello World'

Key Moments - 2 Insights

Why does the space ' ' need to be included explicitly?

Because || just joins strings exactly as they are; it does not add spaces automatically. See execution_table step 1 where 'Hello' and ' ' are joined.

What happens if one side of || is NULL?

The result becomes NULL because concatenating with NULL yields NULL in PostgreSQL. This is not shown here but important to remember.

Visual Quiz - 3 Questions

Look at the execution table, what is the intermediate result after step 1?

A 'HelloWorld'

B 'Hello '

C 'Hello'

D ' World'

💡 Hint

Check the 'Intermediate Result' column in row for step 1

At which step is the final concatenated string formed?

A Step 2

B Step 1

C Step 3

D No final string is formed

💡 Hint

Look at the 'Intermediate Result' after step 2 in the execution table

If we remove the space ' ' from the query, what would the final output be?

A 'Hello '

B 'Hello World'

C 'HelloWorld'

D NULL

💡 Hint

Think about what happens when you concatenate 'Hello' directly with 'World' without space

Concept Snapshot

Concatenation with || operator in PostgreSQL:
- Syntax: string1 || string2
- Joins two strings end-to-end
- Does not add spaces automatically
- NULL on either side results in NULL
- Useful for combining text columns or literals

Full Transcript

This visual execution shows how the PostgreSQL concatenation operator || works. Starting with two strings, the operator joins them end-to-end. In the example, 'Hello' is concatenated with a space ' ', resulting in 'Hello '. Then this result is concatenated with 'World' to form 'Hello World'. The execution table traces each step, showing intermediate results. The variable tracker follows the 'result' variable as it changes. Key moments clarify why spaces must be added explicitly and what happens with NULL values. The quiz tests understanding of intermediate results and final output. This helps beginners see exactly how string concatenation happens step-by-step in SQL.

Practice

1. What does the || operator do in PostgreSQL?

easy

A. Joins two or more text strings into one string

B. Adds two numbers together

C. Compares two strings for equality

D. Deletes a row from a table

Solution

1. Step 1: Understand the operator purpose

   The || operator is used to combine text strings in PostgreSQL.

2. Step 2: Differentiate from other operators

   It does not perform arithmetic or comparison; it concatenates strings.

3. Final Answer:

   Joins two or more text strings into one string -> Option A

4. Quick Check:

   Concatenation = Joins strings [OK]

Hint: Remember: || joins text, not numbers or comparisons [OK]

Common Mistakes:

• Thinking || adds numbers
• Confusing || with equality operator
• Assuming it deletes data

2. Which of the following is the correct syntax to concatenate 'Hello' and 'World' with a space in between using || in PostgreSQL?

easy

A. 'Hello' & ' ' & 'World'

B. 'Hello' + ' ' + 'World'

C. 'Hello' CONCAT ' ' CONCAT 'World'

D. 'Hello' || ' ' || 'World'

Solution

1. Step 1: Identify correct concatenation syntax

   PostgreSQL uses || to join strings, so 'Hello' || ' ' || 'World' is correct.

2. Step 2: Check other options for errors

   Options A and B use invalid operators for strings (& and +); D uses incorrect CONCAT syntax.

3. Final Answer:

   'Hello' || ' ' || 'World' -> Option D

4. Quick Check:

   Use || for string join [OK]

Hint: Use || to join strings, not + or & [OK]

Common Mistakes:

• Using + instead of || for strings
• Using & which is not valid in SQL
• Trying CONCAT keyword without function syntax

3. Given the table users with columns first_name = 'Jane' and last_name = 'Doe', what is the result of this query?

SELECT first_name || ' ' || last_name AS full_name FROM users;

medium

A. Jane || ' ' || Doe

B. JaneDoe

C. Jane Doe

D. NULL

Solution

1. Step 1: Understand concatenation with spaces

   The query joins first_name, a space, and last_name using ||, so the output includes a space between names.

2. Step 2: Apply values from the table

   With 'Jane' and 'Doe', the result is 'Jane Doe'.

3. Final Answer:

   Jane Doe -> Option C

4. Quick Check:

   Concatenate with space = 'Jane Doe' [OK]

Hint: Add spaces manually when joining strings [OK]

Common Mistakes:

• Forgetting to add space between names
• Expecting || to add space automatically
• Confusing output with literal || symbols

4. What is wrong with this query?

SELECT first_name || last_name FROM users WHERE id = 1;

Assuming first_name is 'John' and last_name is NULL for user with id 1.

medium

A. The query returns NULL

B. The query returns 'JohnNULL'

C. The query causes a syntax error

D. The query returns 'John '

Solution

1. Step 1: Understand NULL behavior in concatenation

   In PostgreSQL, concatenating any string with NULL using || results in NULL.

2. Step 2: Apply to given data

   Since last_name is NULL, first_name || last_name returns NULL, not 'JohnNULL'.

3. Final Answer:

   NULL -> Option A

4. Quick Check:

   NULL in concat makes result NULL [OK]

Hint: NULL in concatenation yields NULL result [OK]

Common Mistakes:

• Expecting NULL to convert to 'NULL' string
• Thinking query has syntax error
• Assuming concatenation adds spaces automatically

5. You want to create a full address string by concatenating street, city, and zipcode columns with commas and spaces in between. Which query correctly handles NULL values so the result never becomes NULL?

SELECT street || ', ' || city || ', ' || zipcode AS full_address FROM addresses;

hard

A. Use CONCAT_WS(',', street, city, zipcode) instead of ||

B. Use COALESCE to replace NULLs: COALESCE(street, '') || ', ' || COALESCE(city, '') || ', ' || COALESCE(zipcode, '')

C. Use + operator instead of || to avoid NULL

D. Leave as is; NULLs are ignored in concatenation

Solution

1. Step 1: Identify NULL handling in concatenation

   Using || with NULL returns NULL, so we must replace NULLs with empty strings using COALESCE.

2. Step 2: Apply COALESCE to each column

   Replace each column with COALESCE(column, '') to avoid NULL results.

3. Final Answer:

   Use COALESCE to replace NULLs: COALESCE(street, '') || ', ' || COALESCE(city, '') || ', ' || COALESCE(zipcode, '') -> Option B

4. Quick Check:

   Replace NULLs with COALESCE to avoid NULL output [OK]

Hint: Use COALESCE to avoid NULL in concatenation [OK]

Common Mistakes:

• Assuming NULLs are ignored automatically
• Using + operator which is invalid for strings
• Not handling NULLs causing full result to be NULL