Database Reference
In-Depth Information
Please Note:
1.
The first format describes the natural join. For this, the join
column(s) in each table must be identical. Moreover, you are
not allowed to qualify column names.
2.
The second format is useful where more than one column
in each table have the same name; you then need to specify
which columns should be used for the joining. If you have a
properly designed database with unique attribute-names in
each table, there will not be many occasions to use this format.
3.
The third format is the most widely used, because it is the most
flexible. You specify the join condition after the keyword
ON
.
4.
The fourth format is for taking Cartesian (cross) products; no
join condition is required.
5.
The fifth format is for outer joins; there are three types:
•
The left outer join is a join between two tables that returns
rows based on the join condition, and unmatched rows from
the table on the left.
•
The right outer join is a join between two tables that returns
rows based on the join condition, and unmatched rows from
the table on the right.
•
The full outer join is a join between two tables that returns
rows based on the join condition, and unmatched rows from
the table on the left, as well as the table on the right.
6.
When the keyword LEFT | RIGHT | FULL is used, the keyword
OUTER is implied and is therefore optional. Conversely, when
the keyword LEFT | RIGHT | FULL is omitted, the keyword
INNER is implied.
7.
Left outer join means that all rows from the table on the left
are kept; right outer join means that all rows from the table on
the right are kept; full outer join means that all rows are kept
from both tables (the one on the left and the one on the right).
8.
One significant advantage of the ANSI syntax over the
traditional is that you can separate join conditions from other
conditions that can still be specified in the
Where-Clause
.
Another advantage is a much easier achievement of a full
outer join (in the traditional approach, you have to take a
union of a left outer with a right outer).
