Oracle Database 12c: SQL Workshop I: 07 Displaying Data from Multiple Tables Using Joins

Displaying Data from Multiple Tables Using Joins

Joining Tables Using SQL:1999 Syntax

SELECT table1.column, table2.column FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] | 
[JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| 
[CROSS JOIN table2];

Natural Joins

• The NATURAL JOIN clause is based on all the columns in the two tables that have the same name.
• It selects rows from the two tables that have equal values in all matched columns.
• If the columns having the same names have different data types, an error is returned.

SQL> desc t2;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T2NAME                         VARCHAR2(10)
 ID                         NUMBER(38)
 NO                         NUMBER

SQL> desc t3;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FIRST                          VARCHAR2(1)
 ID                         NUMBER(38)
 NO                         NUMBER

SQL> select * from t2;

T2NAME               ID     NO
-------------------- ---------- ----------
coat                  1      2
co_a                  3      4
ca                2      3
coat                  2      3
coat                  1      2
1                 1      1
co_at                 3      4

7 rows selected.

SQL> select * from t3;

FI     ID         NO
-- ---------- ----------
a       2          1
b       1          1
c       3          1
a       4          1
a       5          1


6 rows selected.

SQL> select id,no,t2name,first from t2 natural join t3;

    ID     NO T2NAME           FI
---------- ---------- -------------------- --
     1      1 1            b

Creating Joins with the USING Clause

• If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.
• Use the USING clause to match only one column when more than one column matches.
• The NATURAL JOIN and USING clauses are mutually exclusive.
• Do not qualify a column that is used in the USING clause.
• If the same column is used elsewhere in the SQL statement, do not alias it.

SQL> select * from t2 join t3 using(no);

    NO T2NAME           ID FI         ID
---------- -------------------- ---------- -- ----------
     1 1                 1 a           5
     1 1                 1 a           4
     1 1                 1 c           3
     1 1                 1 b           1
     1 1                 1 a           2

SQL> select t2.t2name,t3.first from t2 join t3 using(no);

T2NAME           FI
-------------------- --
1            a
1            a
1            c
1            b
1            a

SQL> select t2.t2name,t3.first from t2 join t3 using(no) where no=1;

T2NAME           FI
-------------------- --
1            a
1            b
1            c
1            a
1            a

Creating Joins with the ON Clause

• Use the ON clause to specify arbitrary conditions or specify
columns to join.
• The join condition is separated from other search conditions.
• The ON clause makes code easy to understand.

SQL> select t2name,t2.id,t3.id from t2 join t3 on t2.id=t3.no and t2.no=t3.id;

T2NAME               ID     ID
-------------------- ---------- ----------
coat                  1      2
coat                  1      2
1                 1      1

Self-join

Nonequijoins

OUTER join

• In SQL:1999, the join of two tables returning only matched
rows is called an INNER join.
• A join between two tables that returns the results of the INNER join as well as the unmatched rows from the left (or right) table is called a left (or right) OUTER join.
• A join between two tables that returns the results of an INNER join as well as the results of a left and right join is a full OUTER join.

SQL> select * from t4;

    ID     NO
---------- ----------
     2
            3
     1      2
     1      1

SQL> select * from t5;

    ID NAME
---------- ----------------------------------------
     2 a

SQL> select * from t4 join t5 on t4.id=t5.id;

    ID     NO         ID NAME
---------- ---------- ---------- ----------------------------------------
     2             2 a

SQL> select * from t4 left outer join t5 on t4.id=t5.id;

    ID     NO         ID NAME
---------- ---------- ---------- ----------------------------------------
     2             2 a
            3
     1      1
     1      2

SQL> select * from t4 right outer join t5 on t4.id=t5.id;

    ID     NO         ID NAME
---------- ---------- ---------- ----------------------------------------
     2             2 a

SQL> select * from t4 full outer join t5 on t4.id=t5.id;

    ID     NO         ID NAME
---------- ---------- ---------- ----------------------------------------
     2             2 a
            3
     1      2
     1      1

Cartesian Products

• A Cartesian product is formed when:
– A join condition is omitted
– A join condition is invalid
– All rows in the first table are joined to all rows in the second table
• Always include a valid join condition if you want to avoid a Cartesian product.
• The CROSS JOIN clause produces the cross-product of two tables.
• This is also called a Cartesian product between the two tables.

Oracle Join Syntax

SELECT  table1.column, table2.column
FROM    table1, table2
WHERE   table1.column1 = table2.column2;

Left Outer

SELECT  table1.column, table2.column
FROM    table1, table2
WHERE   table1.column = table2.column(+);

Right Outer

SELECT  table1.column, table2.column
FROM    table1, table2
WHERE   table1.column(+) = table2.column;