0Pricing
SQL Interview Prep · Lesson

Comparing Rows Within One Table

Self-join patterns for finding pairs, duplicates, and adjacent records.

Comparing Rows Within One Table is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Self Joins for Row-to-Row Comparison

Beyond hierarchies, the other major use of a self join is comparing rows of the same table to each other. Instead of parent-child, you pair arbitrary rows to find duplicates, near-matches, or adjacent records.

The pattern is the same: alias the table twice and write an ON condition that expresses the relationship between the two rows you want to pair.

Finding Pairs in the Same Group

Classic question: find all pairs of employees who work in the same department. Join the table to itself on equal department, but keep the two rows distinct.

The naive join would also pair every employee with themselves and produce each pair twice. We fix that next.

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b ON a.department = b.department;

Removing Self-Pairs and Mirror Duplicates

Two problems with same-group pairing: a row matches itself (Alice with Alice), and each pair appears twice (Alice-Bob and Bob-Alice).

Fix both with a single inequality: a.id < b.id. This guarantees the two rows are different and keeps only one ordering of each pair.

SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.id < b.id;

Why a.id < b.id and Not a.id <> b.id

Using a.id <> b.id removes self-pairs but still returns both orderings, doubling your results. Using a.id < b.id removes self-pairs and deduplicates the mirror in one stroke.

Interviewers specifically watch for the < versus <> choice; it shows you understand the combinatorics of self joins.

-- <>  keeps Alice-Bob AND Bob-Alice (duplicated)
-- <   keeps only Alice-Bob (correct unique pairs)

Finding Duplicate Rows

To find records that duplicate each other on key columns, self join on those columns and require different primary keys.

Here we surface customers sharing an email address. The a.id < b.id keeps each duplicate pair once. Often a GROUP BY ... HAVING COUNT(*) > 1 is cleaner, but the self join shows the actual offending pairs side by side.

SELECT a.id, b.id, a.email
FROM customers a
JOIN customers b
  ON a.email = b.email
 AND a.id < b.id;

Comparing Adjacent Records

A frequent analyst task: compare each row to the next one in sequence, for example each day's sales versus the prior day. A self join can pair consecutive rows.

Here we join each day to the row exactly one day earlier to compute a delta. This works when the sequence has no gaps.

SELECT t.day, t.amount,
       t.amount - y.amount AS change_vs_prev
FROM daily_sales t
JOIN daily_sales y
  ON y.day = t.day - INTERVAL '1 day';

The Gap Problem With Adjacency Self Joins

The previous query breaks if a day is missing: there is no row exactly one day prior, so that row drops out (inner join) or you must handle NULLs.

This is why interviewers often steer you toward window functions like LAG for 'compare to previous row,' which use ordinal position rather than a value match and tolerate gaps gracefully.

-- LAG handles gaps; the self join assumed contiguous days
SELECT day, amount,
       amount - LAG(amount) OVER (ORDER BY day) AS change_vs_prev
FROM daily_sales;

Self Join vs Window Function

Know the trade-off:

  • A self join compares rows by a value relationship (same dept, prior date). Flexible but can fan out and mishandle gaps.
  • A window function compares by ordinal position within an ordered partition. Cleaner for previous/next-row logic.

For 'compare to the adjacent row,' prefer LAG/LEAD. For 'find all pairs matching a condition,' the self join is the natural tool.

Finding Rows That Beat Their Peers

Another pattern: find employees who earn more than at least one colleague in their department. A self join expresses this directly.

We join each employee to others in the same department who earn less, then keep the distinct employees that appear. This reads almost like the English sentence.

SELECT DISTINCT a.name, a.department, a.salary
FROM employees a
JOIN employees b
  ON a.department = b.department
 AND a.salary > b.salary;

Watch the Fan-Out

Self joins on a non-unique column multiply rows. Pairing within a department of 100 people yields about 100 x 100 candidate pairs before filtering.

Always include the deduplicating predicate (a.id < b.id) and add DISTINCT or grouping when you only need the participating rows, not every pair. Mention this row-multiplication awareness in interviews.

Choosing the Comparison Tool

Decision guide for intra-table comparison:

  • All matching pairs (duplicates, same-group combos): self join with a.id < b.id.
  • Previous/next row in an order: window function (LAG/LEAD).
  • Compare each row to a group aggregate: correlated subquery or window aggregate.

Quick Check

You want every unique pair of products that share the same category, with no product paired to itself and no duplicate orderings.

Recap: Comparing Rows Within One Table

Key takeaways:

  • Self join the table to pair its own rows for duplicate-finding and same-group combinations.
  • Use a.id < b.id to drop self-pairs and mirror duplicates in one predicate.
  • Self-join adjacency comparisons break on gaps; prefer LAG/LEAD for previous/next-row logic.
  • Always account for fan-out when joining on non-unique columns.

Frequently asked questions

Is the “Comparing Rows Within One Table” lesson free?

Yes — the full text of “Comparing Rows Within One Table” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Comparing Rows Within One Table”?

Self-join patterns for finding pairs, duplicates, and adjacent records. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Comparing Rows Within One Table” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. CROSS JOIN and Cartesian Products
  2. SELF JOIN for Hierarchies
  3. Comparing Rows Within One Table
  4. Choosing the Right Join Type
← Back to SQL Interview Prep