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.