Equi Joins and Non-Equi Joins: SQL Fundamentals Explained

In SQL, joins are used to combine rows from two or more tables based on a related column between them. The main types of joins you might encounter are Equi Joins and Non-Equi Joins.

1. Equi Joins

Equi Joins are the most common type of join and involve matching rows based on equality between specified columns in the joined tables. The basic syntax involves using the '=' operator in the 'ON' clause or 'WHERE' clause to specify the condition that connects the tables.

Example:

Consider two tables, 'Employees' and 'Departments':

- Employees:

| EmpID | EmpName | DeptID |

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

| 1 | Alice | 101 |

| 2 | Bob | 102 |

| 3 | Charlie | 101 |

- Departments:

| DeptID | DeptName |

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

| 101 | HR |

| 102 | IT |

| 103 | Finance |

If you want to retrieve all employees along with their department names, you would use an Equi Join:

'''sql

SELECT Employees.EmpName, Departments.DeptName

FROM Employees

JOIN Departments ON Employees.DeptID = Departments.DeptID;

'''

Result:

| EmpName | DeptName |

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

| Alice | HR |

| Bob | IT |

| Charlie | HR |

In this example, the 'ON Employees.DeptID = Departments.DeptID' clause is the condition that creates the Equi Join.

2. Non-Equi Joins

Non-Equi Joins are used when you need to join tables based on a condition other than equality. These joins typically use comparison operators like '>', '<', '<=', '>=', '!=', etc.

Example:

Consider two tables, 'Products' and 'PricingBrackets':

- Products:

| ProductID | ProductName | Price |

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

| 1 | Pen | 1.50 |

| 2 | Notebook | 3.00 |

| 3 | Marker | 2.50 |

- PricingBrackets:

| MinPrice | MaxPrice | Discount |

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

| 0.00 | 2.00 | 5% |

| 2.01 | 3.00 | 10% |

| 3.01 | 5.00 | 15% |

If you want to apply the correct discount to each product based on its price, you would use a Non-Equi Join:

'''sql

SELECT Products.ProductName, Products.Price, PricingBrackets.Discount

FROM Products

JOIN PricingBrackets ON Products.Price BETWEEN PricingBrackets.MinPrice AND PricingBrackets.MaxPrice;

'''

Result:

| ProductName | Price | Discount |

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

| Pen | 1.50 | 5% |

| Notebook | 3.00 | 10% |

| Marker | 2.50 | 10% |

Here, the 'ON Products.Price BETWEEN PricingBrackets.MinPrice AND PricingBrackets.MaxPrice' clause uses a range condition, which is a type of Non-Equi Join.

Summary

- Equi Joins are used when the join condition involves equality ('=').

- Non-Equi Joins are used when the join condition involves other comparison operators like '<', '>', '<=', '>=', or '!='.

Both types of joins are fundamental in SQL for combining data from multiple tables, and they allow for flexible querying based on various conditions.