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.
Sign up here with your email
Conversion Conversion Emoticon Emoticon