Other Join Types - Learning Module

Loading content...

0/241

Non-Equi Joins: Beyond Equality Conditions

When Equality Isn't Enough

Every join we've examined so far has used equality as its matching condition: employee.department_id = department.department_id. These are equi-joins—by far the most common type. But what happens when your business logic requires matching based on ranges, inequalities, or arbitrary conditions?

Consider these scenarios:

Match employees to salary grades where salary falls between grade minimum and maximum
Find all events that occurred before a reference event
Assign prices based on quantity ranges (1-10 units: price A, 11-50: price B)
Identify overlapping time periods (booking conflicts)

None of these can be expressed with simple equality. You need non-equi joins—joins using operators like <, >, <=, >=, <>, BETWEEN, and even complex boolean expressions.

What You Will Learn

By the end of this page, you will understand theta join theory and its relationship to non-equi joins, master the syntax for range-based and inequality joins, recognize common business patterns that require non-equi joins, understand the performance implications and optimization strategies, and implement complex matching logic that goes beyond equality.

Theoretical Foundation: Theta Joins

In relational algebra, the theta join (θ-join) generalizes the concept of combining tuples based on any condition θ (theta). This provides the theoretical framework for all join conditions.

Formal Definition:

Given relations R and S, the theta join is:

R ⋈_θ S = σ_θ (R × S)

This reads as: "The theta join of R and S is the selection (σ) with condition θ applied to the Cartesian product of R and S."

The condition θ can be any boolean expression involving attributes from R and S.

Types of Theta Joins:

Theta Join Classification
Join Type	Condition Form	Example	Usage Frequency
Equi-join	R.a = S.b	employee.dept_id = department.id	Very common (95%+)
Non-equi-join	R.a θ S.b where θ ∈ {<,>,≤,≥,≠}	salary BETWEEN min_sal AND max_sal	Common (specific patterns)
Range join	R.a BETWEEN S.lo AND S.hi	date BETWEEN start_date AND end_date	Common in temporal/range data
Inequality join	R.a <> S.b	Find all pairs where a ≠ b	Less common (specific needs)
Complex theta	Any boolean expression	Multiple AND/OR conditions	Occasional (complex business rules)

Why Non-Equi Joins Matter:

While equi-joins handle foreign key relationships, many real-world data relationships are range-based or categorical rather than identity-based:

Tax brackets (income falls into a range)
Shipping zones (distance falls into categories)
Grade bands (score determines grade)
Time-based pricing (date determines rate)
Overlapping intervals (scheduling conflicts)

These relationships cannot be modeled with simple foreign keys and require non-equi join conditions.

Historical Context

The term 'theta join' comes from using the Greek letter θ (theta) as a placeholder for any comparison operator. While 'equi-join' and 'non-equi join' are the more common terms in practice, understanding the theta join abstraction helps you recognize that JOIN ON can express any condition, not just equality.

Basic Non-Equi Join Syntax

Non-equi joins are expressed using the standard JOIN ... ON syntax with inequality or range operators instead of (or in addition to) equality.

Less Than / Greater Than:

non_equi_comparison.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Find all employees hired BEFORE their manager
SELECT e.employee_name AS employee,
       e.hire_date AS emp_hire_date,
       m.employee_name AS manager,
       m.hire_date AS mgr_hire_date
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
                AND e.hire_date < m.hire_date;
 
-- Note: This is BOTH an equi-join (manager_id = employee_id)
-- AND a non-equi-join (hire_date <) combined with AND
 
-- Pure inequality join: Find all product pairs where
-- product A is cheaper than product B
SELECT a.product_name AS cheaper_product,
       a.price AS lower_price,
       b.product_name AS expensive_product,
       b.price AS higher_price
FROM products a
JOIN products b ON a.price < b.price
WHERE a.category = b.category;  -- Same category

BETWEEN for Range Matching:

The most common non-equi join pattern uses BETWEEN to match values to ranges:

non_equi_between.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Salary grade lookup table
CREATE TABLE salary_grades (
    grade_id INT PRIMARY KEY,
    grade_name VARCHAR(20),
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2)
);
 
