Joins can be categorized as following types,
1. Inner joins
2. Outer joins
a. Left join or Left outer join
b. Right join or Right outer join
c. Full join or Full outer join
3. Cross joins
4. Self joins
1. Inner joins:- Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table.
2. Outer joins:- Outer join can be a left, a right or full outer join.
a. Left join or Left outer join:- The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause
b. Right join or Right outer join:- A right outer join in the reverse of left and right tables. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
c. Full join or Full outer join:- A full outer join returns all rows in both the left and right tables.
3. Cross joins:- Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table.
Cross joins are also called Cartesian products.
4. Self joins:- A table can be joined to itself in a self-join.