Inner Join in Oracle

Using concepts like normalisation, information is arranged into distinct tables in relational databases to minimise duplication and enhance data integrity. This usually means that you have to merge data from two or more related tables to gain a full picture of particular information. Joining tables is the term for this procedure.

Tables, which are made up of rows (each representing a distinct set of data) and columns (describing the type of information), are where relational databases store information. Primary keys are used to uniquely identify rows in a table, while foreign keys create associations across tables by referencing primary keys in other tables.

To illustrate, let’s create a couple of simple tables and populate them with some data:

CREATE TABLE departments (
    dept_id   NUMBER(3) PRIMARY KEY,
    dept_name VARCHAR2(50)
);

INSERT INTO departments (dept_id, dept_name) VALUES (10, 'Sales');
INSERT INTO departments (dept_id, dept_name) VALUES (20, 'Marketing');
INSERT INTO departments (dept_id, dept_name) VALUES (30, 'HR');
INSERT INTO departments (dept_id, dept_name) VALUES (40, 'IT');
INSERT INTO departments (dept_id, dept_name) VALUES (50, 'Finance');
COMMIT;

CREATE TABLE employees (
    emp_id    NUMBER(5) PRIMARY KEY,
    emp_name  VARCHAR2(50),
    salary    NUMBER(10, 2),
    dept_id   NUMBER(3),
    CONSTRAINT fk_dept
        FOREIGN KEY (dept_id)
        REFERENCES departments (dept_id)
);

INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (101, 'Alice', 60000, 10);
INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (102, 'Bob', 75000, 20);
INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (103, 'Charlie', 50000, 10);
INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (104, 'David', 90000, 40);
INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (105, 'Eve', 62000, 30);
INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (106, 'Frank', 88000, NULL);
COMMIT;

Here’s what our tables look like:

DEPARTMENTS Table:

  DEPT_ID DEPT_NAME
--------- ------------------
       10 Sales
       20 Marketing
       30 HR
       40 IT
       50 Finance

EMPLOYEES Table:

  EMP_ID EMP_NAME     SALARY DEPT_ID
-------- -------- ---------- -------
     101 Alice       60000     10
     102 Bob         75000     20
     103 Charlie     50000     10
     104 David       90000     40
     105 Eve         62000     30
     106 Frank       88000

Oracle Join Syntax

Listing every table in the FROM clause and then defining the join conditions in the WHERE clause is the syntax that Oracle has historically used for joins.

Inner Join (Oracle Syntax)

Only the rows from both tables that have matching values are returned by an inner join. A row is excluded from one table if, according to the join condition, it does not have a matching row in the other table.

SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

Output:

EMP_NAME  DEPT_NAME
--------- ------------------
Alice     Sales
Bob       Marketing
Charlie   Sales
David     IT
Eve       HR

Frank (no department) and the Finance department (no personnel) are not included, as you can see. To prevent confusion, you must use the table alias (such as e.dept_id or d.dept_id) to qualify columns with the same name across tables.

Outer Join (Oracle Syntax)

A basic join’s output is expanded by outer joins, which return all rows that meet the join criteria as well as some or all rows from one table for which no rows from the other table meet the requirement. In Oracle’s conventional syntax, the (+) operator is used on the join’s side where you wish to include any unmatched rows.

Left Outer Join (Oracle Syntax): According to Oracle syntax, a left outer join returns every row from the “left” table the one without the (+) as well as any rows from the “right” table that match. The columns of the right table return NULL values if there is no match on the right.

SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id (+);

Output:

EMP_NAME  DEPT_NAME
--------- ------------------
Alice     Sales
Bob       Marketing
Charlie   Sales
David     IT
Eve       HR
Frank

Since there is no department that matches, NULL is returned for dept_name in this case, even though Frank is included. The old (+) syntax in Oracle is only for left or right outer joins; it cannot be used directly for full outer joins.

Cartesian Product: When displaying multiple tables in the FROM clause, Oracle will provide a Cartesian product if the join condition is completely omitted in the WHERE clause. In other words, all of the rows from the first and second tables are concatenated, which typically produces an extremely huge and useless output. For example, if departments have five rows and  employees  have six, a Cartesian product would produce thirty rows.

ANSI SQL Join Syntax

