Oracle join multi-table join-at least three tables

Oracle join usage

Join)

T1 {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN T2 ON boolean_experssion

{[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN T3 ON boolean_experssion

.....

T1 {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN T2 USING (join colunn list)

T1 NATURAL {[INNER] | {LEFT | RIGHT | FULL} [OUTER]} JOIN T2

INNER and OUTER are optional for all join types. INNER is the default; LEFT, RIGHT, and FULL implicit OUTER join.

The join condition is declared in the ON or USING clause, or the keyword NATURAL is implicitly declared. The join condition determines that the rows from the two source tables are "matched ".

The ON clause is the most common type of join condition: it receives a Boolean expression that is the same as that used in the WHERE clause. if the results of the operations ON the ON expression are true for two rows from T1 and T2 respectively, they are even matched rows.

USING is an abbreviated concept: it receives a list of field names separated by commas (,). These fields must be common to the connection table and form a connection condition, indicating that these field pairs must be the same. finally, the join using output outputs a field for each pair of equal input fields, followed by all other fields from each table. therefore, USING (a, B, c) is equivalent to ON (t1.a = t2.a AND t1. B = t2. B AND t1.c = t2.c). However, if ON is used,, b, and c fields each have two, and when USING is used, each field has only one.

Finally, NATURAL is the abbreviation form of USING: it forms a USING list, which consists of the field names that appear in both tables. like USINqG, these fields appear only once in the output table.

The possible types of conditional JOIN are:

INNER JOIN
For each row of R1 in T1, the generated connection table has a row corresponding to each row in T2 that meets the connection conditions of R1.

LEFT OUTER JOIN
First, execute an internal connection. then, return a connection row for the rows in T1 and any row in T2 that do not meet the connection condition, and fill in the column corresponding to T2 in the connection row with a null value. therefore, the generated connection table unconditionally contains at least one copy of each row from T1.

RIGHT OUTER JOIN
First, execute an internal connection. then, return a connection row for the rows in T2 that do not meet the connection conditions for any row in T1, and fill in the columns corresponding to T1 in the connection row with null values. therefore, the generated connection table unconditionally contains each row from T2.

FULL OUTER JOIN
First, execute an internal connection. then, return a connection row for the rows in T1 and any row in T2 that do not meet the connection condition, and fill in the column corresponding to T2 in the connection row with a null value. similarly, a connection row is returned for rows in T2 that do not meet the connection conditions for any row in T1. The column corresponding to T1 in this row is filled with null values.

If T1 and T2 have one or both tables that can be joined, all types of connections can be chained together or nested together. you can use circular arc around the JOIN clause to control the JOIN sequence. If there is no circular arc, the JOIN clause is nested from left to right.