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;