INSERT INTO salary_grades VALUES
    (1, 'Junior', 30000, 49999),
    (2, 'Mid-Level', 50000, 74999),
    (3, 'Senior', 75000, 99999),
    (4, 'Lead', 100000, 149999),
    (5, 'Principal', 150000, 999999);
 
-- Match employees to their salary grade
SELECT e.employee_name, e.salary, g.grade_name
FROM employees e
JOIN salary_grades g 
    ON e.salary BETWEEN g.min_salary AND g.max_salary;
 
-- Result:
-- employee_name | salary  | grade_name
-- --------------|---------|------------
-- Alice         | 65000   | Mid-Level
-- Bob           | 95000   | Senior
-- Carol         | 125000  | Lead

Not Equals (<> or !=):

Inequality joins find all pairs where values differ:

non_equi_not_equals.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Find all different product pairs (for comparison feature)
SELECT p1.product_id AS product_a,
       p1.product_name AS name_a,
       p2.product_id AS product_b,
       p2.product_name AS name_b
FROM products p1
JOIN products p2 ON p1.product_id <> p2.product_id
WHERE p1.category_id = p2.category_id;
 
-- Warning: This produces many rows!
-- For N products in a category, you get N × (N-1) pairs
 
-- To avoid duplicates (A,B) and (B,A), add ordering:
SELECT p1.product_id AS product_a,
       p2.product_id AS product_b
FROM products p1
JOIN products p2 ON p1.product_id < p2.product_id
WHERE p1.category_id = p2.category_id;
-- Now each pair appears once (A,B) where A < B

Result Size Warning

Non-equi joins often produce more rows than you expect. Inequality conditions like <> can match many combinations, approaching Cartesian product sizes. Always estimate result size and use LIMIT during development.

Range Join Patterns

Range joins are the most practical application of non-equi joins. They match point values to enclosing intervals or check for interval overlaps.

Pattern 1: Point-in-Range Matching

point_in_range.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Tax bracket lookup
CREATE TABLE tax_brackets (
    bracket_id INT,
    min_income DECIMAL(12,2),
    max_income DECIMAL(12,2),
    tax_rate DECIMAL(5,4),
    PRIMARY KEY (bracket_id)
);
 
INSERT INTO tax_brackets VALUES
    (1, 0, 10000, 0.10),
    (2, 10001, 40000, 0.12),
    (3, 40001, 85000, 0.22),
    (4, 85001, 165000, 0.24),
    (5, 165001, 999999999, 0.32);
 
-- Find tax rate for each customer based on income
SELECT c.customer_name, c.annual_income, 
       t.tax_rate, 
       c.annual_income * t.tax_rate AS estimated_tax
FROM customers c
JOIN tax_brackets t 
    ON c.annual_income BETWEEN t.min_income AND t.max_income;
 
-- Quantity-based pricing
CREATE TABLE quantity_pricing (
    product_id INT,
    min_qty INT,
    max_qty INT,
    unit_price DECIMAL(10,2)
);
 
-- Get price based on order quantity
SELECT o.order_id, o.product_id, o.quantity,
       p.unit_price,
       o.quantity * p.unit_price AS line_total
FROM order_lines o
JOIN quantity_pricing p 
    ON o.product_id = p.product_id  -- First: match product (equi)
    AND o.quantity BETWEEN p.min_qty AND p.max_qty;  -- Then: quantity range

Pattern 2: Temporal Range Matching (Point-in-Time)

temporal_range.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Historical pricing lookup
CREATE TABLE product_prices (
    product_id INT,
    effective_from DATE,
    effective_to DATE,  -- NULL means current
    price DECIMAL(10,2)
);
 
-- Get price that was active on the order date
SELECT o.order_id, o.order_date, o.product_id,
       p.price AS price_at_order_time
FROM orders o
JOIN product_prices p 
    ON o.product_id = p.product_id
    AND o.order_date >= p.effective_from
    AND (o.order_date <= p.effective_to OR p.effective_to IS NULL);
 
