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

Self-Joins with Inequality Conditions

Apply non-equality join conditions in self-joins to solve problems like finding overlapping date ranges or sequences.

Self-Joins with Inequality Conditions

You'll learn how to use non-equality operators in self-joins to find overlapping, sequential, or comparative relationships within the same table.

Why Inequality Conditions Matter in Self-Joins

So far, you've used self-joins with equality conditions (like employee.manager_id = manager.id). But some real-world problems require comparing rows using inequality operators like <, >, <=, >=, or BETWEEN.

Think of it like this: imagine you have a calendar of events and want to find which events overlap. You can't just check if start times are equal—you need to check if one event's start is before another event's end, AND its end is after the other's start.

Common Patterns

Finding Overlapping Date Ranges:

SELECT 
    e1.event_name AS event_a,
    e2.event_name AS event_b
FROM events e1
INNER JOIN events e2
    ON e1.event_id < e2.event_id  -- avoid duplicates
    AND e1.start_date <= e2.end_date
    AND e1.end_date >= e2.start_date;

Finding Sequential Records:

SELECT 
    p1.product_name,
    p1.price AS old_price,
    p2.price AS new_price
FROM price_history p1
INNER JOIN price_history p2
    ON p1.product_id = p2.product_id
    AND p2.effective_date > p1.effective_date;

Comparing Values Within Rows:

SELECT s1.student_name, s2.student_name
FROM students s1
INNER JOIN students s2
    ON s1.score > s2.score + 10;  -- students who scored 10+ points higher

The key is combining equality conditions (to match related records) with inequality conditions (to express the comparison logic).

Key Takeaway: Self-joins with inequality conditions unlock powerful pattern-matching capabilities like finding overlaps, sequences, and relative comparisons within a single table—essential for time-series analysis and relational logic.