|
1a. A Self Join query using the JOIN syntax with the join specified by ON |
| |
| |
|
1b. This is a query that uses the NATURAL JOIN syntax. Here Oracle does the join
between same named columns in the two tables. This syntax does not allow a user
to join the two tables on a single column. The Oracle recommends using this type of
join with caution or preferably avoiding the "Natural Join" syntax.
|
|
In the example below, if the user intends to join only on customer_id between the two tables,
if city were to exist in both of them, then the join will also include city, which will result
in different row counts.
|
| |
| |
|
1c. Same query as example 1a, using the INNER JOIN syntax |
| |
| |
| 1d. A query using the INNER JOIN with USING syntax |
| Note: There is no alias for the tables and columns. The column
names should be the identical in the two tables that are being joined
|
| |
| |
|
1e. A query using the LEFT OUTER JOIN syntax. In the example below customer_master is
the driving table, on the left hand side (query gets all data from this table).
|
| |
| |
| Below is the the standard Oracle sql syntax (similar to the query shown
on Page2 example query 2)
|
| |
| |
|
1f. A query using the RIGHT OUTER JOIN syntax. In the example below customer_master is
the driving table, on the right hand side (query gets all data from this table). Essentially the
two tables are flipped w.r.t query shown in 1e.
|
| |