What are different Types of Join?

Posted by

To view Verified answers click on the button below.

1. Cross Join A cross join that does not have a WHERE clause produces the Cartesian
product of the tables involved in the join. The size of a Cartesian product result set is
the number of rows in the first table multiplied by the number of rows in the second
table. The common example is when company wants to combine each product with
a pricing table to analyze each product at each price.

2. Inner Join A join that displays only the rows that have a match in both joined tables
is known as inner Join. This is the default type of join in the Query and View
Designer.

3. Outer Join A join that includes rows even if they do not have related rows in the
joined table is an Outer Join. You can create three different outer join to specify the
unmatched rows to be included:

1. Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left”
table, which appears leftmost in the JOIN clause are included. Unmatched
rows in the right table do not appear.

2. Right Outer Join: In Right Outer Join all rows in the second-named table i.e.
“right” table, which appears rightmost in the JOIN clause are included.
Unmatched rows in the left table are not included.

3. Full Outer Join: In Full Outer Join all rows in all joined tables are included,
whether they are matched or not.

4. Self Join This is a particular case when one table joins to itself, with one or two
aliases to avoid confusion. A self join can be of any type, as long as the joined tables
are the same. A self join is rather unique in that it involves a relationship with only
one table.

The common example is when company has a hierarchal reporting
structure whereby one member of staff reports to another. Self Join can be Outer
Join or Inner Join.