A JOIN in Oracle is used to combine rows from two or more tables based on a related column between them.

Sample Tables

STUDENT Table

DEPARTMENT Table

Step 1: Create DEPARTMENT Table

CREATE TABLE department (
    dept_id NUMBER(2) PRIMARY KEY,
    dept_name VARCHAR2(30) NOT NULL
);

Step 2: Create STUDENT Table

CREATE TABLE student (
    student_id NUMBER(5) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    dept_id NUMBER(2),
    CONSTRAINT fk_department
        FOREIGN KEY (dept_id)
        REFERENCES department(dept_id)
);

Step 3: Insert Data into DEPARTMENT Table

INSERT INTO department VALUES (1, 'Computer');
INSERT INTO department VALUES (2, 'IT');
INSERT INTO department VALUES (3, 'Commerce');
INSERT INTO department VALUES (4, 'Mechanical');

Step 4: Insert Data into STUDENT Table

INSERT INTO student VALUES (101, 'Amit', 1);
INSERT INTO student VALUES (102, 'Neha', 2);
INSERT INTO student VALUES (103, 'Ravi', 3);
INSERT INTO student VALUES (104, 'Pooja', 2);

Step 5: Save the Records

COMMIT;

1. INNER JOIN

Returns only the matching records from both tables.

Syntax

SELECT s.student_id,
       s.name,
       d.dept_name
FROM student s
INNER JOIN department d
ON s.dept_id = d.dept_id;

Output

2. LEFT OUTER JOIN

Returns all records from the left table and matching records from the right table.

Example

SELECT s.student_id,
       s.name,
       d.dept_name
FROM student s
LEFT OUTER JOIN department d
ON s.dept_id = d.dept_id;

If a student has no department, the department column will display NULL.

3. RIGHT OUTER JOIN

Returns all records from the right table and matching records from the left table.

Example

SELECT s.student_id,
       s.name,
       d.dept_name
FROM student s
RIGHT OUTER JOIN department d
ON s.dept_id = d.dept_id;

Output

4. FULL OUTER JOIN

Returns all records from both tables.

Example

SELECT s.student_id,
       s.name,
       d.dept_name
FROM student s
FULL OUTER JOIN department d
ON s.dept_id = d.dept_id;

This displays all students and all departments, with NULL where there is no match.

5. CROSS JOIN

Returns the Cartesian product (every row from the first table combined with every row from the second table).

Example

SELECT s.name,
       d.dept_name
FROM student s
CROSS JOIN department d;

If there are 4 students and 4 departments, the result contains 16 rows.

6. SELF JOIN

A table is joined with itself.

EMPLOYEE Table

Query

SELECT e.emp_name AS Employee,
       m.emp_name AS Manager
FROM employee e
LEFT JOIN employee m
ON e.manager_id = m.emp_id;

Output

7. NATURAL JOIN

Joins tables automatically using columns with the same name.

Example

SELECT *
FROM student
NATURAL JOIN department;

Note: Use NATURAL JOIN carefully because Oracle automatically joins on all columns with the same name.

Oracle JOIN Summary

Practice Question

Create the following tables and write queries for each type of JOIN.

CREATE TABLE department (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(30)
);

CREATE TABLE student (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    dept_id NUMBER,
    CONSTRAINT fk_dept
        FOREIGN KEY (dept_id)
        REFERENCES department(dept_id)
);

Insert sample data:

INSERT INTO department VALUES (1, 'Computer');
INSERT INTO department VALUES (2, 'IT');
INSERT INTO department VALUES (3, 'Commerce');
INSERT INTO department VALUES (4, 'Mechanical');

INSERT INTO student VALUES (101, 'Amit', 1);
INSERT INTO student VALUES (102, 'Neha', 2);
INSERT INTO student VALUES (103, 'Ravi', 3);
INSERT INTO student VALUES (104, 'Pooja', 2);

COMMIT;