-- Exchange rate lookup for currency conversion
CREATE TABLE exchange_rates (
    from_currency CHAR(3),
    to_currency CHAR(3),
    rate_date DATE,
    rate DECIMAL(10,6)
);
 
-- Get exchange rate for each transaction's date
SELECT t.transaction_id, t.amount, t.currency, t.transaction_date,
       e.rate,
       t.amount * e.rate AS converted_amount
FROM transactions t
JOIN exchange_rates e 
    ON t.currency = e.from_currency
    AND e.to_currency = 'USD'
    AND t.transaction_date = e.rate_date;  -- Exact date match
    -- Or use BETWEEN for closest rate if not daily data

Pattern 3: Interval Overlap Detection

Detecting overlapping time ranges is essential for scheduling, resource allocation, and conflict detection:

interval_overlap.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Room booking conflicts: Find overlapping reservations
CREATE TABLE room_bookings (
    booking_id INT PRIMARY KEY,
    room_id INT,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    guest_name VARCHAR(100)
);
 
-- Two intervals [A_start, A_end] and [B_start, B_end] overlap if:
-- A_start < B_end AND A_end > B_start
 
SELECT a.booking_id AS booking_a, 
       b.booking_id AS booking_b,
       a.room_id,
       a.start_time AS a_start, a.end_time AS a_end,
       b.start_time AS b_start, b.end_time AS b_end
FROM room_bookings a
JOIN room_bookings b 
    ON a.room_id = b.room_id           -- Same room
    AND a.booking_id < b.booking_id     -- Avoid self-join and duplicates
    AND a.start_time < b.end_time       -- Overlap condition part 1
    AND a.end_time > b.start_time;      -- Overlap condition part 2
 
-- This finds all pairs of conflicting bookings
 
-- Alternative overlap check using NOT with gap detection:
-- Two intervals DON'T overlap if: A_end <= B_start OR A_start >= B_end
-- Overlap = NOT(no overlap)
SELECT a.booking_id, b.booking_id
FROM room_bookings a
JOIN room_bookings b 
    ON a.room_id = b.room_id
    AND a.booking_id < b.booking_id
    AND NOT (a.end_time <= b.start_time OR a.start_time >= b.end_time);

Overlap Formula

Memorize the interval overlap test: intervals [A_start, A_end] and [B_start, B_end] overlap if and only if A_start < B_end AND A_end > B_start. For inclusive endpoints (closed intervals), use <= and >= instead. This formula applies to dates, times, numbers, or any ordered domain.

Business Application Examples

Non-equi joins solve real business problems that can't be addressed with simple foreign key relationships. Here are detailed examples:

Example 1: Customer Segmentation by Lifetime Value

business_segmentation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Value segments defined by ranges
CREATE TABLE value_segments (
    segment_id INT PRIMARY KEY,
    segment_name VARCHAR(50),
    min_ltv DECIMAL(12,2),
    max_ltv DECIMAL(12,2),
    discount_rate DECIMAL(5,2),
    priority_level INT
);
 
INSERT INTO value_segments VALUES
    (1, 'Bronze', 0, 999.99, 0.00, 4),
    (2, 'Silver', 1000, 4999.99, 0.05, 3),
    (3, 'Gold', 5000, 19999.99, 0.10, 2),
    (4, 'Platinum', 20000, 99999999, 0.15, 1);
 
