Beginner

684. JOIN with DISTINCT Subquery

Use DISTINCT inside a subquery to remove duplicate relationship values before JOINing.

📘 Lesson 684 of 728 ⏱ 14 min 🎯 Advanced

A DISTINCT subquery returns unique combinations of selected columns before the result is used in a JOIN.

Removing duplicate relationship keys can prevent unwanted row multiplication.

Join customers with a unique list of customers who have active accounts.

SQL 
SELECT
    c.CustomerName,
    a.CustomerID
FROM Customers c
INNER JOIN (
    SELECT DISTINCT
        CustomerID
    FROM Accounts
    WHERE AccountStatus = 'Active'
) a
    ON c.CustomerID = a.CustomerID;

Used in data-quality checks, master-data integration, customer analytics, and reporting.

  • DISTINCT removes duplicate selected combinations.
  • It can control JOIN cardinality.
  • Only columns needed for uniqueness should be selected.
  • DISTINCT should not be used blindly to hide duplicate-data problems.
  • The underlying relationship should still be understood.
Why use DISTINCT before a JOIN?

It can ensure that the JOIN input contains unique keys and prevent unwanted row multiplication.

Can DISTINCT hide a data problem?

Yes. It can hide unexpected duplicates instead of addressing their underlying cause.

Have you finished this lesson?

Introduction to SQL – All Lessons