What are the types of Join?

NON-EQUI JOIN:
This join does not make use of the following comparison operators:- >, <, >=, <=. This is to say, it joins two or more tables based on a specified column value not equaling a specified column value in another table.

Example:

SELECT STUDENT_TBL.STD_ID, STUDENT_MARKS_TBL.DATE_EXAM
FROM STUDENT_TBL, STUDENT_MARKS_TBL
WHERE STUDENT_TBL.STD_ID!= STUDENT_MARKS_TBL.EMP_ID;

CARTESIAN JOIN:
Cartesian join does not join two tables based on a condition. In this case combined rows of both tables are returned.

Example:

select employee_id, manager_id
from employee cross join manager;

SELF JOIN:
This joins is a join to the table itself. As per the condition, the results are returned. Since the same name of the table cant be used twice, aliases of the tables are created.

Example:

select b.std_last_name STUDENT, a.std_last_name PROFESSOR
from std a right outer join std b on (a.std_key = b.professor);

OUTER JOIN:
In this type of join results are returned only if the join condition is met- i.e. the joined fields are equal.

Example:

Select employee.employee_id, employee.employee_name, manager.manager_id
from employee, manager
where employee.employee_id = manager.employee_id(+);