SQL Concatenation, Literals, Alternative Quotes & DISTINCT in Oracle Explained

Introduction

When working with SQL, retrieving data is just the beginning. The real power comes from formatting, transforming, and presenting data in a meaningful way.

That’s where features like:

• Concatenation operator (||)
• Literal values
• Alternative quote operator (q'[]')
• DISTINCT keyword

come into play.

These tools help you make your SQL output:

• More readable
• More professional
• More useful for reporting and analysis

In this guide, we’ll break down these concepts with real examples so you can confidently use them in your daily SQL work.

---

Understanding the Concatenation Operator (||)

The concatenation operator (||) allows you to combine:

• Columns
• Strings
• Values

into a single output column.

---

Example: Combining Columns

SELECT last_name || salary
FROM employees;

What Happens?

• last_name (text) + salary (number)
• Result becomes a string (character expression)

👉 Even numeric data becomes text after concatenation.

---

Important Insight

• Numeric values → Normally right-aligned
• Character values → Left-aligned

After concatenation:

👉 Everything becomes left-aligned (because it’s now text)

---

Making Output More Readable

Let’s improve readability using concatenation.

Without Formatting

SELECT last_name || job_id
FROM employees;

Output:

AbelSA_REP
DaviesST_CLERK

Hard to read, right?

---

Using Literal Strings

We can fix this using literal values.

Example

SELECT last_name || ' is a ' || job_id AS name_and_job
FROM employees;

Output

Abel is a SA_REP
Davies is a ST_CLERK

Much better 👍

---

What Is a Literal?

A literal is:

• A fixed value written directly in SQL
• Can be a string, number, or date

Key Rule

• Character literals must be inside single quotes (‘ ‘)

---

Important Behavior

If your query returns 20 rows:

👉 The literal string appears 20 times

---

Handling Special Characters (Apostrophes)

What if your string contains an apostrophe?

Example (Error)

SELECT last_name || ' isn't a ' || job_id
FROM employees;

❌ This causes an error because ' ends the string prematurely.

---

Solution 1: Escape with Double Quotes

SELECT last_name || ' isn''t a ' || job_id
FROM employees;

✔ Works—but can be messy

---

Solution 2: Alternative Quote Operator

Oracle provides a cleaner solution:

👉 Alternative Quote Operator

---

Using Alternative Quote Operator

Syntax

q'[your string]'

---

Example

SELECT last_name || q'[ isn't a ]' || job_id
FROM employees;

Output

Abel isn't a SA_REP

---

Why Use It?

• Avoid escaping single quotes
• Improve readability
• Cleaner SQL code

---

Custom Delimiters

You can use different delimiters:

q'(text)'
q'{text}'
q'<text>'
q'?text?'

Rules

• Must start with q'
• No spaces between q and delimiter
• Opening and closing delimiters must match

---

Common Mistakes

• ❌ Adding spaces inside operator
• ❌ Using wrong delimiter format
• ❌ Forgetting q

---

Introducing the DISTINCT Keyword

When querying data, you may encounter duplicates.

That’s where DISTINCT comes in.

---

What Does DISTINCT Do?

👉 Removes duplicate rows from output

---

Example Without DISTINCT

SELECT department_id
FROM employees;

Output:

90
90
90
60
60

---

Example With DISTINCT

SELECT DISTINCT department_id
FROM employees;

Output:

90
60

✔ Duplicates removed

---

DISTINCT with Multiple Columns

DISTINCT applies to all selected columns together.

---

Example

SELECT DISTINCT department_id, manager_id
FROM employees;

What Happens?

• Unique combinations are returned
• Duplicate pairs are removed

---

Important Insight

This:

60, 102
60, 103
60, 103

Becomes:

60, 102
60, 103

---

Real-World Use Cases

1. Clean Reports

Remove duplicate entries for clarity.

2. Data Analysis

Get unique values for grouping.

3. Filtering Results

Avoid redundant information.

---

Combining All Concepts

Let’s build a powerful query:

SELECT DISTINCT
       last_name || q'[ works as ]' || job_id AS employee_info
FROM employees;

Result

• Clean output
• No duplicates
• Readable format

---

Best Practices

✔ Use Concatenation for Readability

Make outputs user-friendly.

✔ Prefer Alternative Quotes

Especially for complex strings.

✔ Use DISTINCT Carefully

It can impact performance on large datasets.

✔ Always Use Aliases

Improve clarity in reports.

---

Performance Consideration

DISTINCT requires sorting and comparison.

👉 On large datasets, it may:

• Increase execution time
• Use more resources

Use only when necessary.

---

Common Mistakes to Avoid

• ❌ Forgetting quotes for literals
• ❌ Misusing concatenation with numbers
• ❌ Overusing DISTINCT
• ❌ Not using aliases
• ❌ Incorrect delimiter usage

---

Conclusion

These SQL features may seem simple—but they are incredibly powerful when used correctly.

By mastering:

• Concatenation (||)
• Literal values
• Alternative quote operator
• DISTINCT

You can transform raw data into clear, meaningful, and professional outputs.

---

Final Thought

Good SQL isn’t just about retrieving data.

It’s about presenting it in a way that makes sense.

And these tools help you do exactly that.