Chapter 4 Joining Multiple Tables

1. Chapter 4Joining Multiple Tables

2. 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

3. 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)

4. Cartesian Join Example:Omitted Condition

5. 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

6. 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

7. Equality Join: WHERE Clause Example

8. Equality Join: NATURAL JOIN Syntax: tablename NATURAL JOIN tablename

9. Equality Join: JOIN…USING Syntax: tablename JOIN tablename USING (columnname)

10. Equality Join: JOIN…ON Syntax: tablename JOIN tablename ON condition

11. 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

12. Non-Equality Join: WHERE Clause Example

13. Non-Equality Join: JOIN…ON Example

14. Self-Joins • Used to link a table to itself • Requires use of column qualifier

15. Self-Join: WHERE Clause Example

16. Self-Join: JOIN…ON Example

17. 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

18. Outer Join: WHERE Clause Example

19. Outer Join: OUTER JOIN Keyword Example

20. Set Operators Used to combine the results of two or more SELECT statements

21. Set Operator Example

22. 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

23. Joining Three or More Tables: Example