JOINS:
  combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is.

 OR

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.


Types of joins:
1. Cartesian OR Cross join
2. Inner OR Equi OR natural join
3. Left Join OR Left Outer join
4. Right join OR Right Outer join
5. Full outer join
6. Self Join


Note: To write a join between the tables we need to have common cloumn among the table OR atleast matching records between the table. Otherwise we cannot write a join.
**This above rule is not applicable to Cartesian join
------------------------------------------

1. Cartesian join OR Cross join:
   It displays all the permutation & combination of data from all the columns which are mentioned in the query. It is not widely used as it create more data & thus consumes more memory.

Syntax:
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1, <table2> alias2;

Note:
Cartesian join is also k.a., Cross join bcoz we can get the same output using CROSS JOIN keyword.

Syntax:
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1 CROSS JOIN <table2> alias2;
--------------------------------------------

2. Inner OR Equi OR natural join
    This is the widely used join among all other joins. It displays only matching records from multiple tables. Hence its called Equi join.

Syntax: (Implicit join)
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1, <table2> alias2 where <condition>;


Inner OR Equi OR natural join using ON clause (ansi join OR explicit join)

Syntax:
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1 INNER JOIN/JOIN <table2> alias2 ON <condition>;
--------------------------------------------

3. Left Join OR Left Outer join
   It will display all the records form the left table and only matching records from the right table.

Syntax:
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1, <table2> alias2 where <condition>(+);


Left Join OR Left Outer join using ON clause(ansi OR explicit join)
Syntax:
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1 LEFT JOIN <table2> alias2 ON <condition>;
-------------------------------------------------
4. Right join OR Right Outer join:
     It will display all the records from right table & matching records from left table.

Syntax:
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1, <table2> alias2 where (+)<condition>;


Right join OR Right Outer join using ON clause
Syntax:
   select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1 RIGHT JOIN <table2> alias2 ON <condition>;
-------------------------------------------------
5. Full outer join:
   It is a UNION between Left join and Right join.

   1. All the records from left table & matching records from the right table.
   2. All the records from right table & matching records from the left table.

syntax:
  select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1 FULL OUTER JOIN <table2> alias2 ON <condition>;

----------  OR -------------

select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1 LEFT JOIN <table2> alias2 ON <condition>

UNION

select alias1.col1, alias1.col2, alias2.col1, alias2.col2 from
  <table1> alias1 RIGHT JOIN <table2> alias2 ON <condition>;


-----------------------------------------------
  
6. Self Join:
     Joining the records from the same table is k.a., Self join

Q: find out employee names and their manager names from emp table?
Ans: 
select e.ename "EMPLOYEE", m.ename "MANAGER"
from emp e, emp m where e.mgr=m.empno(+)

Q: Why self join is required?
Ans:
A self-join in SQL allows you to join a table to itself. This enables you to compare rows within the same table as though you were joining two different tables. Self-joins are useful for comparing values in a hierarchical table, finding duplicate values, or comparing rows with other rows in the same table.


