Inner Join in Oracle 

Inner Join in Oracle with Examples

In this article, I am going to discuss the INNER JOIN in Oracle with Examples. Please read our previous article where we discussed the Basics of Joins in Oracle. As part of this article, we are going to discuss the following pointers in detail.

  1. What is Inner Join?
  2. How to implement Inner Join in Oracle?
  3. When do we need to use Inner JOIN?
  4. Example to Understand Inner JOIN
  5. How to use Inner Join with WHERE Condition?
  6. How to Inner Join Three Tables in Oracle?
  7. How to join more than two tables using different joins?
What is Inner Join in Oracle?

The Inner Join in Oracle is used to return only the matching rows from both the tables involved in the join. That means it eliminates the non-matching rows. The INNER JOIN is the most common type of join used in real-time applications. The following diagram represents the pictorial representation of Inner Join in Oracle.

What is Inner Join in Oracle?

How to implement Inner Join in Oracle?

The following images show how to use Inner Join in Oracle. As you can, we can use either the INNER JOIN or JOIN keyword to perform the Inner Join operation in Oracle. That means if you use the JOIN keyword, then also it is going to perform Inner JOIN Operation.

How to implement Inner Join in Oracle?

Note: The INNER JOIN in Oracle returns the rows in the result set where the column value in a row of table1 is equal to the column value in a row of table2. The ON clause defines the columns and conditions to be evaluated in Inner Join.

When do we need to use Inner JOIN?

If you want to retrieve all the matching rows from both the tables involved in the join by removing all the non-matching rows in the result set then you need to use the Inner Join in Oracle. 

Examples to Understand Oracle Inner JOIN:

Let us understand how to implement Inner Join in Oracle with Examples. To understand Inner Join, we are going to use the following Employee, Address, and Projects tables. Here, EmployeeId is the common column that is available in all the tables and based on this column we are going to perform the Inner Join Operations.

When do we need to use Inner JOIN in Oracle?

Please use the following SQL Script to create the Employee, Address, and Projects tables with the required data.

set linesize 500;
DROP TABLE Employee;
CREATE TABLE Employee (
  EmployeeId INT PRIMARY KEY,
  FullName VARCHAR(20),
  Department VARCHAR(10),
  Salary INT,
  Gender VARCHAR(10),
  Age INT
);

INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1007, 'Priyanka Dewangan', 'HR', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Department, Salary, Gender, Age) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26);

DROP TABLE Projects;
CREATE TABLE Projects (
    ProjectId INT PRIMARY KEY,
    ProjectName VARCHAR(50),
    EmployeeId INT
);

INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (1, 'Develop Ecommerse Website from Scratch', 1003);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (2, 'WordPress Website for our company', 1002);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (3, 'Manage our Company Servers', 1007);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (4, 'Hosting account is not working', 1009);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (5, 'MySQL database from my desktop application', 1010);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (6, 'Develop WordPress plugin for my business website', NULL);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (7, 'Migrate web application and database to new server', NULL);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (8, 'Android Application development', 1004);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (9, 'Hosting account is not working', 1001);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (10, 'MySQL database from my desktop application', 1008);
INSERT INTO Projects (ProjectId, ProjectName, EmployeeId) VALUES (11, 'Develop WordPress plugin for my business website', NULL);

DROP TABLE Address;
CREATE TABLE Address
(
    AddressId INT PRIMARY KEY,
    EmployeeId INT,
    Country VARCHAR(10),
    State VARCHAR(20),
    City VARCHAR(10)
);

INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (1, 1001, 'India', 'Odisha', 'BBSR');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (2, 1002, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (3, 1003, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (4, 1004, 'India', 'Odisha', 'Cuttack');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (5, 1005, 'India', 'Maharashtra', 'Nagpur');
INSERT INTO Address (AddressId, EmployeeId, Country, State, City) Values (6, 1006, 'India', 'Odisha', 'Cuttack');
Inner Join Example in Oracle:

Our requirement is to retrieve the EmployeeId, FullName, Department, Gender, ProjectName from the Employee and Projects tables. The query should return the following result set.

Inner Join Example in Oracle

Following is the SQL Query that is Inner Joining the Employee and Projects tables based on the EmployeeID column and will give you the required results.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName
FROM Employee
INNER JOIN Projects
ON Employee.EmployeeID = Projects.EmployeeId;

If you look at the output in the image, here, we got only 8 rows. We did not get those 3 rows that have the NULL value in the EmployeeId column in the Projects table. Instead of using the INNER JOIN keyword in MySQL, we can also use the JOIN keyword as shown in the below SQL Query and it will also give you the same result as the previous Query.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName
FROM Employee
JOIN Projects
ON Employee.EmployeeID = Projects.EmployeeId;

Note: INNER JOIN or JOIN means the same. But it’s always recommended to use INNER JOIN, as it explicitly specifies the type of join operation we are performing.

Inner Join with Where Condition:

Let us understand how to use INNER JOIN along with the WHERE filtering condition in Oracle. Our requirement is to retrieve all the EmployeeId, FullName, Department, Gender, ProjectName from the Employee and Projects tables where the gender is Male. The following is the SQL Script.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName
FROM Employee
INNER JOIN Projects
ON Employee.EmployeeID = Projects.EmployeeId
WHERE Employee.Gender = 'Male';

When you execute the above query, you will get the following result set which includes only Male employees who are currently assigned with projects.

Inner Join with Where Condition

Joining Three Tables in Oracle:

It is also possible in Oracle to join more than two tables. Let us see and understand how to JOIN three tables in Oracle. The following is the syntax in oracle to join three tables.

Joining Three Tables in Oracle

Inner Joining Employee, Projects, and Address tables

Now, our requirement is to fetch the details of such employees who are currently having any projects as well as they must have an address. The following SQL Query, Inner Joining Employee, Projects, and Address tables to fetch the Employee details, projects, and their addresses.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName, Country, State, City
FROM Employee
INNER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId
INNER JOIN Address ON Employee.EmployeeID = Address.EmployeeId;

When you execute the above SQL query, you will get the following output. You can see, we get only four records, this is because the following four records exist in all three tables.

INNER JOIN in Oracle with Real-time Examples

Joining Multiple Tables with Different Joining Condition

It is also possible in Oracle to use Different types of Joins while joining more than two tables. For example, in the below SQL Query, the first joining operation (between Employee and Projects) is performed using Inner JOIN, and the second joining operation (between Employee and Address) is performed using LEFT OUTER JOIN in Oracle.

SELECT Employee.EmployeeID, FullName, Department, Gender, ProjectName, Country, State, City
FROM Employee
INNER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId
LEFT OUTER JOIN Address ON Employee.EmployeeID = Address.EmployeeId;

When you execute the above SQL Query, you will get the following result set. In this case, for the left join between Employee and Address table, it will take null values for Country, State, and City column which does not have any reference.

Joining Multiple Tables with Different Joining Condition

In the next article, I am going to discuss LEFT OUTER JOIN in Oracle with real-time examples. Here, in this article, I try to explain the INNER JOIN in Oracle with Real-time Examples and I hope you enjoy this article.

Leave a Reply

Your email address will not be published. Required fields are marked *