What are the Different types of Joins in Oracle?

Joins in Oracle

The ability to join tables is a fundamental concept within relational database management systems (RDBMS) such as Oracle, serving as the core mechanism that provides the relational structure and lends the RDBMS its name,,. A join is a query operation that combines rows from two or more tables, views, or materialized views,. The strength of a relational database lies in its capacity to connect information across multiple tables, allowing you to retrieve a complete picture of the data that is not available from any single table alone.

In a join query, multiple tables must appear in the FROM clause. If these tables share a column name, you must qualify all references to those columns throughout the query with table names (or table aliases, also known as tuple variables) to avoid ambiguity,. The component of SQL used for retrieval, including joins, is known as Data Manipulation Language (DML).

Joins and Join Conditions

A join condition, usually included in the FROM or WHERE clauses, is present in the majority of join queries. In this condition, two columns that come from distinct tables are compared. Oracle merges rows that have a join condition that evaluates to TRUE, one row from each table. It is not required that the columns used in the join condition be present in the SELECT list.

Usually, keys are used to establish relationships between tables. A primary key is a column, or set of columns, that gives a table row a unique identity. A foreign key creates a connection between two structures by storing a primary key from one table in another.

The two main join syntaxes that Oracle offers are the ANSI SQL standard syntax, which moves join criteria into the FROM clause, and the conventional Oracle SQL syntax, which places join conditions in the WHERE clause. The ANSI syntax is usually thought to be simpler to administer and understand, and it started to be fully supported in Oracle9i.

Creating an Example Table and Inserting Data

To illustrate the various join types, we will create two sample tables: DEPARTMENTS and STAFF. These tables model a common scenario involving departments and the staff assigned to them. Note that in the STAFF table, one staff member (Elias) has a DEPT_ID (90) that does not exist in the DEPARTMENTS table, and in the DEPARTMENTS table, one department (Marketing, DEPT_ID 40) has no assigned staff. This data intentionally creates unmatched records, which are crucial for demonstrating the behaviour of Outer Joins.

Creating the DEPARTMENTS Table

The CREATE TABLE command is used to define the columns and their datatypes,,.

CREATE TABLE DEPARTMENTS (
    DEPT_ID      NUMBER(2) PRIMARY KEY,
    DEPT_NAME    VARCHAR2(20)
);

INSERT INTO DEPARTMENTS VALUES (10, 'Accounting');
INSERT INTO DEPARTMENTS VALUES (20, 'Finance');
INSERT INTO DEPARTMENTS VALUES (30, 'IT');
INSERT INTO DEPARTMENTS VALUES (40, 'Marketing');

SELECT * FROM DEPARTMENTS;

Output of DEPARTMENTS Table:

(4 rows selected.)

Creating the STAFF Table

CREATE TABLE STAFF (
    STAFF_ID     NUMBER(3) PRIMARY KEY,
    STAFF_NAME   VARCHAR2(20),
    DEPT_ID      NUMBER(2)
);

INSERT INTO STAFF VALUES (101, 'Alice', 10);
INSERT INTO STAFF VALUES (102, 'Bob', 20);
INSERT INTO STAFF VALUES (103, 'Charlie', 20);
INSERT INTO STAFF VALUES (104, 'David', 30);
INSERT INTO STAFF VALUES (105, 'Elias', 90);

SELECT * FROM STAFF;

Output of STAFF Table:

(5 rows selected.)

Types of Joins

Cross Join

An explicit join phrase is not necessary when using a cross join to join two tables. The two tables’ Cartesian product is the outcome of this operation. A Cartesian product is the result of combining each row from the first table with each row from the second table. When two tables with more than one row are connected without naming the joined columns in the WHERE clause, a Cartesian product typically a large and frequently meaningless result set is created. For example, 8,000 rows are produced when a table with 80 rows and one with 100 rows are combined.

Oracle Syntax:

SELECT
    S.STAFF_NAME,
    D.DEPT_NAME
FROM
    STAFF S,
    DEPARTMENTS D;

ANSI Syntax:

SELECT
    S.STAFF_NAME,
    D.DEPT_NAME
FROM
    STAFF S CROSS JOIN DEPARTMENTS D;

Example Output (5 Staff x 4 Departments = 20 rows):

(20 rows selected.)

Outer Joins

A simple join’s output is expanded by an outer join, which returns all rows that meet the join criterion as well as some or all rows from one table for which no rows from the other table meet the requirement. For data retrieval in situations where matches may be sparse or absent, outer joins are helpful.

Left Outer Join

