Using Relational Databases and SQL

1 / 29

# Using Relational Databases and SQL - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Using Relational Databases and SQL' - ramona

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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;
• 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
• 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)...
• 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;