Joins in oracle

Joins are used to combine data of two or more tables based on some condition.

To study these functions we use “employee” and “department” table as example.

Selecting employee table



Selecting department table



They are divided as below:
1. Inner Join:- Inner Join is the simplest and most common type of join. It is also known as simple join. It returns all rows from multiple tables where the join condition is met. If condition is matched for equality then it is referred as EQUI JOIN Otherwise referred as NON EQUI JOIN.

Syntax for EQUI JOIN

SELECT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Commoncolumn = Table2.Commoncolumn;

Syntax for NON-EQUI JOIN

SELECT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Commoncolumn OP Table2.Columncolumn;

In above syntax OP refers to any relational operator other than equal to.

Use of EQUI JOIN



Use of NON-EQUI JOIN



Use of NON-EQUI JOIN



2. Outer Join:- An outer join is similar to equijoin but it gets also the non-matched rows from the tables.

Types of Outer Joins are as follow:
A. Left Outer Join
B. Right Outer Join
C. Full Outer Join

Left Outer Join

Syntax for Left Outer Join

SELECT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Commoncolumn = Table2.Commoncolumn (+);

In above syntax when we add (+) Symbol on right side of WHERE condition then Join referred as left outer join.

Use of LEFT OUTER JOIN



Right Outer Join

Syntax for Right Outer Join

SELECT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Commoncolumn (+) = Table2.Commoncolumn;

In above syntax when we add (+) Symbol on left side of WHERE condition then Join referred as right outer join.

Use of RIGHT OUTER JOIN



Full Outer Join

Syntax for Full Outer Join

SELECT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Commoncolumn = Table2.Commoncolumn (+)
UNION
SELECT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Commoncolumn (+) = Table2.Commoncolumn;

In above syntax we combine query of left outer join and right outer join using UNION operator to get resultant output of Full Outer Join.

Use of FULL OUTER JOIN



3. Self Join:- Self Join is a specific type of Join. In Self Join, a table is joined with itself (Unary relationship). A self-join simply specifies that each rows of a table is combined with itself and every other row of the table.

Syntax for SELF JOIN

SELECT alias1.Column1, alias2.Column2
FROM Table alias1, Table alias 2
WHERE Table.Commoncolumn = Table.Commoncolumn;

In above syntax it is compulsory to specify alias (alternative name) for table.

Use of SELF JOIN



4. Cross Join:- The CROSS JOIN specifies that all rows from first table join with all of the rows of second table. If there are "x" rows in table1 and "y" rows in table2 then the cross join result set have x*y rows. It normally happens when no matching join columns are specified.
In simple words you can say that if two tables in a join query have no join condition, then the Oracle returns their Cartesian product.

Syntax for CROSS JOIN

SELECT Table1.Column1, Table2.Column1
FROM Table1, Table2;

Use of CROSS JOIN



I hope this article will help you