SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 3 of 18 · ~3 min

Self Joins and Non-Equi Joins

Slide two is a pay-equity check: flag anyone who might be underpaid relative to a teammate doing the same job, so a manager can go find out whether there's a real reason for the gap. There's no second table involved — "teammate" just means "another row in Employees with the same dept" — so this is a self join: a table joined to itself, using two aliases the way you'd alias any two different tables.

-- Subgoal: for every employee, find a teammate in the same department who earns more
SELECT e1.name AS employee, e2.name AS out_earned_by
FROM Employees e1
JOIN Employees e2 ON e1.dept = e2.dept AND e2.salary > e1.salary;

employee | out_earned_by
---------|---------------
Alice    | Bob

One row. Alice, in Sales, earning $50,000 against Bob's $60,000 — a legitimate flag, worth a manager's five minutes. But look at what's conspicuously not in that result: nothing at all about IT, where Carol earns $70,000 and Dave's salary is NULL. You might expect Carol to show up as out-earning Dave, or the reverse — instead, IT contributes nothing to this query, silently.

The reason is the comparison e2.salary > e1.salary, and it's worth sitting with, because it's the same mechanism this reference keeps coming back to. The moment either side of that > is Dave's NULL, the comparison can't resolve to true or false — it resolves to a third thing, and the join condition treats "not true" exactly like "false." Dave never gets compared and found not to qualify. He gets skipped over, as if the comparison had never been attempted. Hold onto that; the next two chapters exist to explain precisely why.

There's a second label worth pinning on this query too: it's a non-equi join, meaning = isn't the operator doing the matching — here it's > instead. JOIN ... ON never actually demanded equality in the first place; anything that resolves to true or false works, so BETWEEN and <= are equally fair game. You'll meet this constantly outside HR data too — "which shift was this timestamp inside," "which pricing tier does this order size fall into" — anywhere the relationship between two rows is a range rather than a match.

It's worth being clear that "self join" and "non-equi join" are two independent labels, not one combined feature — this query happens to be both at once, but neither one requires the other. You could self-join Employees to itself on plain equality (matching two rows with the same dept, full stop, no salary comparison at all — useful for, say, listing every possible teammate pairing within a department before filtering further). And you could write a non-equi join between two genuinely different tables — matching an order's dollar amount against a PayBands table's min_amount/max_amount range, for instance, with no self-join in sight. This query sits at the intersection of both because that's what the pay-equity question actually needed: the "same table" part comes from comparing employees to each other, and the "non-equi" part comes from asking who out-earns whom rather than who matches whom exactly.