EQUI Join in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

EQUI Join in Oracle with Examples

In this article, I am going to discuss the EQUI Join in Oracle with Examples. Please read our previous article where we discussed NATURAL Join in Oracle with Examples.

EQUI Join in Oracle

An equijoin is such a join that performs against a join condition containing an equality operator. It combines rows of one table associated with one or more rows in another table based on the equality of column values or expressions.

Retrieving data from multiple tables based on “equal operator ( = ) ” is called an EQUI join. When we use EQUI join between two or more tables, the common column (or) common field name does not need to be the same name (but recommended). The common column (or) common field datatype must be matched.

Syntax:

WHERE <TABLE NAME1>.<COMMON COLUMN> = <TABLE NAME2>.<COMMON COLUMN>;
(OR)
WHERE <TN1 ALIAS NAME>.<COMMON COLUMN> = <TN2 ALIAS NAME>.<COMMON COL>;

Understanding EQUI Join

We are going to use the following Department and Employee tables to understand EQUI JOIN in Oracle.

Please use the below SQL script to create and populate the Department and Employee tables with the required sample data.

DROP Table Department;
CREATE TABLE Department (
  DepartmentId INT,
  DepartmentName VARCHAR(15)
);

INSERT INTO Department (DepartmentId, DepartmentName) VALUES (10, 'IT');
INSERT INTO Department (DepartmentId, DepartmentName) VALUES (20, 'HR');
INSERT INTO Department (DepartmentId, DepartmentName) VALUES (30, 'Finance');

DROP Table Employee;
CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  DepartmentId INT,
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1001, 'John', 10, 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1002, 'Smith', 20, 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1003, 'James', 30, 50000, 'Male', 28, 'Delhi');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1004, 'Mike', 30, 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1005, 'Linda', 20, 75000, 'Female', 26, 'Mumbai');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 10, 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 20, 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 10, 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 10, 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, DepartmentId, Salary, Gender, Age, City) VALUES (1010, 'Hina', 20, 75000, 'Female', 26, 'Mumbai');

EQUI Join Example:

Write a Query to retrieve Employee and the corresponding department details from Employee and Department tables by using EQUI join?

SELECT * FROM Employee, Department WHERE DepartmentId = DepartmentId;

When you execute the above query, you will get the following error i.e. ORA-00918: column ambiguously defined.

In the above example, we get an error i.e. “column ambiguously defined”. To overcome this error then we should use a table name as an identity to ambiguous column DepartmentId like below.

SELECT * FROM Employee, Department WHERE Employee.DepartmentId = Department.DepartmentId;
Or
SELECT * FROM Employee E, Department D WHERE E.DepartmentId = D.DepartmentId;

When you execute the above queries, you will get the following output.

Oracle EQUI Joins using AND, IN

The following SQL query returns all the Employee and Department details in which the DepartmentId contains the value 10 or 30.

SELECT * FROM Employee E, Department D
WHERE E.DepartmentId = D.DepartmentId
AND E.DepartmentId IN (10, 30);

When you execute the above queries, you will get the following output.

In the next article, I am going to discuss NON-EQUI JOIN in Oracle with Examples. Here, in this article, I try to explain the EQUI Join in Oracle with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.