-- Assign customers to segments based on lifetime value
WITH customer_ltv AS (
    SELECT customer_id, SUM(order_total) AS lifetime_value
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_name, 
       c.email,
       ltv.lifetime_value,
       s.segment_name,
       s.discount_rate,
       s.priority_level
FROM customers c
JOIN customer_ltv ltv ON c.customer_id = ltv.customer_id
JOIN value_segments s 
    ON ltv.lifetime_value BETWEEN s.min_ltv AND s.max_ltv
ORDER BY s.priority_level, ltv.lifetime_value DESC;

Example 2: Distance-Based Shipping Rates

business_shipping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Shipping rate zones based on distance
CREATE TABLE shipping_zones (
    zone_id INT PRIMARY KEY,
    zone_name VARCHAR(50),
    min_distance_km INT,
    max_distance_km INT,
    base_rate DECIMAL(10,2),
    per_kg_rate DECIMAL(10,2)
);
 
INSERT INTO shipping_zones VALUES
    (1, 'Local', 0, 50, 5.00, 0.50),
    (2, 'Regional', 51, 200, 10.00, 0.75),
    (3, 'National', 201, 1000, 20.00, 1.00),
    (4, 'International', 1001, 99999, 50.00, 2.00);
 
-- Warehouse to customer distances (calculated elsewhere)
CREATE TABLE delivery_distances (
    warehouse_id INT,
    customer_id INT,
    distance_km INT
);
 
-- Calculate shipping cost for each order
SELECT o.order_id, o.customer_id, o.weight_kg,
       d.distance_km,
       z.zone_name,
       z.base_rate + (o.weight_kg * z.per_kg_rate) AS shipping_cost
FROM orders o
JOIN delivery_distances d ON o.customer_id = d.customer_id
JOIN shipping_zones z 
    ON d.distance_km BETWEEN z.min_distance_km AND z.max_distance_km;

Example 3: Time-of-Day Pricing (Surge Pricing)

business_surge_pricing.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Time-based pricing multipliers
CREATE TABLE time_pricing (
    pricing_id INT PRIMARY KEY,
    day_type VARCHAR(20),  -- 'weekday', 'weekend', 'holiday'
    start_hour INT,        -- 0-23
    end_hour INT,
    price_multiplier DECIMAL(4,2)
);
 
INSERT INTO time_pricing VALUES
    (1, 'weekday', 0, 5, 0.80),     -- Early morning discount
    (2, 'weekday', 6, 8, 1.50),     -- Morning rush
    (3, 'weekday', 9, 16, 1.00),    -- Standard daytime
    (4, 'weekday', 17, 19, 1.75),   -- Evening rush (surge)
    (5, 'weekday', 20, 23, 1.00),   -- Evening standard
    (6, 'weekend', 0, 23, 1.25);    -- Weekend flat rate
 
-- Calculate actual price for ride requests
SELECT r.ride_id, r.request_time, r.base_fare,
       tp.price_multiplier,
       r.base_fare * tp.price_multiplier AS final_fare
FROM ride_requests r
JOIN time_pricing tp 
    ON (CASE 
            WHEN EXTRACT(DOW FROM r.request_time) IN (0, 6) THEN 'weekend'
            ELSE 'weekday'
        END) = tp.day_type
    AND EXTRACT(HOUR FROM r.request_time) BETWEEN tp.start_hour AND tp.end_hour;

Example 4: Credit Score to Interest Rate Mapping

business_credit_rates.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Interest rates by credit score range
CREATE TABLE credit_rate_table (
    rate_id INT PRIMARY KEY,
    min_score INT,
    max_score INT,
    base_rate DECIMAL(5,4),
    risk_category VARCHAR(20)
);
 
INSERT INTO credit_rate_table VALUES
    (1, 300, 579, 0.1899, 'High Risk'),
    (2, 580, 669, 0.1299, 'Fair'),
    (3, 670, 739, 0.0899, 'Good'),
    (4, 740, 799, 0.0599, 'Very Good'),
    (5, 800, 850, 0.0399, 'Exceptional');
 
-- Determine loan offer for each application
SELECT la.application_id, la.applicant_name, la.credit_score,
       la.loan_amount, la.term_months,
       cr.risk_category,
       cr.base_rate AS interest_rate,
       la.loan_amount * cr.base_rate * (la.term_months / 12.0) AS total_interest
FROM loan_applications la
JOIN credit_rate_table cr 
    ON la.credit_score BETWEEN cr.min_score AND cr.max_score;

Performance Considerations

Non-equi joins present significant performance challenges because they cannot leverage the hash-based and B-tree index lookup optimizations that make equi-joins efficient.

Why Non-Equi Joins Are Expensive:

Join Algorithm Capability
Algorithm	Equi-Join Support	Non-Equi Support	Typical Speed
Hash Join	Excellent (designed for =)	Not applicable	O(n + m) - very fast
Merge Join	Excellent (sorted data)	Limited (<, >, range)	O(n + m) - fast when sorted
Nested Loop	Works (suboptimal)	Required for complex θ	O(n × m) - slow
Index Scan	B-tree: exact lookup	B-tree: range scan	O(log n) per probe

Optimization Strategy 1: Index on Range Boundaries

For range tables (salary grades, price tiers), index the boundary columns:

perf_range_index.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Create indexes on range boundaries
CREATE INDEX idx_salary_grades_range 
    ON salary_grades (min_salary, max_salary);
 
CREATE INDEX idx_tax_brackets_range 
    ON tax_brackets (min_income, max_income);
 
-- The optimizer can use these for range scans
EXPLAIN ANALYZE
SELECT e.employee_name, g.grade_name
FROM employees e
JOIN salary_grades g 
    ON e.salary BETWEEN g.min_salary AND g.max_salary;
 
-- With small lookup tables (< 100 rows), index benefit is minimal
-- Focus optimization on the larger table's join column

Optimization Strategy 2: Pre-Filter Large Tables

Reduce the rows participating in the join before applying expensive non-equi conditions:

perf_prefilter.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- INEFFICIENT: Join first, then filter
SELECT e.*, g.grade_name
FROM employees e
JOIN salary_grades g 
    ON e.salary BETWEEN g.min_salary AND g.max_salary
WHERE e.department_id = 10;
 
-- BETTER: Pre-filter with CTE or subquery
WITH dept_employees AS (
    SELECT * FROM employees WHERE department_id = 10
)
SELECT de.*, g.grade_name
FROM dept_employees de
JOIN salary_grades g 
    ON de.salary BETWEEN g.min_salary AND g.max_salary;
 
-- Most optimizers push predicates down automatically,
-- but CTEs make intent explicit and can help with 
-- complex queries where optimizer heuristics fail

Optimization Strategy 3: Interval Tree / R-tree for Overlap Queries

For heavy overlap query workloads, consider specialized index structures:

perf_interval_index.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- PostgreSQL: Use range types with GiST index
CREATE TABLE reservations (
    id INT PRIMARY KEY,
    room_id INT,
    during TSTZRANGE  -- Timestamp range type
);
 
-- GiST index for efficient overlap queries
CREATE INDEX idx_reservations_during 
    ON reservations USING GIST (during);
 
-- Efficient overlap check using && operator
SELECT a.id, b.id
FROM reservations a, reservations b
WHERE a.room_id = b.room_id
    AND a.id < b.id
    AND a.during && b.during;  -- && means "overlaps"
 
-- PostgreSQL range operators:
-- && : overlaps
-- @> : contains
-- <@ : contained by
-- -|- : adjacent

EXPLAIN Before Execution

Always EXPLAIN ANALYZE non-equi joins before running on large tables. The execution plan reveals whether the optimizer is using indexes or falling back to nested loops. A 'Nested Loop' with 'Seq Scan' on large tables is a red flag—the query may take hours.

Common Mistakes and Edge Cases

Non-equi joins have subtle behaviors that can produce unexpected results. Understanding these edge cases prevents bugs.

Mistake 1: Overlapping Ranges in Lookup Table

mistake_overlapping_ranges.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- PROBLEMATIC: Ranges overlap at boundaries
INSERT INTO salary_grades VALUES
    (1, 'Junior', 30000, 50000),     -- includes 50000
    (2, 'Mid-Level', 50000, 75000);  -- also includes 50000!
 
-- An employee earning exactly 50000 matches BOTH grades!
SELECT e.employee_name, g.grade_name
FROM employees e
JOIN salary_grades g 
    ON e.salary BETWEEN g.min_salary AND g.max_salary
WHERE e.salary = 50000;
 
-- Result: Two rows (one for each matching grade)
-- employee_name | grade_name
-- --------------|------------
-- Alice         | Junior
-- Alice         | Mid-Level
 
-- SOLUTION: Use non-overlapping ranges
-- Option A: Exclusive upper bound
INSERT INTO salary_grades VALUES
    (1, 'Junior', 30000, 49999.99),
    (2, 'Mid-Level', 50000, 74999.99);
 
-- Option B: Half-open intervals [min, max)
SELECT e.employee_name, g.grade_name
FROM employees e
JOIN salary_grades g 
    ON e.salary >= g.min_salary AND e.salary < g.max_salary;

Mistake 2: Missing Ranges (Gaps in Coverage)

mistake_range_gaps.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- PROBLEMATIC: Gap between ranges
INSERT INTO salary_grades VALUES
    (1, 'Junior', 30000, 49999),
    (2, 'Mid-Level', 55000, 74999);  -- Gap: 50000-54999 not covered
 
-- Some employees match NO grade
SELECT e.employee_name, e.salary
FROM employees e
LEFT JOIN salary_grades g 
    ON e.salary BETWEEN g.min_salary AND g.max_salary
WHERE g.grade_id IS NULL;
 
-- Shows employees with salaries in the gap
 
-- SOLUTION: Ensure complete coverage
-- Either fix the data or use COALESCE for default category

Mistake 3: NULL Handling in Range Comparisons

mistake_null_handling.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Open-ended ranges often use NULL for "infinity"
INSERT INTO price_history VALUES
    (1, 101, '2024-01-01', '2024-03-31', 99.99),
    (2, 101, '2024-04-01', NULL, 109.99);  -- NULL = still current
 
-- WRONG: BETWEEN doesn't handle NULL end
SELECT p.product_id, ph.price
FROM products p
JOIN price_history ph ON p.product_id = ph.product_id
    AND '2024-05-15' BETWEEN ph.start_date AND ph.end_date;
-- Returns nothing for product 101 because NULL comparison fails!
 
-- CORRECT: Handle NULL explicitly
SELECT p.product_id, ph.price
FROM products p
JOIN price_history ph ON p.product_id = ph.product_id
    AND '2024-05-15' >= ph.start_date
    AND (ph.end_date IS NULL OR '2024-05-15' <= ph.end_date);

Non-Equi Join Pitfalls

•Overlapping ranges — Multiple matches produce duplicate rows
•Range gaps — Some values match nothing (use LEFT JOIN to detect)
•NULL in comparisons — BETWEEN fails with NULL; handle explicitly
•Incorrect boundary inclusion — Off-by-one errors at range edges
•Self-join duplicates — A<>B produces both (A,B) and (B,A); use A<B
•Large result sizes — Inequality joins can produce near-Cartesian results
•Data type mismatches — Implicit conversions may cause unexpected comparisons

Summary and Key Takeaways

Non-equi joins extend SQL's matching capabilities beyond simple equality, enabling range-based, temporal, and inequality-based data relationships. Let's consolidate what we've learned:

Key Takeaways

•Non-equi joins use operators other than = — Including <, >, <=, >=, <>, BETWEEN, and complex boolean expressions.
•Theta join is the theoretical foundation — Any condition θ can be used in JOIN ON, making all conditional joins possible.
•Range joins match points to intervals — Common for salary grades, price tiers, tax brackets, and similar lookups.
•Interval overlap detection uses (A_start < B_end AND A_end > B_start) — Essential for scheduling conflicts and temporal queries.
•Performance is challenging — Non-equi joins often require nested loops; use indexes and pre-filtering to optimize.
•Watch for overlapping/gapping ranges — Poorly designed lookup tables cause duplicate or missing matches.
•NULL requires explicit handling — BETWEEN fails with NULL; use IS NULL checks for open-ended ranges.
•Test boundary conditions — Off-by-one errors at range edges are common; verify inclusive/exclusive semantics.

Page Complete

You now understand how to apply non-equi joins for range matching, interval overlap detection, and complex business logic. These techniques are essential for real-world scenarios where foreign key relationships don't capture the true data relationships. In the next page, we'll explore self-referential joins—joining a table to itself for hierarchical and comparative queries.