230 likes | 338 Views
Chapter 4 Joining Multiple Tables. 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. Cartesian Join.
E N D
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
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)
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
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
Equality Join: NATURAL JOIN Syntax: tablename NATURAL JOIN tablename
Equality Join: JOIN…USING Syntax: tablename JOIN tablename USING (columnname)
Equality Join: JOIN…ON Syntax: tablename JOIN tablename ON condition
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
Self-Joins • Used to link a table to itself • Requires use of column qualifier
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
Set Operators Used to combine the results of two or more SELECT statements
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