SQL Joins: Understanding Equi and Non-Equi Types

In SQL, joins are used to combine rows from two or more tables based on a related column between them. There are different types of joins, but they generally fall into two categories: Equi Joins and Non-Equi Joins.

Equi Joins

An Equi Join is a type of join that combines rows from two or more tables based on a condition that compares the equality of specified columns. This is the most common type of join. The 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN', and 'FULL OUTER JOIN' can all be equi joins when they use the equality operator ('=') in the join condition.

Example of an Equi Join:

Suppose you have two tables, 'students' and 'courses':

'students' table:

| student_id | student_name |

|------------|--------------|

| 1 | Alice |

| 2 | Bob |

| 3 | Carol |

'courses' table:

| course_id | student_id | course_name |

|-----------|-------------|-------------|

| 101 | 1 | Math |

| 102 | 2 | Science |

| 103 | 3 | History |

An equi join to combine these tables based on the 'student_id' column:

'''sql

SELECT students.student_id, students.student_name, courses.course_name

FROM students

INNER JOIN courses

ON students.student_id = courses.student_id;

'''

Result:

| student_id | student_name | course_name |

|------------|--------------|-------------|

| 1 | Alice | Math |

| 2 | Bob | Science |

| 3 | Carol | History |

Non-Equi Joins

A Non-Equi Join is a type of join that combines rows from two or more tables based on a condition that uses comparison operators other than the equality operator ('='). These operators include '>, <, >=, <=, !=', etc.

Example of a Non-Equi Join:

Suppose you have two tables, 'products' and 'price_ranges':

'products' table:

| product_id | product_name | price |

|------------|---------------|-------|

| 1 | Laptop | 1200 |

| 2 | Tablet | 600 |

| 3 | Smartphone | 800 |

'price_ranges' table:

| range_id | min_price | max_price | category |

|----------|-----------|-----------|----------------|

| 1 | 0 | 500 | Budget |

| 2 | 501 | 1000 | Mid-Range |

| 3 | 1001 | 1500 | High-End |

A non-equi join to categorize products based on their price:

'''sql

SELECT products.product_name, products.price, price_ranges.category

FROM products

JOIN price_ranges

ON products.price BETWEEN price_ranges.min_price AND price_ranges.max_price;

'''

Result:

| product_name | price | category |

|--------------|-------|-----------|

| Laptop | 1200 | High-End |

| Tablet | 600 | Mid-Range |

| Smartphone | 800 | Mid-Range |

In this example, the join condition uses the 'BETWEEN' operator, which is a form of non-equi join.

Summary

- Equi Joins: Use the equality operator ('=') to combine rows based on matching column values.

- Non-Equi Joins: Use other comparison operators ('>, <, >=, <=, !=', etc.) to combine rows based on non-equality conditions.

Both types of joins are useful in different scenarios, depending on the nature of the data and the specific requirements of the query.