Course contentsShow
← Data Science
Lesson 947 of 2,14524. SQL JoinsFree lesson

Self-Joins for Comparisons Within a Table

Find pairs of rows meeting certain conditions, such as matching products or comparing records within the same table.

Self-Joins for Comparisons Within a Table

You'll learn how to find pairs of rows within the same table that meet specific comparison criteria.

The Core Idea

Sometimes you need to compare rows within the same table to each other. For example, you might want to find all pairs of products with the same price, or identify employees who work in the same department. A self-join lets you treat a single table as if it were two separate tables, allowing you to compare each row against other rows.

Think of it like this: imagine making two copies of a class roster and placing them side by side. Now you can compare each student on the left list with every student on the right list to find pairs that match some criteria (same hometown, same age, etc.).

How It Works

You join a table to itself using different aliases. Each alias represents a different "copy" of the table, letting you write conditions that compare columns across rows:

SELECT a.product_name AS product_a, 
       b.product_name AS product_b,
       a.price
FROM products a
INNER JOIN products b 
  ON a.price = b.price
  AND a.product_id < b.product_id;

Notice the a.product_id < b.product_id condition? This prevents duplicate pairs (finding Product A matches Product B is the same as finding Product B matches Product A) and stops a product from matching itself.

Common Patterns

  • Finding duplicates: Match rows where key fields are identical
  • Comparing metrics: Find records where one value exceeds another
  • Pairing related items: Identify rows that share common attributes but differ in others

Key Takeaway: Self-joins with comparison conditions let you analyze relationships between rows in the same table, perfect for finding matches, duplicates, or patterns that require row-to-row comparison.