Oracle SQL Joins

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever two or more tables are listed in a SQL statement.

There are 4 different types of SQL joins:

1. SQL INNER JOIN (sometimes called simple join)
2. SQL LEFT OUTER JOIN (sometimes called LEFT JOIN)
3. SQL RIGHT OUTER JOIN (sometimes called RIGHT JOIN)
4. SQL FULL OUTER JOIN (sometimes called FULL JOIN)

SQL INNER JOIN (simple join)

Chances are, you've already written a SQL statement that uses an SQL INNER JOIN. It is the most common type of SQL join. SQL INNER JOINS return all rows from multiple tables where the join condition is met.


Syntax

The syntax for the INNER JOIN in SQL is:

SELECT columns FROM table1 

INNER JOIN table2 

ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the SQL INNER JOIN returns the shaded area:

The SQL INNER JOIN would return the records where table1 and table2 intersect.

SQL LEFT OUTER JOIN

Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax

The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns FROM table1 

LEFT [OUTER] JOIN table2 

ON table1.column = table2.column;


In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.

Visual Illustration

In this visual diagram, the SQL LEFT OUTER JOIN returns the shaded area:

The SQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

SQL RIGHT OUTER JOIN

Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax

The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns FROM table1 

RIGHT [OUTER] JOIN table2 

ON table1.column = table2.column;


In some databases, the OUTER keyword is omitted and written simply as RIGHT JOIN.
Visual Illustration

In this visual diagram, the SQL RIGHT OUTER JOIN returns the shaded area:

The SQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

SQL FULL OUTER JOIN

Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with NULL values in place where the join condition is not met.
Syntax

The syntax for the SQL FULL OUTER JOIN is:

SELECT columns FROM table1 

FULL [OUTER] JOIN table2 

ON table1.column = table2.column;


In some databases, the OUTER keyword is omitted and written simply as FULL JOIN.
Visual Illustration

In this visual diagram, the SQL FULL OUTER JOIN returns the shaded area:

The SQL FULL OUTER JOIN would return the all records from both table1 and table2.

Explicit vs. Implicit SQL Joins

The explicit join is easier to read and the implicit syntax is difficult to understand and more prone to errors. Moreover implicit syntax is now a day’s outdated.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation":

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

SELECT *
  FROM employee INNER JOIN department 
    ON employee.DepartmentID = department.DepartmentID;

The "implicit join notation" simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).

The following example shows a query which is equivalent to the one from the previous example, but this time written using the implicit join notation:

SELECT *  
FROM   employee, department 
WHERE  employee.DepartmentID = department.DepartmentID;