1. Overview

An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition. An equi join is a subset of inner join that specifically uses the equality operator (=) in the join condition.

In Oracle, an INNER JOIN is used to retrieve rows from two or more tables where there is a match in the columns specified in the ON clause. It ensures that only the rows with matching values in both tables are included in the result set. This tutorial will guide you through understanding, writing, and optimizing INNER JOIN queries in Oracle.

2. Syntax of INNER JOIN

The basic syntax for an INNER JOIN in Oracle is:

SELECT column_list
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

• column_list: The columns you want to retrieve.
• table1 and table2: The tables you are joining.
• ON: The condition specifying the relationship between the columns in the tables.

3. Understanding INNER JOIN with an Example

For the example, we’ll use the following tables:

Employee

+-------------+------------+-----------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
+-------------+------------+-----------+---------------+
| 101         | John       | Doe       | 1             |
+-------------+------------+-----------+---------------+
| 102         | Jane       | Smith     | 2             |
+-------------+------------+-----------+---------------+
| 103         | Sam        | Brown     | 3             |
+-------------+------------+-----------+---------------+
| 104         | Sue        | Green     | 2             |
+-------------+------------+-----------+---------------+

Department

+---------------+-----------------+
| DEPARTMENT_ID | DEPARTMENT_NAME |
+---------------+-----------------+
| 1             | Human Resources |
+---------------+-----------------+
| 2             | Finance         |
+---------------+-----------------+
| 3             | IT              |
+---------------+-----------------+

Location

+---------------+-----------+
| DEPARTMENT_ID |  LOCATION |
+---------------+-----------+
| 1             | New York  |
+---------------+-----------+
| 2             | San Diego |
+---------------+-----------+
| 3             | Seattle   |
+---------------+-----------+

Inner join query

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id;

Output

+-------------+------------+-----------------+
| EMPLOYEE_ID | FIRST_NAME | DEPARTMENT_NAME |
+-------------+------------+-----------------+
| 101         | John       | Human Resources |
+-------------+------------+-----------------+
| 102         | Jane       | Finance         |
+-------------+------------+-----------------+
| 103         | Sam        | IT              |
+-------------+------------+-----------------+
| 104         | Sue        | Finance         |
+-------------+------------+-----------------+

4. Joining Multiple Tables

You can join more than two tables in a single query. For example:

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id
INNER JOIN location l
on d.department_id = l.department_id;

5. Using WHERE Clause with INNER JOIN

You can combine INNER JOIN with a WHERE clause to filter results further:

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id
where department_name = 'Finance';

6. Best Practices for INNER JOIN

• Indexing: Ensure the columns used in the ON condition are indexed for better performance.
• Minimize Columns: Retrieve only the columns you need to reduce overhead.
• Use Aliases: Use table aliases to make your queries more readable.
• Analyze Query Plans: Use tools like EXPLAIN PLAN to understand query performance.

7. INNER JOIN with Subqueries

You can use subqueries within an INNER JOIN to filter or aggregate data:

SELECT e.first_name, d.department_name
FROM Employee e
INNER JOIN (
    SELECT department_id, department_name
    FROM department
    WHERE department_id < 3
) d
ON e.department_id = d.department_id;

Output

+-------------+-----------------+
| EMPLOYEE_ID |    FIRST_NAME   |
+-------------+-----------------+
| John        | Human Resources |
+-------------+-----------------+
| Jane        | Finance         |
+-------------+-----------------+
| Sue         | Finance         |
+-------------+-----------------+

8. Common Mistakes

• Missing ON Clause: Forgetting to specify the join condition results in a syntax error.

SELECT *
FROM Employees e
INNER JOIN Departments d; -- Error: Missing ON clause

• Ambiguous Columns: When two tables have columns with the same name, always qualify them with the table name or alias.

SELECT employee_id, department_id
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id;

• Unnecessary Joins: Avoid joining tables that are not needed for the result set.