A Left Outer Join yields matching data from the right table as well as all rows from the table designated on the left side of the JOIN keyword (the driving table). If an expression contains columns from the right table and the left row has no match in the right table, Oracle returns NULL.

Oracle Syntax (Old Style): In Oracle Syntax (Old Style), the right table (DEPARTMENTS columns) in this left join context is the “shorter” table (the table may have missing matches), and all of its columns must have the outer join operator (+) applied to them.

SELECT
    S.STAFF_NAME,
    D.DEPT_NAME
FROM
    STAFF S, DEPARTMENTS D
WHERE
    S.DEPT_ID = D.DEPT_ID(+);

ANSI Syntax (New Style):

SELECT
    S.STAFF_NAME,
    D.DEPT_NAME
FROM
    STAFF S LEFT OUTER JOIN DEPARTMENTS D
    ON S.DEPT_ID = D.DEPT_ID;

Example Output: All staff members are returned, including Elias (ID 90), whose department is listed as NULL. Marketing (ID 40) is excluded because it is on the right side.

(5 rows selected.)

Right Outer Join

A Right Outer Join yields matching rows from the left table as well as every row from the table indicated on the right side of the JOIN keyword.

Oracle Syntax (Old Style): The outer join operator (+) must be applied to the columns of the left table (STAFF) in Oracle Syntax (Old Style).

SELECT
    S.STAFF_NAME,
    D.DEPT_NAME
FROM
    STAFF S, DEPARTMENTS D
WHERE
    S.DEPT_ID(+) = D.DEPT_ID;

ANSI Syntax (New Style):

SELECT
    S.STAFF_NAME,
    D.DEPT_NAME
FROM
    STAFF S RIGHT OUTER JOIN DEPARTMENTS D
    ON S.DEPT_ID = D.DEPT_ID;

Example Output: All departments are returned, including Marketing (ID 40), which has no assigned staff, so the staff name is NULL. Elias (ID 90) is excluded because the join is anchored by the right table.

(5 rows selected.)

Full Outer Join

Both tables’ rows are returned by a full outer join. For the columns of the non-matching table, rows that do not meet the ON condition will return NULL values.

The standard syntax for ANSI SQL can be used to do this operation. To do this, two independent outer joins (one left, one right) had to be performed in pre-Oracle9i syntax, and the results had to be combined using the UNION procedure.

ANSI Syntax:

SELECT
    S.STAFF_NAME,
    D.DEPT_NAME
FROM
    STAFF S FULL OUTER JOIN DEPARTMENTS D
    ON S.DEPT_ID = D.DEPT_ID;

Example Output: Both the unmatched staff member (Elias) and the unmatched department (Marketing) are included, with corresponding NULL values in the other table’s columns.

(6 rows selected.)

Natural Join

Instead of requiring an explicit join condition, the Natural Join automatically creates one based on all of the columns in the two tables being linked that have the same name.

The outcome of a join without conditions, which yields a Cartesian product, occurs when the tables being connected share no columns. Column names cannot be preceded by a table alias qualifier (such as S.DEPT_ID) when utilising a natural join.

ANSI Syntax:

SELECT
    STAFF_NAME,
    DEPT_NAME,
    DEPT_ID
FROM
    STAFF NATURAL JOIN DEPARTMENTS;

Example Output: This join is successful because both tables share the column DEPT_ID.

(4 rows selected.)

Advanced and Related Join Concepts

A number of additional methods are employed to retrieve or filter data from various tables:

• Joins of Three or More Tables: The WHERE clause can be used to join multiple tables by setting the common columns to equal one another. It is not necessary for every table to be joined directly to every other table, but links (join conditions) between the tables are typically one fewer than the total number of tables being joined.
• Antijoins and Semijoins: The main purpose of semijoins and antijoins as query strategies is filtering, not retrieving data. The EXISTS operator is frequently used to efficiently implement a semijoin, which locates rows in one table that match at least one row in another. A table’s rows that don’t match any other table’s rows are found using an antijoin. Although an antijoin might typically use NOT IN, this can be slow because it requires a full read of the subquery table. More effective substitutes are frequently employed, like an outer join and testing for NULL values or the NOT EXISTS operator:
• Set Operators (UNION, INTERSECT, MINUS): These are not exactly joins, but they combine the output of several SELECT queries (working on sets of rows) when the columns chosen are of the same type. UNION, for example, aggregates all unique rows from both queries. A right outer join and a left outer join could be combined using UNION to create a full outer join in pre-ANSI SQL. These processes, which depend on internal sorting and impact how quickly the user receives the first row.