By: Pankaj Yadav

Oracle Join is basically used when we want to see the data from two or more tables at a same time. The data of the table got merged with the other table having a same column.

Joining Tables using Oracle Syntax

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

Use a join to query data from more than one table.

• Write the join condition in the WHERE clause.
• Prefix the column name with the table name when the same column name appears in more than one table

We can declare fields as follows:

table1.column     --denotes the table and column from which data is retrieved
table1.column1 = table2.column2  --condition that joins (or relates) the tables together

We have various types of joins which are given below:

1. Equi join
2. Non-Equi join
3. Outer join
4. Self join
5. Cross join
6. Natural join

1. Equi join

It is the simplest join or inner. This type of join involves primary and foreign key complements.

2. Non Equi join

It is a join condition containing something other than equality operator.

3. Outer Join

We use outer join to also see rows that do not meet join condition. The outer join operator is plus sign (+).

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column (+) = table2.column;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2 .column(+);

The missing rows can be returned if an outer join operator is used in the join condition. The operator is a plus sign enclosed in parentheses (+), and it is placed on the “side” of the join that is deficient in Information. This operator has the effect of creating one or more null rows, to which one or more rows from the no deficient table can be joined.

table1.column = is the condition that joins (or relates) the tables together.
table2.column (+) is the outer join symbol, which can be placed on either side of the
WHERE clause condition, but not on both sides. (Place the outer
Join symbol following the name of the column in the table without

4. Self Join

This join can be performed when we have to join the single table to itself.

5. Cross join

This join returns the cross product of the records.

6. Natural join

The NATURAL JOIN clause is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in all matched columns. If the columns having the same names have different data types, an error is returned.