# Using Relational Databases and SQL - PowerPoint PPT Presentation

1 / 29

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.

Using Relational Databases and SQL

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

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;