NON-EQUI joins in SQL

So here's another post about SQL joins. Based on the type of condition we use for joining we distinguish equi joins and non-equi joins.

Simply put:
- equi joins: we're using the equality operator:
tab_a.column_x = tab_b.column_y
- non-equi joins: other operators, like comparison, inequality or BETWEEN are used

While a good portion of the time we use equi joins to, say, lookup the department the employee is part of, non-equi joins are not uncommon either.

Moreover, sometimes we might use both equality and other operators for joining the same table.

Let's look at a simple non-equi join scenario below.

SELECT
  o.order_id,
  o.order_date,
  c.name AS campaign_name,
  o.amount AS list_price_amount,
  d.discount_percentage,
  (1-discount_percentage)*amount AS paid_amount
FROM orders o
JOIN campaign c ON o.order_date BETWEEN c.valid_from AND c.valid_to
JOIN discounts d ON o.amount >= value_from AND o.amount < value_to

---

Enjoyed this? Here are some related articles you might find useful:

• Self-joins in SQL
• Anti-joins in SQL
• SEMI-JOINS in SQL
• NATURAL JOIN in SQL