Beginner

729. JOIN with SUBSTRING

Use part of a string as a JOIN key when only a portion of a stored value represents the relationship.

📘 Lesson 728 of 728 ⏱ 15 min 🎯 Advanced

SUBSTRING extracts a portion of a string so that the extracted value can participate in a JOIN condition.

Legacy identifiers often contain prefixes, suffixes, or embedded business codes that must be extracted before matching.

Extract a branch code from an account reference and JOIN it to the branch table.

SQL 
SELECT
    t.TransactionID,
    b.BranchName
FROM Transactions t
INNER JOIN Branches b
    ON SUBSTRING(t.TransactionReference, 1, 4)
     = b.BranchCode;

Used in legacy databases, transaction references, file imports, and system integrations.

  • SUBSTRING extracts part of a string.
  • The extracted value can be used in a JOIN.
  • String positions and syntax vary by database.
  • Expression-based JOINs may affect index usage.
  • A persisted normalized key can improve long-term performance.
Why use SUBSTRING in a JOIN?

It allows a portion of a larger identifier to be used as the matching key.

What is the main concern with function-based JOINs?

Applying functions to JOIN columns can make index usage less efficient and increase processing cost.

Have you finished this lesson?

Introduction to SQL – All Lessons