Oracle

Join is a query that is used to combine rows from two or more tables, views, or materialized views. It retrieves data from multiple tables and creates a new table.

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.

Syntax:

SELECT columns  
FROM table1   
INNER JOIN table2  
ON table1.column = table2.column;   

Example:

SELECT *  
FROM students   
INNER JOIN admission  
ON students.student_id = admission.student_id;  

Above Query join both table and print all rows which statisfy given codintion.

OUTER JOIN

 It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 9i ANSI/ISO 1999 standard.

Left Outer Join

Left Outer Join returns all rows from the left (first) table specified in the ON condition and only those rows from the right (second) table where the join condition is met.

Syntax:

SELECT columns  
FROM table1  
LEFT [OUTER] JOIN table2  
ON table1.column = table2.column;   

Example:

SELECT *  
FROM department   
LEFT OUTER JOIN admission  
ON department.dept_id = admission.dept_id;  

Right Outer Join

The Right Outer Join returns all rows from the right-hand table specified in the ON condition and only those rows from the other table where the join condition is met.

Syntax

SELECT columns  
FROM table1  
RIGHT [OUTER] JOIN table2  
ON table1.column = table2.column;   

Example:

SELECT *  
FROM department   
RIGHT OUTER JOIN admission  
ON department.dept_id = admission.dept_id;  

Full Outer Join

The Full Outer Join returns all rows from the left hand table and right hand table. It places NULL where the join condition is not met.

Syntax

SELECT columns  
FROM table1  
FULL [OUTER] JOIN table2  
ON table1.column = table2.column;   

Example:

SELECT *  
FROM department   
FULL OUTER JOIN admission  
ON department.dept_id = admission.dept_id;  

EQUI JOIN

Oracle Equijoin returns the matching column values of the associated tables. It uses a comparison operator in the WHERE clause to refer equality.

Syntax

SELECT column_list   
FROM table1, table2....  
WHERE table1.column_name =  
table2.column_name;  

Alternative:

Equijoin also can be performed by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.

Syntax

SELECT *  
FROM table1   
JOIN table2  
[ON (join_condition)]   

Example:

SELECT *  
FROM department   
JOIN admission  
ON department.dept_id = admission.dept_id;  

SELECT *  
FROM department, admission 
WHERE department.dept_id = admission.dept_id;  

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

SELECT a.column_name, b.column_name...   
FROM table1 a, table1 b   
WHERE a.common_filed = b.common_field;   

Example

SELECT *  
FROM students a,students b
WHERE a.dob = b.dob;

Cross Join (Cartesian Products)

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

SELECT *   
FROM table1   
CROSS JOIN table2;  

Or

SELECT * FROM table1, table2  

Both the above syntax are same and used for Cartesian product. They provide similar result after execution.

Example:

SELECT *  
FROM department,admission;

It will return catesian product.