Inner Join in Oracle

Introduction to Inner Join in Oracle

Inner Join in Oracle is a type of join where the first and second table is matched based on some joins predicate or condition by comparing each row of the first table with the rows of the second table to find the pair of rows that satisfies the join condition and all those pair of rows which satisfies the join condition (non-null values also included while comparing the rows) provided in the query are then combined into a row to form a result set to be displayed as the output.

The Syntax of the inner join

SELECT column [ , column ] FROM t1
INNER JOIN t2
ON t1.column = t2.column;

The inner join visual representation is represented in the below diagram, as in the below diagram the shaded area return as the result of the Oracle Inner Join

The Oracle Inner Join returns the intersect records of t1 and t2 as a result.

Query Examples for Inner Join

Let’s understand the inner join in detail with the help of some of the query examples:

Example #1

Query example for Inner Join

SELECT employee.employee _id, employee.employee_name, department. department_name
FROM employee
INNER JOIN department
ON employee.employee _id = department.employee _id;

This above Oracle INNER JOIN example will return all rows from the employee table and department table where the employee _id value in both the employee table and department table are matched.

We consider here the hr schema which is the oracle database sample schemas. The hr schema contains COUNTRIES, EMPLOYEES, DEPARTMENTS, JOB_HISTORY, JOBS, LOCATIONS, REGIONS tables, in which we are interested or require EMPLOYEES, DEPARTMENTS and LOCATIONS tables.

The description of these tables are –

Table EMPLOYEES

Table DEPARTMENTS

Table LOCATIONS

Example #2

Inner join example for two Tables

Here we would like to retrieve data from two tables EMPLOYEES and DEPARTMENTS. Here query we write to retrieve the employee’s employee id and employee’s first name from the employee’s table and the department’s name from the department’s table –

SELECT employees. employee_id , employees. first_name, departments.department_name
FROM   employees , departments
WHERE  employees. employee_id = departments. department_id;

Output

The column names with the table are preceded by the Oracle SELECT clause. If a column is common to both tables, then for clarity the column name must be prefixed with the table name. The tables from where the data is to retrieve specify in the from clause with the comma-separated. In the WHERE clause specify the join condition. The relation between employees and department tables is determined by the values in the employee_id common column in both tables must be equal and this relation is referred to as an Equi Join. Commonly primary key and foreign keys are involved in this relation.

Example #3

Inner join example of two tables with Aliases

Let us rewrite the above example by using Table Aliases as –

SELECT e. employee_id , e. first_name,
d. department_name
FROM   employees e , departments d
WHERE  e. employee_id = d. department_id;

Output

As in the above query, the table aliases are created in the from clause after the column name and Instead of writing a full table name in the select clause before each column use the table Aliases as e and d in the above query.

Example #4

Inner join example for two tables with Adding Additional Clauses

Next query example where we add additional clauses to our Oracle SELECT statement in where clause for adding aggregations, restricting the rows returned, defining the sorting order and so on. Here is an example query that retrieves all employees who are working in departments Finance, Manufacturing, and Construction.

SELECT e. employee_id , e. first_name,
d. department_name
FROM   employees e , departments d
WHERE  e. employee_id = d. department_id
AND d. department_name in ( 'Finance', 'Manufacturing', 'Construction' );

Output

Example #5

Inner join example of two tables with an order by clause

Next query example, we rewrite the above query by adding the order by clause, therefore displaying the employee’s data sorted by the department name.

SELECT e. employee_id , e. first_name,
d. department_name
FROM   employees e , departments d
WHERE  e. employee_id = d. department_id
AND d. department_name in ('Finance', 'Manufacturing', 'Construction')
ORDER BY d. department_name;

Output

Example #6

Inner Join example for multiple tables join

Sometimes to get the information we may need to join more than two tables. Next, we write the query example to join multiple tables. Consider the example we want to retrieve the employee’s first name from the employee table, the name of the department from department table, and the country’s id of the location where this employee works in the location table.

SELECT e. first_name, e. salary , d. department_name, l. city, l. country_id
FROM  employees e , departments d , locations l
WHERE e. employee_id = d. department_id
AND
d. location_id = l. location_id;

Output

To join four or more tables the same concept applies by adding the table name in FROM clause of an oracle and apply the join condition at the WHERE clause of an oracle.

Conclusion

Inner join is one of the types of join in the oracle database. An inner join used to join the multiple tables and return those rows for which the join condition is or are true. Commonly the primary key and foreign key are involved in inner join to create a relationship between tables.

Recommended Articles

This is a guide to Inner Join in Oracle. Here we discuss the introduction to Inner join along with some examples in detail. You can also go through our other suggested articles to learn more–

1. Joins in MySQL
2. Oracle Queries
3. Oracle Data Warehousing
4. What Is Oracle Database
5. Learn the Different Types of Oracle Versions
6. Inner Join vs Outer Join | Top Differences
7. Learn the MySQL Outer Join