SQL Joins in Oracle (19c / 23ai) - A short explanation

 SQL Joins in Oracle

A join is a query that combines rows from two or more tables, views or materialized views. Oracle Database performs join whenever multiple tables appear in the FROM clause of the query.

The SELECT list of the query can select any column from any of these tables.

ANSI & Non-ANSI syntax for SQL Joins

There are both ANSI and Non-ANSI (Old) styles for writing SQL queries with joins.

The non-ANSI style is the old, traditional and oracle proprietary way of writing joins where the join is specified in the WHERE clause.

The ANSI standard in the modern, standard SQL style that clearly separates join condition from filtering condition. The join condition is mentioned in the FROM Clause.

It is always recommended to write joins using ANSI standards as it is more readable, easier to maintain, portable to other databases.

We will discuss both styles of writing joins in our explanation and examples below. 

SQL Joins can be broadly divided into 3 types: Inner Join, Outer Join & Cross Join

Inner Join can be further classified into Equi Join, Natural Join and Self Join.
Outer join can be further classified into Left Outer Join, Right Outer Join and Full Outer Join.

Let's review these join types with example.

1. Inner Join

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. The join condition can be equality (=) operator or < , >, IN , BETWEEN etc.

Example:

ANSI Syntax -->

SELECT *
  FROM salary s INNER JOIN grade g
    ON s.amount BETWEEN g.low_sal AND g.high_sal;

You also can write only JOIN (instead of INNER JOIN) as a shot form as INNER is default in the JOIN clause.

Non-ANSI Syntax -->

SELECT *

 FROM salary s, grade g
WHERE s.amount BETWEEN g.low_sal AND g.high_sal;

• Equi Join

        Equi Join is a special type of inner join where the join condition can only be an equality (=) operator. Equi Join can be specified either with ON clause or USING clause in the ANSI syntax.

Example:

ANSI Syntax -->

ON Clause

SELECT e.emp_id, e.name, d.dept_name
   FROM employees e JOIN departments d
        ON e.emp_id = d.emp_id;

 Using clause

SELECT e.emp_id, e.name, d.dept_name
   FROM employees e JOIN departments d
        USING (emp_id);

USING clause can be used when the joining column names on both the tables are same.

Non-ANSI syntax

SELECT e.emp_id, e.name, d.dept_name
  FROM employees e, departments d
      WHERE e.emp_id = d.emp_id;

• Natural Join

Natural Join is only available in ANSI syntax. Natural Join is one form of Equi Join where Oracle automatically joins on the columns from both the tables with the same name and same data type.

You need not specify the joining column names in the ON or USING clause.

Example:

SELECT e.emp_id, e.name, d.dept_name

  FROM employees e NATURAL JOIN departments d;

• Self Join

 An equijoin can also happen between the same table on both side of the JOIN clause. In that case, it is called Self Join.

Example (ANSI Syntax) :

SELECT e1.employee_id, e1.name, e2.name manager_name

FROM employees e1 JOIN employees e2

      ON e1.manager_id = e2.emp_id; 

2. Outer Join

Outer join extends the result of an inner join. An outer join returns all the rows that satisfy the join condition and it also returns those rows from either one of the table (depending on the type of outer join) or both the tables for which no rows that satisfy the join condition.

Outer join can be Left outer join, Right Outer Join and Full Outer Join. They can also be specified as LEFT JOIN, RIGHT JOIN and FULL JOIN in a SQL query (Outer is default in such cases).

• Left Outer Join

The Left outer join returns all the rows from both the table that satisfy the join condition and it also returns all the rows from the table that is mentioned on the left side of the join that do not satisfy the join condition. For such rows from left side table which do not satisfy the join condition, NULL values are returned for the right-side table.

This is depicted on the diagram where you will get all the rows from the left table which is marked in blue.

Example:

ANSI Syntax ->

SELECT e.emp_id, e.name, d.dept_name

  FROM employees e LEFT OUTER JOIN departments d

       ON e.emp_id = d.emp_id;

SELECT e.emp_id, e.name, d.dept_name

  FROM employees e LEFT JOIN departments d

       USING(emp_id);

Non-ANSI Syntax ->

SELECT e.emp_id, e.name, d.dept_name

  FROM employees e, departments d

 WHERE e.emp_id = d.emp_id(+);

• Right Outer Join

The Right outer join returns all the rows from both the table that satisfy the join condition and it also returns all the rows from the table that is mentioned on the Right side of the join that do not satisfy the join condition. For such rows from Right side table which do not satisfy the join condition, NULL values are returned for the left-side table.

This is depicted on the diagram where you will get all the rows from the right table which is marked in blue.

 

Example:

ANSI Syntax ->

-- ON Clause 

SELECT e.emp_id, e.name, d.dept_name

   FROM employees e RIGHT OUTER JOIN departments d

        ON e.emp_id = d.emp_id;

 -- USING Clause

SELECT e.emp_id, e.name, d.dept_name

   FROM employees e RIGHT JOIN departments d

        USING(emp_id);

 

Non-ANSI Syntax ->

SELECT e.emp_id, e.name, d.dept_name
  FROM employees e, departments d
      WHERE e.emp_id(+) = d.emp_id;

• Full Outer Join

The Full outer join returns all the rows from both the table that satisfy the join condition and it also returns all the rows from both left side and right  side table that do not satisfy the join condition. For such rows which do not satisfy the join condition, NULL values are returned from the table which do not have the matching row.

This is depicted on the diagram below. You will get all the rows from both the left and right side table (marked in blue) with the matching rows listed only once (The overlapping area).

You can not write Full Outer join in Non ANSI syntax. You must use the ANSI syntax.

Example (ANSI Syntax):

SELECT e.emp_id, e.name, d.dept_name

   FROM employees e FULL OUTER JOIN departments d

        ON e.emp_id = d.emp_id;

 

SELECT e.emp_id, e.name, d.dept_name

   FROM employees e FULL JOIN departments d

        USING(emp_id);

3. Cross Join or Cartesian Join

If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates a large number of rows and is rarely useful.

For Example, the Cartesian product of two tables, each with 100 rows, has (100 x 100) 10,000 rows. 

In ANSI syntax, CROSS JOIN clause without any join condition is used in SQL queries to get the Cartesian product result. In Non-ANSI syntax, there is no join condition in the WHERE clause.

Example:

ANSI Syntax ->

SELECT e.emp_id, e.name, d.dept_name
  FROM employees e CROSS JOIN departments;

Non-ANSI Syntax ->

SELECT e.emp_id, e.name, d.dept_name
  FROM employees e, departments d;

Always include a join condition unless you specifically need a Cartesian join. Sometimes, you will experience slowness in your database if a cartesian join query is executed by any session.

                                ------------------------- ************* ---------------------------