1. Overview

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizer chooses to execute the join, the total size of the columns in the equijoin condition in a single table may be limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE.

Equijoin is a type of join in SQL where two tables are joined based on a condition that compares columns from both tables using the equality operator (=). This is one of the most commonly used joins to fetch data that is logically related between tables.

In Oracle, equijoins are typically implemented using the INNER JOIN syntax or the traditional join syntax in the WHERE clause.

2. Syntax

Using INNER JOIN:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Using WHERE Clause:

SELECT column1, column2, ...
FROM table1, table2
WHERE table1.column_name = table2.column_name;

3. Example

We’ll use the following tables to understand equijoin.

Employee

+-------------+------------+-----------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
+-------------+------------+-----------+---------------+
| 101         | John       | Doe       | 1             |
+-------------+------------+-----------+---------------+
| 102         | Jane       | Smith     | 2             |
+-------------+------------+-----------+---------------+
| 103         | Sam        | Brown     | 3             |
+-------------+------------+-----------+---------------+
| 104         | Sue        | Green     | 2             |
+-------------+------------+-----------+---------------+

Department

+---------------+-----------------+
| DEPARTMENT_ID | DEPARTMENT_NAME |
+---------------+-----------------+
| 1             | Human Resources |
+---------------+-----------------+
| 2             | Finance         |
+---------------+-----------------+
| 3             | IT              |
+---------------+-----------------+

Performing an equijoin

Using INNER JOIN:

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

Output

+-------------+------------+-----------+-----------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_NAME |
+-------------+------------+-----------+-----------------+
| 101         | John       | Doe       | Human Resources |
+-------------+------------+-----------+-----------------+
| 102         | Jane       | Smith     | Finance         |
+-------------+------------+-----------+-----------------+
| 103         | Sam        | Brown     | IT              |
+-------------+------------+-----------+-----------------+
| 104         | Sue        | Green     | Finance         |
+-------------+------------+-----------+-----------------+

Using WHERE Clause:

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id;

The output will be same as earlier.