NON EQUI Join in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

NON-EQUI Join in Oracle with Examples

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

NON-EQUI Join in Oracle

The NON-EQUI joins is such a join that matches column values from different tables based on inequality (instead of the equal sign like >, <, >=, <= ) expression. The value of the join column in each row in the source table is compared to the corresponding values in the target table. A match is found in the expression based on an inequality operator used in the join, evaluates to true.

Retrieving data from multiple tables based on any condition except equal operator condition is called NON-EQUI join. In the join, we can use the operators such as <,>, <=, >=, and, between, etc.

Understanding Oracle NON-EQUI Join

We are going to use the following Department and Employee tables to understand NON-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');

NON-EQUI Join Example in Oracle:

Write a Query to retrieve Employee and the corresponding department details from Employee and Department tables by using NON-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.

Here, in this article, I try to explain the NON-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.