steven emory department of computer science california state university los angeles
Download
Skip this Video
Download Presentation
Using Relational Databases and SQL

Loading in 2 Seconds...

play fullscreen
1 / 29

Using Relational Databases and SQL - PowerPoint PPT Presentation


  • 65 Views
  • Uploaded on

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

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

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


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.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
Steven Emory

Department of Computer Science

California State University, Los Angeles

Using Relational Databases and SQL

Lecture 3:

Joins Part I

miscellany
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
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
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
What is a Join?
  • A join is a subset of the Cartesian Product between two tables
what is a cartesian product
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
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 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
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
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
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
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
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
MySQL Join Types
  • Natural (this week)‏
  • Equi- (this week)‏
  • Inner (this week)‏
  • Outer (next week)‏
    • Left
    • Right
  • Cross (this week)‏
natural joins
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
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
Natural Join Examples‏
  • Two tables:
    • SELECT *FROM Artists NATURAL JOIN Titles;
  • Three tables:
    • SELECT *FROM ArtistsNATURAL JOIN TitlesNATURAL JOIN Tracks;
equi joins
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
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
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
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
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
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
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
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
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
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
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
Cross Join Examples
  • Two tables:
    • SELECT *FROM Artists CROSS JOIN Titles;
  • Three tables:
    • SELECT *FROM ArtistsCROSS JOIN TitlesCROSS JOIN Tracks;
ad