Equi Join in DBMS: Definition, Example, and Usage

An equi join is a type of join in databases that combines rows from two tables based on matching values in specified columns using the equality operator (=). It returns rows where the values in the joined columns are exactly equal.

How It Works

An equi join works by comparing columns from two tables and selecting rows where the values in these columns are the same. Imagine you have two lists of people: one with their names and IDs, and another with IDs and their phone numbers. An equi join matches the IDs from both lists to combine the related information into one list.

This join uses the equality operator (=) to find matching pairs. It is like matching puzzle pieces that fit perfectly because their edges are equal. The result is a new table that shows combined data only for those rows where the join condition is true.

Example

This example shows how to use an equi join to combine two tables: Employees and Departments, matching them by the DepartmentID column.

sql

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Output

Name       | DepartmentName
-----------|----------------
Alice      | Sales
Bob        | Marketing
Charlie    | Sales

When to Use

Use an equi join when you want to combine data from two tables based on exact matching values in one or more columns. It is very common in relational databases to link related data, such as matching customers to their orders or students to their classes.

For example, if you have a table of orders and a table of customers, you can use an equi join on the customer ID to see which customer placed each order. This helps in reporting, data analysis, and building meaningful views of your data.

Key Points

• An equi join uses the equality operator (=) to match rows.
• It returns combined rows where the join condition is true.
• It is one of the most common types of joins in SQL.
• Equi joins can be written using JOIN ... ON syntax or in the WHERE clause.

Key Takeaways

An equi join matches rows from two tables where specified columns have equal values.

It is used to combine related data from different tables in a database.

The equality operator (=) is the core of an equi join condition.

Equi joins help create meaningful combined views for analysis and reporting.