1 / 28

Using Relational Databases and SQL

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

ahanu
Download Presentation

Using Relational Databases and SQL

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. Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 3: Joins Part I

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

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

  4. 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;

  5. What is a Join? • A join is a subset of the Cartesian Product between two tables

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

  7. Cartesian Product Example • Given these two tables, what is the Cartesian Product? • SELECT ArtistName FROM Artists; • SELECT Title FROM Titles;

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

  9. 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)‏

  10. 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;

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

  12. 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;

  13. 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;

  14. MySQL Join Types • Natural (this week)‏ • Equi- (this week)‏ • Inner (this week)‏ • Outer (next week)‏ • Left • Right • Cross (this week)‏

  15. Natural Joins • A Natural Join joins two or tables, automatically determining the join condition. • The join condition attributes are only displayed once when using SELECT * with a natural join.

  16. Natural Join Syntax • Two tables: • SELECT attribute_listFROM table1 NATURAL JOIN table2; • Multiple tables: • SELECT attribute_listFROM table1NATURAL JOIN table2NATURAL JOIN table3...

  17. Natural Join Examples‏ • Two tables: • SELECT *FROM Artists NATURAL JOIN Titles; • Three tables: • SELECT *FROM ArtistsNATURAL JOIN TitlesNATURAL JOIN Tracks;

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

  19. 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 ...;

  20. 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;

  21. Inner Joins • Exact same thing as an equi-join, just using a different syntax: the JOIN ON syntax • The INNER keyword is optional

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

  23. 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;

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

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

  26. Join Using Examples • Two tables: • SELECT *FROM Artists JOIN TitlesUSING(ArtistID); • Three tables: • SELECT *FROM ArtistsJOIN Titles USING(ArtistID) JOIN Tracks USING(TitleID);

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

  28. Cross Join Syntax • Two tables: • SELECT attribute_listFROM table1 CROSS JOIN table2; • Multiple tables: • SELECT attribute_listFROM table1CROSS JOIN table2CROSS JOIN table3...

More Related