Due to its clarity and portability across other database management systems, ANSI SQL syntax is typically chosen for new development, and Oracle has demonstrated a strong respect for SQL standards. The FROM clause contains direct specifications for the join requirements.

Inner Join (ANSI )

The ON clause defines the join condition once the second table and the INNER JOIN keyword are applied.

SELECT e.emp_name, d.dept_name
FROM employees e INNER JOIN departments d
ON e.dept_id = d.dept_id;

Output:

EMP_NAME  DEPT_NAME
--------- ------------------
Alice     Sales
Bob       Marketing
Charlie   Sales
David     IT
Eve       HR

The outcome is the same as that of the Oracle inner join.

Outer Joins (ANSI Syntax)

For various kinds of outer joins, ANSI SQL offers specific keywords.

Left Outer Join (ANSI LEFT JOIN…ON):

SELECT e.emp_name, d.dept_name
FROM employees e LEFT OUTER JOIN departments d
ON e.dept_id = d.dept_id;

Output:

EMP_NAME  DEPT_NAME
--------- ------------------
Alice     Sales
Bob       Marketing
Charlie   Sales
David     IT
Eve       HR
Frank

The outcome is same to that of the Oracle (+) left outer join. Also, since OUTER is optional, you can use LEFT JOIN.

Right Outer Join (ANSI RIGHT JOIN…ON): The “right” table’s rows as well as any matching rows from the “left” table are returned by the “right outer join” (ANSI RIGHT JOIN…ON). The columns of the left table return NULL values if there is no match on the left.

SELECT e.emp_name, d.dept_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.dept_id = d.dept_id;

Output:

EMP_NAME  DEPT_NAME
--------- ------------------
Alice     Sales
Bob       Marketing
Charlie   Sales
David     IT
Eve       HR
          Finance

This includes the Finance department, which returns NULL for emp_name because it has no employees.

Full Outer Join (ANSI FULL JOIN…ON): The Full Outer Join (ANSI FULL JOIN…ON) function returns every row from both tables, extending them with NULL if the join condition is not met.

SELECT e.emp_name, d.dept_name
FROM employees e FULL OUTER JOIN departments d
ON e.dept_id = d.dept_id;

Output:

EMP_NAME  DEPT_NAME
--------- ------------------
Alice     Sales
Bob       Marketing
Charlie   Sales
David     IT
Eve       HR
Frank
          Finance

Finance (an unmatched department) and Frank (an unmatched employee) are both listed, with NULL where necessary.

Natural Join

An explicit join condition is not necessary for a natural join, which is a unique kind of join. Rather, it automatically combines tables by identifying all of the columns in both tables that share the same name. If similarly named columns don’t indicate a logical join requirement, this can be dangerous but also convenient.

-- For this to work, let's assume a slightly different 'departments' table
-- with an 'id' column instead of 'dept_id' for a natural join to specifically match 'dept_id'
-- However, given our current setup, if we had 'dept_id' as the ONLY common column,
-- a natural join would work. Let's demonstrate with our existing 'dept_id' if that were the only shared column name.
-- If 'dept_id' is the *only* common column, this would be valid.
-- For a natural join to make sense with our tables, we'd need to rename `dept_id` in one table to something else
-- or have a scenario where 'dept_id' is the *only* common column name.

-- Assuming 'dept_id' is the only common column name between 'employees' and 'departments'
-- (which it is in our current schema, as 'emp_id', 'emp_name', 'salary' are unique to employees
-- and 'dept_name' is unique to departments in terms of name).
SELECT emp_name, dept_name
FROM employees NATURAL JOIN departments;

Output:

EMP_NAME  DEPT_NAME
--------- ------------------
Alice     Sales
Bob       Marketing
Charlie   Sales
David     IT
Eve       HR

Because dept_id was the only common column name, the natural join implicitly used it as the join column. The USING clause, which is another feature of ANSI SQL, gives you more flexibility than a natural join by explicitly listing common columns for joining when their names are the same (JOIN departments USING (dept_id)).

Finally, the key to successful database queries is knowing how to merge data from several tables. Because of its clarity, explicit join conditions, and portability, the ANSI SQL JOIN…ON and JOIN…USING syntax is typically advised, even if Oracle’s conventional FROM and WHERE clause syntax is still supported. Whether you need to incorporate unmatched rows from one or both sides of the relationship will determine which join type inner, left outer, right outer, or full outer is best for you.