290 likes | 373 Views
Steven Emory Department of Computer Science California State University, Los Angeles. Using Relational Databases and SQL. Lecture 3: Joins Part I. Miscellany. Questions regarding Lab and Homework #1? Very happy with Lab and Homework #1 Updated Wiki page Moved Functions past midterm
E N D
Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 3: Joins Part I
Miscellany • Questions regarding Lab and Homework #1? • Very happy with Lab and Homework #1 • Updated Wiki page • Moved Functions past midterm • Moved Subqueries and Set Operations before midterm
The Problem • Question: Display each title name along with the name of the artist that made it. • Hmmm... let's trySELECT Title, ArtistID from Titles; • Hmmm... doesn't work • We want artist names too • Artist names, however, are in another table
The Solutions • Use a subquery • SELECT Title, (SELECT ArtistName FROM Artists WHERE ArtistID=T.ArtistID) AS 'ArtistName'FROM Titles T; • Use a join • SELECT Title, ArtistNameFROM Titles NATURAL JOIN Artists;
What is a Join? • A join is a subset of the Cartesian Product between two tables
What is a Cartesian Product? • The Cartesian Product of tables A and B is the set of all possible concatenated rows whose first component comes from A and whose second component comes from B
Cartesian Product Example • Given these two tables, what is the Cartesian Product? • SELECT ArtistName FROM Artists; • SELECT Title FROM Titles;
Cartesian Product Result • Cartesian Product • SELECT ArtistName, TitleFROM Artists, Titles; • 66 total rows • Is this the answer we want? No! There is too much information. • Cartesian Products can be quite large
Join Conditions • Since many records in a Cartesian Product are not meaningful, we can eliminate them using a join condition • In general, most of the time, we want to keep only matching records (i.e. only when two values of a common attribute between the two tables are equal)
Join Condition Example • For example: • -- Two tablesSELECT * FROM Artists;SELECT * FROM Titles; • -- Cartesian ProductSELECT *FROM Artists, Titles; • -- Join (Equi-join)SELECT *FROM Artists A, Titles TWHERE A.ArtistID=T.ArtistID;
Table Aliases • When joining tables with common attribute names, MySQL will get confused if you say: • SELECT *FROM Artists, TitlesWHERE ArtistID=ArtistID; • To solve this we can give each table an alias name (unlike column aliases, do not wrap you names in quotes): • SELECT *FROM Artists A, Titles TWHERE A.ArtistID=T.ArtistID;
Ways to Do a Cartesian Product • Several ways to do a Cartesian Product: • Cartesian Product (Form #1: Equi-Join Syntax)SELECT *FROM Titles, Artists; • Cartesian Product (Form #2: Cross Join Syntax)SELECT *FROM Titles CROSS JOIN Artists; • Cartesian Product (Form #3: Inner Join Syntax)SELECT *FROM Titles INNER JOIN Artists; • Cartesian Product (Form #4: Join On/Using Syntax)SELECT *FROM Titles JOIN Artists;
Cartesian Product Warnings • Do not do a Cartesian Product on more than two tables unless you really know what you’re doing! • -- Takes a long time!!!SELECT * FROM Artists, Titles, Tracks;
MySQL Join Types • Natural (this week) • Equi- (this week) • Inner (this week) • Outer (next week) • Left • Right • Cross (this week)
Natural Joins • A Natural Join joins two tables, automatically determining the join condition. • The join condition attributes are only displayed once when using SELECT * with a natural join.
Natural Join Syntax • Two tables: • SELECT attribute_listFROM table1 NATURAL JOIN table2; • Multiple tables: • SELECT attribute_listFROM table1NATURAL JOIN table2NATURAL JOIN table3...
Natural Join Examples • Two tables: • SELECT *FROM Artists NATURAL JOIN Titles; • Three tables: • SELECT *FROM ArtistsNATURAL JOIN TitlesNATURAL JOIN Tracks;
Equi-Joins • An equi-join is a Cartesian Product with a join condition specified in the WHERE clause • The join condition attributes will be displayed multiple times when using SELECT * with an equi-join. • You must use table aliases in the join condition to differentiate join attributes.
Equi-Join Syntax • Two tables: • SELECT attribute_listFROM table1 alias1, table2 alias2WHERE alias1.attribute = alias2.attribute; • Multiple tables: • SELECT attribute_listFROM table1 alias1, table2 alias2, table3 alias3, ...WHERE alias1.attribute = alias2.attributeAND alias2.attribute = alias3.attributeAND ...;
Equi-Join Examples • Two tables: • SELECT *FROM Artists A, Titles TWHERE A.ArtistID = T.ArtistID; • Three tables: • SELECT *FROM Artists A, Titles T, Tracks KWHERE A.ArtistID = T.ArtistID ANDT.TitleID = K.TitleID;
Inner Joins • Exact same thing as an equi-join, just using a different syntax: the JOIN ON syntax • The INNER keyword is optional • In the following examples I will use [INNER] to indicate that the INNER keyword is options
Inner Join Syntax • Two tables: • SELECT attribute_listFROM table1 alias1 [INNER] JOIN table2 alias2ON alias1.attribute = alias2.attribute; • Multiple tables: • SELECT attribute_listFROM table1 alias1[INNER] JOIN table2 alias2 ON alias1.attribute = alias2.attribute[INNER] JOIN table3 alias3 ON alias2.attribute = alias3.attribute...
Inner Join Examples • Two tables: • SELECT *FROM Artists A INNER JOIN Titles TON A.ArtistID = T.ArtistID; • Three tables: • SELECT *FROM Artists AINNER JOIN Titles T ON A.ArtistID = T.ArtistID INNER JOIN Tracks K ON T.TitleID = K.TitleID;
Join Using • Equivalent to a natural join, with the exception that the attributes to be used in the join condition are not determined automatically • The user must specify one or more column attributes for the join condition
Join Using Syntax • Two tables: • SELECT attribute_listFROM table1 JOIN table2USING(attribute_name); • Multiple tables: • SELECT attribute_listFROM table1JOIN table2 USING(attribute_name1)JOIN table3 USING(attribute_name2)...
Join Using Examples • Two tables: • SELECT *FROM Artists JOIN TitlesUSING(ArtistID); • Three tables: • SELECT *FROM ArtistsJOIN Titles USING(ArtistID) JOIN Tracks USING(TitleID);
Cross Joins • A cross join computes the Cartesian Product • A cross join is logically equivalent to: • SELECT * FROM table1, table2, table3, ...; • In MySQL, a cross join is equivalent to an inner join, using the same syntax, but uses the CROSS keyword instead of INNER keyword • In standard SQL2003, a cross join is not equivalent to an inner join. A cross join in standard SQL2003 cannot use the JOIN ON syntax (i.e. CROSS JOIN ON is illegal).
Cross Join Syntax • Two tables: • SELECT attribute_listFROM table1 CROSS JOIN table2; • Multiple tables: • SELECT attribute_listFROM table1CROSS JOIN table2CROSS JOIN table3...
Cross Join Examples • Two tables: • SELECT *FROM Artists CROSS JOIN Titles; • Three tables: • SELECT *FROM ArtistsCROSS JOIN TitlesCROSS JOIN Tracks;