Steven emory department of computer science california state university los angeles
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

Using Relational Databases and SQL PowerPoint PPT Presentation


  • 39 Views
  • Uploaded on
  • Presentation posted in: General

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

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


  • Login