Self joins and non-equi joins — advanced join patterns

Self Join: A Table Joined to Itself

A self join queries a table as if it were two separate tables by using two aliases. This is the pattern for hierarchical data — an employees table where each row has a manager_id referencing another row in the same table.

SELECT
  e.name       AS employee,
  m.name       AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

The LEFT JOIN ensures employees with no manager (the CEO) still appear, with NULL in the manager column.

Non-Equi Joins: Joining on a Range or Inequality

Join conditions don't have to use =. You can join on BETWEEN, >, <, or any boolean expression. A common case is matching a value to a tier table:

-- Assign discount tier based on order total
SELECT
  o.id,
  o.amount,
  t.tier_name,
  t.discount_pct
FROM orders o
JOIN discount_tiers t
  ON o.amount BETWEEN t.min_amount AND t.max_amount;

Non-equi joins can produce multiple matching rows per input row if ranges overlap — design your tier table with non-overlapping ranges to avoid duplicates.