Oracle Equi Join FAQs with Examples

1. What is an Equi Join in Oracle?

An Equi Join is a join in which two tables are joined using the equality (=) operator. It returns rows where the values in the join columns are equal.

Syntax (ANSI JOIN)

SELECT column_list
FROM table1 t1
JOIN table2 t2
ON t1.column_name = t2.column_name;

2. Why do we use an Equi Join?

An Equi Join is used to:

• Retrieve related data from multiple tables
• Match rows based on equal values
• Generate reports
• Maintain normalized database design
• Avoid duplicate storage of related data

3. How does an Equi Join work?

Suppose we have:

EMPLOYEES

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID
101	John	10
102	David	20
103	Scott	30

DEPARTMENTS

DEPARTMENT_ID	DEPARTMENT_NAME
10	Sales
20	HR
30	Finance

Query:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Output:

EMPLOYEE_NAME	DEPARTMENT_NAME
John	Sales
David	HR
Scott	Finance

4. What is the syntax of an Equi Join?

Using ANSI JOIN:

SELECT *
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Using old Oracle syntax:

SELECT *
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;

5. Why is it called an Equi Join?

It is called an Equi Join because the join condition uses the equal-to (=) operator.

Example:

ON e.department_id = d.department_id

6. What operators are used in an Equi Join?

Only the equality operator:

=

Operators like >, <, >=, <=, and BETWEEN are used in Non-Equi Joins, not Equi Joins.

7. Difference between an Equi Join and a Non-Equi Join

Equi Join	Non-Equi Join
Uses =	Uses <, >, <=, >=, BETWEEN, etc.
Matches equal values	Matches ranges or inequalities
Most commonly used	Used for range-based relationships

Example of a Non-Equi Join:

SELECT e.employee_name,
       g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary
AND g.max_salary;

8. Can an Equi Join use multiple columns?

Yes.

Example:

SELECT *
FROM orders o
JOIN shipments s
ON o.order_id = s.order_id
AND o.customer_id = s.customer_id;

Both columns must match.

9. Can an Equi Join use aliases?

Yes. Aliases improve readability.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

10. Can an Equi Join use WHERE?

Yes.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

11. Can an Equi Join use GROUP BY?

Yes.

Example:

SELECT d.department_name,
       COUNT(*) AS employee_count
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name;

12. Can an Equi Join use ORDER BY?

Yes.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
ORDER BY d.department_name;

13. Can an Equi Join be used with more than two tables?

Yes.

Example:

SELECT e.employee_name,
       d.department_name,
       l.city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

14. What is the difference between an Equi Join and an Inner Join?

An Equi Join describes the join condition (using =), while an Inner Join describes the join type (returning only matching rows). In practice, most inner joins are equi joins.

Example:

SELECT *
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

This query is both an Inner Join and an Equi Join.

15. Can an Equi Join return duplicate rows?

Yes. If multiple matching rows exist in either table, all matching combinations are returned.

Example: If two employees belong to the same department, both rows are returned.

16. What are the advantages of an Equi Join?

• Simple syntax
• Fast when join columns are indexed
• Retrieves related data efficiently
• Easy to understand
• Most commonly used join type

17. What are the disadvantages of an Equi Join?

• Only works for equality comparisons
• Cannot join ranges
• Requires matching values
• Not suitable for interval or range lookups

18. Common Errors with Equi Join

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00918	Column ambiguously defined
ORA-00933	SQL command not properly ended

19. Real-Time Example

An HR database stores employee and department information separately.

EMPLOYEES

EMPLOYEE_NAME	DEPARTMENT_ID
John	10
David	20

DEPARTMENTS

DEPARTMENT_ID	DEPARTMENT_NAME
10	Sales
20	HR

Query:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Output:

EMPLOYEE_NAME	DEPARTMENT_NAME
John	Sales
David	HR

20. Equi Join vs Other Joins

Join Type	Description
Equi Join	Joins using the = operator
Non-Equi Join	Joins using range or inequality operators
Inner Join	Returns only matching rows
Left Join	Returns all left rows and matching right rows
Right Join	Returns all right rows and matching left rows
Full Outer Join	Returns matching and non-matching rows
Cross Join	Returns the Cartesian product
Self Join	Joins a table to itself

21. Can an Equi Join use the USING clause?

Yes, if the join columns have the same name in both tables.

Example:

SELECT employee_name,
       department_name
FROM employees
JOIN departments
USING (department_id);

This is equivalent to:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

22. Can an Equi Join use the old Oracle (+) syntax?

Yes, for outer joins. For example:

SELECT e.employee_name,
       d.department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id(+);

This performs a left outer join using an equality condition. For modern Oracle development, ANSI JOIN syntax is recommended because it is clearer and easier to maintain.

Key Point:
Equi Join is the most common join pattern in Oracle. It uses equality conditions to match related rows across tables.