Beginner

693. JOIN with CASE-Based Key

Use a CASE expression to dynamically determine which value should participate in a JOIN.

📘 Lesson 693 of 728 ⏱ 15 min 🎯 Advanced

A CASE-based JOIN uses conditional logic to select the appropriate matching value depending on the row's attributes.

Legacy systems and business rules sometimes require different relationship keys for different record types.

Use CorporateCustomerID for corporate customers and RetailCustomerID for retail customers.

SQL 
SELECT
    c.CustomerName,
    r.RiskCategory
FROM Customers c
INNER JOIN CustomerRisk r
    ON r.CustomerID =
       CASE
           WHEN c.CustomerType = 'Corporate'
               THEN c.CorporateCustomerID
           ELSE c.RetailCustomerID
       END;

Used in legacy systems, customer integration, migration projects, and complex business mappings.

  • CASE can dynamically select a JOIN key.
  • The logic should be carefully documented.
  • Complex expressions can affect readability.
  • Indexes may be less effective depending on the expression.
  • A normalized mapping table may be a better long-term design.
Can CASE be used inside a JOIN condition?

Yes. CASE can determine which value should be compared in the JOIN.

What is a drawback of CASE-based JOINs?

They can make queries harder to understand and may affect optimization compared with simple key JOINs.

Have you finished this lesson?

Introduction to SQL – All Lessons