Different Type of SQL JOINs

1. JOIN or INNER JOIN:-

In this kind of a JOIN, we get all records that match the condition in both the joining tables, and records in both the tables that do not match are not reported.

In other words, ONLY the matching entries in BOTH the tables SHOULD be listed.

Note:- JOIN without any other JOIN keywords (like INNER, OUTER, LEFT, etc) is an INNER JOIN.

2. OUTER JOIN:-

Retrieves either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).

There are three kinds of Outer Join :-

LEFT JOIN or LEFT OUTER JOIN:-

Return all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT JOIN or RIGHT OUTER JOIN:-

This JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL JOIN or FULL OUTER JOIN:-

This JOIN combines result of both LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns all Matched and Non-matching rows. It returns NULL value when there is no match.

3. CROSS JOIN:-

It is the Cartesian product of the two tables involved. It produces a result set which is number of rows in first table multiplied by number of rows in second table.

If where clause is used with CROSS JOIN it functions like a INNER JOIN.

4. SELF JOIN:-

It is a join in which a table is joined with itself(unary relationship). It can be viewed as join of two copies of a same table. It can be a INNER or OUTER JOIN.

5. NATURAL JOIN:-

It is based on the below conditions :

1. the JOIN is made on all the columns with the same name for equality.

2. Removes duplicate columns from the result.

3. Don't use ON clause in a natural join.

4. Columns must be of same data type.

This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.