PostgreSQL Complex JOIN Conditions: Beyond Simple Equality
Most JOINs use simple equality: ON table1.id = table2.id. But PostgreSQL supports any condition in the ON clause - range comparisons, date overlaps, logical conditions, and more. These advanced patterns solve complex business problems.
This guide explores non-equi JOINs and shows when to use them.
What You’ll Learn
This guide covers:
- Non-equi JOINs (>, <, >=, <=)
- Range overlaps and time-based joins
- Multiple ON conditions
- Correlated subqueries in ON
- BETWEEN and IN in joins
- Performance considerations for complex conditions
Understanding ON Conditions
The ON clause isn’t limited to equality. Any Boolean condition works:
-- Simple equality (most common)
ON table1.id = table2.id
-- Range comparison (non-equi join)
ON table1.salary > table2.min_salary
AND table1.salary <= table2.max_salary
-- Date overlap
ON event1.start_date < event2.end_date
AND event1.end_date > event2.start_date
-- Multiple conditions
ON table1.id = table2.id
AND table1.status = 'active'
AND table2.status = 'active'
Non-Equi Joins
Salary Grade Classification
Classify employees into salary bands based on ranges.
-- Create salary grades
CREATE TABLE salary_grades (
grade CHAR(1) PRIMARY KEY,
min_salary DECIMAL(10, 2),
max_salary DECIMAL(10, 2)
);
INSERT INTO salary_grades (grade, min_salary, max_salary) VALUES
('A', 0, 50000),
('B', 50001, 75000),
('C', 75001, 100000),
('D', 100001, 999999);
-- Join employees to salary grades
SELECT
e.emp_id,
e.first_name,
e.last_name,
e.salary,
g.grade,
CASE
WHEN g.grade = 'A' THEN 'Entry Level'
WHEN g.grade = 'B' THEN 'Mid Level'
WHEN g.grade = 'C' THEN 'Senior'
WHEN g.grade = 'D' THEN 'Executive'
END as level
FROM employees e
LEFT JOIN salary_grades g
ON e.salary > g.min_salary
AND e.salary <= g.max_salary
ORDER BY e.salary DESC;
Result:
emp_id | first_name | last_name | salary | grade | level
-------+------------+-----------+---------+-------+---------
7 | David | Miller | 105000 | D | Executive
8 | Jennifer | Martinez | 102000 | D | Executive
9 | Richard | Rodriguez | 98000 | C | Senior
1 | James | Wilson | 75000 | B | Mid Level
The non-equi join condition e.salary > g.min_salary AND e.salary <= g.max_salary matches each employee to their grade range.
Commission Calculation Based on Sales
Pay different commission rates based on total sales achieved.
CREATE TABLE commission_rates (
commission_level INTEGER,
min_sales DECIMAL(12, 2),
max_sales DECIMAL(12, 2),
commission_pct DECIMAL(5, 2)
);
INSERT INTO commission_rates (commission_level, min_sales, max_sales, commission_pct) VALUES
(1, 0, 50000, 2.0),
(2, 50001, 100000, 3.5),
(3, 100001, 250000, 5.0),
(4, 250001, 999999, 7.5);
-- Employee sales and commission
SELECT
e.emp_id,
e.first_name,
e.salary,
es.total_sales,
cr.commission_pct,
(es.total_sales * cr.commission_pct / 100) as commission_earned
FROM employees e
LEFT JOIN employee_sales es ON e.emp_id = es.emp_id
LEFT JOIN commission_rates cr
ON es.total_sales > cr.min_sales
AND es.total_sales <= cr.max_sales
WHERE es.total_sales > 0
ORDER BY es.total_sales DESC;
This calculates commission based on achievement tier, not a fixed rate.
Range and Overlap Joins
Date Range Overlap
Find employees and projects they worked on during overlapping timeframes.
-- Projects have start and end dates
CREATE TABLE project_assignments (
assignment_id INTEGER PRIMARY KEY,
emp_id INTEGER,
start_date DATE,
end_date DATE
);
INSERT INTO project_assignments (assignment_id, emp_id, start_date, end_date) VALUES
(1, 1, '2024-01-15', '2024-06-30'),
(2, 2, '2024-03-01', '2024-08-15'),
(3, 1, '2024-05-01', '2024-12-31');
-- Find overlapping projects
SELECT
pa1.assignment_id as project_1,
pa2.assignment_id as project_2,
e.first_name,
e.last_name,
GREATEST(pa1.start_date, pa2.start_date) as overlap_start,
LEAST(pa1.end_date, pa2.end_date) as overlap_end
FROM project_assignments pa1
INNER JOIN project_assignments pa2
ON pa1.emp_id = pa2.emp_id
AND pa1.assignment_id < pa2.assignment_id
AND pa1.start_date < pa2.end_date
AND pa1.end_date > pa2.start_date
INNER JOIN employees e ON pa1.emp_id = e.emp_id
ORDER BY e.last_name;
Result:
project_1 | project_2 | first_name | last_name | overlap_start | overlap_end
-----------+----------+------------+-----------+---------------+-------------
1 | 3 | James | Wilson | 2024-05-01 | 2024-06-30
The condition pa1.start_date < pa2.end_date AND pa1.end_date > pa2.start_date detects date overlaps.
Temporal Overlaps in Events
Find scheduling conflicts.
CREATE TABLE meetings (
meeting_id INTEGER PRIMARY KEY,
room_id INTEGER,
meeting_title VARCHAR(100),
start_time TIMESTAMP,
end_time TIMESTAMP
);
INSERT INTO meetings VALUES
(1, 1, 'Team Standup', '2026-02-21 09:00', '2026-02-21 09:30'),
(2, 1, 'Sprint Planning', '2026-02-21 09:15', '2026-02-21 10:00'),
(3, 1, 'Budget Review', '2026-02-21 10:30', '2026-02-21 11:30');
-- Find room conflicts
SELECT
m1.meeting_id as meeting_1,
m1.meeting_title,
m1.start_time,
m1.end_time,
m2.meeting_id as meeting_2,
m2.meeting_title,
m2.start_time,
m2.end_time,
'CONFLICT' as status
FROM meetings m1
INNER JOIN meetings m2
ON m1.room_id = m2.room_id
AND m1.meeting_id < m2.meeting_id
AND m1.start_time < m2.end_time
AND m1.end_time > m2.start_time
ORDER BY m1.start_time;
Result:
meeting_1 | meeting_title | start_time | end_time | meeting_2 | meeting_title | start_time | status
-----------+---------------------+----------------------+----------------------+-----------+----------------+----------------------+---------
1 | Team Standup | 2026-02-21 09:00:00 | 2026-02-21 09:30:00 | 2 | Sprint Planning| 2026-02-21 09:15:00 | CONFLICT
Meetings 1 and 2 overlap in the same room.
Complex Multi-Condition Joins
Multiple AND Conditions
Employees and projects with status and date filtering.
SELECT
e.first_name,
e.last_name,
d.dept_name,
p.project_name,
p.budget,
p.status
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id
INNER JOIN projects p
ON d.dept_id = p.dept_id
AND p.status = 'active'
AND p.start_date <= CURRENT_DATE
AND (p.end_date IS NULL OR p.end_date >= CURRENT_DATE)
WHERE e.hire_date <= '2020-01-01'
ORDER BY d.dept_name;
The ON clause filters on:
- Department match (
d.dept_id = p.dept_id) - Project status (
p.status = 'active') - Start date is not in future (
p.start_date <= CURRENT_DATE) - End date is not in past (or ongoing)
OR Conditions in ON
Employees assigned to projects OR their manager supervises the project.
-- Assume projects have a manager_id column
SELECT
e.first_name,
e.last_name,
p.project_name,
CASE
WHEN e.emp_id = pa.emp_id THEN 'Direct Assignment'
WHEN e.emp_id = p.manager_id THEN 'Manages Project'
ELSE 'Other'
END as relationship
FROM employees e
INNER JOIN projects p ON p.manager_id IS NOT NULL
LEFT JOIN project_assignments pa
ON (e.emp_id = pa.emp_id AND pa.project_id = p.project_id)
OR (e.emp_id = p.manager_id)
WHERE pa.assignment_id IS NOT NULL
OR e.emp_id = p.manager_id
ORDER BY e.last_name, p.project_name;
Combines assignment and management relationships in one query.
Subqueries in ON Conditions
Join to Aggregated Values
Find employees earning more than their department’s average.
SELECT
e.emp_id,
e.first_name,
e.last_name,
e.salary,
d.dept_name,
d.avg_salary,
e.salary - d.avg_salary as salary_above_average
FROM employees e
INNER JOIN (
SELECT
dept_id,
dept_name,
AVG(salary) as avg_salary
FROM employees
JOIN departments USING (dept_id)
GROUP BY dept_id, dept_name
) d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary
ORDER BY d.dept_name, e.salary DESC;
Result:
emp_id | first_name | last_name | salary | dept_name | avg_salary | salary_above_average
-------+------------+-----------+--------+---------------+------------+---------------------
7 | David | Miller | 105000 | Engineering | 97625 | 7375
8 | Jennifer | Martinez | 102000 | Engineering | 97625 | 4375
9 | Richard | Rodriguez | 98000 | Engineering | 97625 | 375
The subquery calculates department averages, then the ON condition matches employees to their department’s data.
Correlated Subquery in ON
Find employees with their most recent salary record.
SELECT
e.emp_id,
e.first_name,
e.last_name,
s.salary,
s.effective_date
FROM employees e
LEFT JOIN salaries s
ON e.emp_id = s.emp_id
AND s.effective_date = (
SELECT MAX(effective_date)
FROM salaries
WHERE emp_id = e.emp_id
)
ORDER BY e.last_name;
Result:
emp_id | first_name | last_name | salary | effective_date
-------+------------+-----------+---------+----------------
1 | James | Wilson | 75000 | 2023-06-01
2 | Mary | Johnson | 78000 | 2023-01-15
7 | David | Miller | 105000 | 2023-07-01
The correlated subquery (SELECT MAX(effective_date) WHERE emp_id = e.emp_id) finds the latest salary per employee, then the ON condition matches to it.
BETWEEN in Joins
Date Range Within Project Lifespan
Find employees hired during specific projects.
SELECT
e.emp_id,
e.first_name,
e.hire_date,
p.project_name,
p.start_date,
p.end_date
FROM employees e
INNER JOIN projects p
ON e.hire_date BETWEEN p.start_date
AND COALESCE(p.end_date, CURRENT_DATE)
ORDER BY p.start_date, e.hire_date;
Finds employees hired while their department’s projects were active.
IN and EXISTS in Joins
Join Using IN
Find employees in specific departments.
SELECT
e.emp_id,
e.first_name,
e.last_name,
d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IN (
SELECT dept_id
FROM departments
WHERE location IN ('New York', 'San Francisco')
)
ORDER BY e.last_name;
Gets employees from specific locations.
EXISTS in Join
SELECT
e.emp_id,
e.first_name,
d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE EXISTS (
SELECT 1
FROM projects p
WHERE p.dept_id = d.dept_id
AND p.budget > 200000
)
ORDER BY e.last_name;
Finds employees in departments with high-budget projects.
Window Functions with Complex Joins
Rank Within Joined Results
Rank employees by salary within matched project groups.
SELECT
e.emp_id,
e.first_name,
p.project_name,
e.salary,
RANK() OVER (
PARTITION BY p.project_id
ORDER BY e.salary DESC
) as salary_rank_in_project
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN projects p ON d.dept_id = p.dept_id
WHERE p.budget > 150000
ORDER BY p.project_name, salary_rank_in_project;
Ranks employees within each project by salary.
Common Errors & Solutions
Error: “Unexpected join results”
Problem:
SELECT e.*, p.*
FROM employees e
LEFT JOIN projects p
ON e.salary > p.budget / 10 -- Vague condition
WHERE e.salary IS NOT NULL;
Result is unpredictable and potentially Cartesian.
Solution:
Clarify the join intent with specific conditions:
SELECT e.first_name, p.project_name
FROM employees e
LEFT JOIN projects p
ON e.dept_id = p.dept_id
AND e.salary > (p.budget / 10)
WHERE p.project_id IS NOT NULL;
Error: “Performance degradation”
Problem:
-- Slow: Correlated subquery in ON for every row
FROM employees e
LEFT JOIN salaries s
ON s.emp_id = e.emp_id
AND s.effective_date = (SELECT MAX(effective_date)
FROM salaries WHERE emp_id = e.emp_id)
Solution:
Use window functions or aggregate in a CTE:
WITH latest_salaries AS (
SELECT
emp_id,
salary,
effective_date,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) as rn
FROM salaries
)
SELECT e.first_name, ls.salary
FROM employees e
LEFT JOIN latest_salaries ls
ON e.emp_id = ls.emp_id AND ls.rn = 1;
Error: “NULL values where you don’t expect them”
Problem:
FROM employees e
INNER JOIN salary_grades g
ON e.salary > g.min_salary AND e.salary <= g.max_salary
WHERE e.salary > 100000;
Got fewer results than expected.
Cause: The join condition has a gap or overlap in ranges.
Solution:
Debug with EXPLAIN ANALYZE and verify ranges are complete:
SELECT * FROM salary_grades ORDER BY min_salary;
Ensure no gaps between grades.
Performance Tips for Complex Conditions
1. Use Indexes on Join Columns
CREATE INDEX idx_employees_salary ON employees(salary);
CREATE INDEX idx_salary_grades_min ON salary_grades(min_salary, max_salary);
Indexes help non-equi joins too, though less efficiently than equality joins.
2. Avoid Expensive Functions in ON
-- Slow: Function called for each row
ON LOWER(e.name) = LOWER(d.dept_name)
-- Better: Pre-compute or use database function indexes
ON e.dept_id = d.dept_id
3. EXPLAIN ANALYZE Complex Joins
EXPLAIN ANALYZE
SELECT ...
FROM employees e
LEFT JOIN salary_grades g
ON e.salary > g.min_salary AND e.salary <= g.max_salary;
Look for “Nested Loop” vs “Hash Join”. Nested loops on complex conditions can be slow.
4. Consider Derived Tables
-- Complex join
WITH graded_employees AS (
SELECT e.*, g.grade
FROM employees e
LEFT JOIN salary_grades g ON e.salary > g.min_salary AND e.salary <= g.max_salary
)
SELECT * FROM graded_employees WHERE grade = 'C';
Breaking into stages often clarifies intent and can improve performance.
FAQ
Q: When should I use non-equi joins? A: For range matches (salary bands, commission tiers), date overlaps, and complex business logic. Use sparingly - they’re slower than equality joins.
Q: Is a non-equi join slower than INNER JOIN? A: Generally yes. Non-equi joins can’t use simple index joins as efficiently. Test with EXPLAIN ANALYZE.
Q: Can I use >= and <= in ON? A: Yes, and it’s common for range joins.
Q: Should I put complex conditions in ON or WHERE? A: ON affects which rows are joined. WHERE filters results after joining. Use ON for conditions involving multiple tables, WHERE for single-table filtering.
Q: What’s the difference between BETWEEN and >= / <=?
A: BETWEEN is syntactic sugar. salary BETWEEN min AND max equals salary >= min AND salary <= max. Performance is identical.
Q: Can I use CASE in ON conditions?
A: Yes: ON CASE WHEN ... THEN ... ELSE ... END. But it’s slower than direct conditions.
Related Topics
Master advanced query patterns:
- PostgreSQL JOINs Complete Guide - All JOIN fundamentals
- Multiple JOINs in Single Query - Chaining complex joins
- INNER JOIN Examples - Practical patterns
Complex JOIN conditions unlock powerful data analysis. Use them when simple equality doesn’t capture your business logic.