Complex Join Conditions
You'll learn how to build sophisticated join predicates using multiple conditions, inequalities, and functions beyond simple equality checks.
Beyond Basic Equality
So far, you've primarily seen join conditions like ON orders.customer_id = customers.id — simple equality between two columns. But join conditions can be much more powerful. You can combine multiple conditions with AND/OR, use inequality operators (<, >, <=, >=, <>), and even incorporate functions or calculations.
Multiple Conditions in Practice
Imagine matching employees with their managers, but only if they're in the same department and the employee was hired after the manager:
SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.id
AND e.department_id = m.department_id
AND e.hire_date > m.hire_date;
Inequality Joins
Sometimes you need ranges or exclusions. For example, finding all products priced within ±10% of each other:
SELECT p1.name AS product_a, p2.name AS product_b
FROM products p1
INNER JOIN products p2
ON p1.id < p2.id -- Avoid duplicate pairs
AND p2.price BETWEEN p1.price * 0.9 AND p1.price * 1.1;
Functions in Join Conditions
You can use functions you've learned to transform values during the join:
SELECT *
FROM users u
INNER JOIN activity_log a
ON LOWER(u.email) = LOWER(a.user_email)
AND a.log_date BETWEEN u.start_date AND u.end_date;
Key Takeaway: Join conditions aren't limited to simple equality — you can combine multiple conditions with logical operators, use inequality comparisons, and apply functions to create sophisticated matching rules that capture complex real-world relationships.