Joins Joins In order to maintain normalization in
- Slides: 10
Joins
Joins In order to maintain normalization in the database design it is necessary to break up data into separate tables. The data can then be re-associated through the use of a join.
What to join? • Key columns are the best since these were created for the purpose of existing as a reference. • Should have similar data • Should be the same datatype • nulls will not join since their value is not known.
Syntax • Usually best to put the join conditions first in the WHERE clause • Use of aliases greatly simplifies the statement. • Any logical operator can be used. • A self-join can be performed on the same table by qualifying it twice.
Self Join Which authors in Oakland have the same zip code? Select distinct au 1. au_fname , au 1. au_lname, au 1. zip from authors au 1, authors au 2 where au 1. city = “Oakland” and au 1. zip = au 2. zip and au 1. au_id != au 2. au_id
Outer Joins • Allow you to look at the results of a SELECT in the context of what rows did not qualify. Select fname, lname, pubname from author, pub where author. city *= pub. city • This will return all the rows of the first table in the statement and with a NULL value in the column of anything that did not qualify from the second. • Putting the * on the other side of the = will have the same effect on the second table.
Results _au_fname_ _au_lname_ pub_name Johnson White NULL Marjorie Green NULL Cheryl Carson Algodata Michael O’Leary NULL
How a Join is Processed • First the system obtains the Cartesian Product of all tables in join Cartesian Product - the matrix of all possible combinations that could satisfy the join • The select list is used to restrict the columns returned • The WHERE clause is then used to restrict the rows return that satisfy the query
Union clause • Way of combining data from multiple queries • Uses the column names from the first SELECT statement select fname First. Name, lname Last. Name, city City from authors where city=‘Oakland’ UNION select fname, lname, city from authors where city=‘New York’
Union cont. • Removes all duplicates by default. Using the ALL keyword prevents this. UNION ALL • Can use an ORDER BY only in the last SELECT statement which applies to all the output • Can eliminate the need to write numerous SELECT statements.
