Chapter 4 Joining Multiple Tables

1. Chapter 4Joining Multiple Tables Oracle9i: SQL

2. Chapter Objectives • Create a Cartesian join • Create an equality join using the WHERE clause • Create an equality join using the JOIN keyword • Create a non-equality join using the WHERE clause • Create a non-equality join using the JOIN…ON approach Oracle9i: SQL

3. Chapter Objectives • Create a self-join • Distinguish an inner join from an outer join • Create an outer join using the WHERE clause • Create an outer join using the OUTER keyword • Use set operators to combine the results of multiple queries • Join three or more tables Oracle9i: SQL

4. Purpose of Joins • Joins are used to link tables and reconstruct data in a relational database • Joins can be created through: • Conditions in a WHERE clause • Use of JOIN keywords in FROM clause Oracle9i: SQL

5. Cartesian Join • Created by omitting joining condition in the WHERE clause or through CROSS JOIN keywords in the FROM clause • Results in every possible row combination (m * n) Oracle9i: SQL

6. Cartesian Join Example:Omitted Condition Oracle9i: SQL

7. Cartesian Join Example:CROSS JOIN Keywords Oracle9i: SQL

8. Equality Join • Links rows through equivalent data that exists in both tables • Created by: • Creating equivalency condition in the WHERE clause • Using NATURAL JOIN, JOIN…USING, or JOIN…ON keywords in the FROM clause Oracle9i: SQL

9. Equality Join: WHERE Clause Example Oracle9i: SQL

10. Equality Join: NATURAL JOIN Syntax: tablename NATURAL JOIN tablename Oracle9i: SQL

11. Equality Join: JOIN…USING Syntax: tablename JOIN tablename USING (columnname) Oracle9i: SQL

12. Equality Join: JOIN…ON Syntax: tablename JOIN tablename ON condition Oracle9i: SQL

13. JOIN Keyword Overview • Use NATURAL JOIN when tables have one column in common • Use JOIN…USING when tables have more than one column in common • Use JOIN…ON when a condition is needed to specify a relationship other than equivalency • Using JOIN keyword frees the WHERE clause for exclusive use in restricting rows Oracle9i: SQL

14. Non-Equality Joins • In WHERE clause, use any comparison operator other than equal sign • In FROM clause, use JOIN…ON keywords with non-equivalent condition Oracle9i: SQL

15. Non-Equality Join: WHERE Clause Example Oracle9i: SQL

16. Non-Equality Join: JOIN…ON Example Oracle9i: SQL

17. Self-Joins • Used to link a table to itself • Requires use of column qualifier Oracle9i: SQL

18. Self-Join: WHERE Clause Example Oracle9i: SQL

19. Self-Join: JOIN…ON Example Oracle9i: SQL

20. Outer Joins • Use to include rows that do not have a match in the other table • In WHERE clause, include outer join operator (+) next to table with missing rows to add NULL rows • In FROM clause, use FULL, LEFT, or RIGHT with OUTER JOIN keywords Oracle9i: SQL

21. Outer Join: WHERE Clause Example Oracle9i: SQL

22. Outer Join: OUTER JOIN Keyword Example Oracle9i: SQL

23. Set Operators Used to combine the results of two or more SELECT statements Oracle9i: SQL

24. Set Operator Example Oracle9i: SQL

25. Joining Three or More Tables • Same procedure as joining two tables • Will always results in one less join than the number of tables being joined Oracle9i: SQL

26. Joining Three or More Tables: Example Oracle9i: SQL