1 / 23

Understanding Joins in Relational Databases: Techniques and Examples

This chapter discusses the concept of joins in relational databases, explaining their purpose to link tables and reconstruct data. It covers various types of joins, including Cartesian joins, equality joins (using NATURAL JOIN, JOIN...USING, and JOIN...ON), non-equality joins, self-joins, and outer joins. The chapter includes syntax examples and detailed explanations of how to implement each join type. Additionally, it addresses the process of joining three or more tables, highlighting that it requires one less join than the total number of tables involved.

tiger-wright
Télécharger la présentation

Understanding Joins in Relational Databases: Techniques and Examples

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related