Equi Join

An Equi Join is a type of Join in SQL (Structured Query Language) where two or more tables are combined based on a common column, and the rows are matched using an equality condition (using the = operator). In an equi join, the join condition explicitly states that the values in the columns being joined must be equal.

Equi Join can be performed between two or more tables, based on a column(s) that is common between them, with the = operator used to compare the values of these columns.

Types of Equi Join in Oracle:

1. Inner Join (Equi Join):
• The most common type of equi join, where only matching rows from both tables are included in the result.
• If there is no match between the rows of the two tables, those rows will be excluded from the result set.

Example:

SELECT employees.name, departments.department_name

FROM employees e

INNER JOIN departments d

ON e.department_id = d.department_id;

2. Outer Join (Left, Right, Full):
• In an Outer Join, rows that do not match between the two tables can still be included in the result.
• Left Outer Join: Includes all rows from the left table and the matching rows from the right table. If there is no match, NULL values are shown for columns of the right table.
• Right Outer Join: Includes all rows from the right table and the matching rows from the left table. If no match exists, NULL values are shown for the left table columns.
• Full Outer Join: Includes all rows from both tables. If there is no match, NULL values are shown for columns of the non-matching table.

Example:

SELECT employees.name, departments.department_name

FROM employees e

LEFT OUTER JOIN departments d

ON e.department_id = d.department_id;

Key Concepts of Equi Join:

1. Join Condition:
• The join condition is a requirement that specifies which columns to match between the tables. It is usually based on an equality (=) comparison.
2. Matching Rows:
• When two rows from different tables satisfy the join condition, they are combined into a single row in the result set.
3. Result Set:
• The result set of an equi join contains combined columns from both tables where the join condition is met.
4. Self Join:
• An equi join can also be applied within the same table. This is called a self join. In a self join, you join the table with itself based on a specific condition.

Example:

SELECT e1.name, e2.name AS manager

FROM employees e1

JOIN employees e2

ON e1.manager_id = e2.employee_id;

Syntax of an Equi Join:

Basic Syntax for Equi Join:

SELECT column1, column2, ...

FROM table1 t1

INNER JOIN table2 t2

ON t1.column_name = t2.column_name;

Outer Join Syntax:

SELECT column1, column2, ...

FROM table1 t1

LEFT OUTER JOIN table2 t2

ON t1.column_name = t2.column_name;

Differences Between Equi Join and Other Joins:

• Equi Join vs Inner Join: An Inner Join is a broader term that refers to any type of join that returns only rows with matching values in both tables. An Equi Join is a specific type of Inner Join where the join condition uses the = operator for comparison.
• Equi Join vs Non-Equi Join: In a Non-Equi Join, the join condition uses operators other than = (such as <, >, <=, >=, <>), whereas in an Equi Join, the condition strictly uses the equality (=) operator.

Example of Non-Equi Join:

SELECT employees.name, sales.amount

FROM employees e

JOIN sales s

ON e.salary > s.amount;

Performance Considerations:

• Indexes: Equi joins typically perform better if the columns used in the join condition are indexed.
• Execution Plan: Oracle's query optimizer may choose different execution plans for equi joins, depending on the size of the tables and the presence of indexes.

Practical Usage:

• Database Normalization: Equi Joins are often used in normalized databases, where related data is spread across multiple tables and needs to be combined for meaningful results.
• Reporting: In business intelligence reporting, equi joins are used to merge data from fact tables and dimension tables to generate reports with meaningful insights.

Conclusion:

• An Equi Join is a fundamental concept in SQL, commonly used to combine rows from multiple tables based on an equality condition.
• It is widely used in practice, especially in normalized databases, where data is spread across different tables and needs to be linked together.
• Oracle allows for different types of joins (e.g., Inner Join, Outer Join) based on the specific business requirements.