Home » Oracle SQL » Oracle Inner Join with Examples

Oracle Inner Join with Examples

In this tutorial, we will learn how to join two or more than two tables using INNER JOIN in Oracle.

In relational databases, we use database normalization to store related data in different tables and to avoid redundancy. For this reason, we distribute data in several related tables in the HR schema.

Sometimes, we need to source data from multiple tables and for this reason, we use joins.

Syntax:

SELECT
  *
FROM
  Table_1
INNER JOIN Table_2 ON join_condition;

Here,

Table_1 and Table_2 – Tables we want to join

join_condition – The join condition based on the common column between the two tables.

This query returns a data set which is a combination of all matching values from both the tables, provided that it satisfies the join condition.

If a row in the column in join condition contains a NULL value, then that row would be absent from the result. This is only true in case of INNER JOIN and not applicable for LEFT, RIGHT and FULL OUTER JOIN.

The Venn Diagram of Inner Join is as follows:

Examples

Here we will be using Employees, Departments and Locations Table from HR schema for the following examples:

1. Oracle Inner Join Example

Suppose, we simply want to join the Employee and Departments table for displaying Department name alongside Employee name by using Inner Join, then we use this query:

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

Result:

The query joins the Employee and Departments table and returns the department name alongside Employee name.

2. Inner Join with USING clause

We can rewrite the same query as above, by inserting the USING clause in the query instead of ON.

SELECT first_name, last_name, department_name 
FROM employees 
INNER JOIN departments  USING (department_id);

Result:

As we can see, the query returns the same result as above with the only difference being here we used USING clause instead of ON.

3. INNER JOIN using multiple tables

We can also join multiple tables using the INNER JOIN, have a look:

SELECT first_name, last_name,
       department_name, city
FROM employees 
INNER JOIN departments  USING (department_id)
INNER JOIN locations USING (location_id)
ORDER BY department_name;

Result:

Here, we joined the Departments and Locations table with the Employees table using INNER JOIN.

We can also, simply use JOIN in place of INNER JOIN and the query will produce the same result:

SELECT first_name,last_name,
       department_name, city
FROM employees 
JOIN departments  USING (department_id)
JOIN locations USING (location_id)
ORDER BY department_name;

Result:

As, we can see the output is same.

Here, we INNER JOIN to join 4 tables:

SELECT first_name,job_title,
       department_name, city
FROM employees 
INNER JOIN departments  USING (department_id)
INNER JOIN locations USING (location_id)
INNER JOIN jobs USING (job_id)
ORDER BY department_name;

Result: