CS 122 Using Relational Databases and SQL 4

CS 122 Using Relational Databases and SQL 4. Subqueries and joins Daniel Firpo Slides prepared by Randy Moss Department of Computer Science California State University, Los Angeles

Outline r Subqueries m Subqueries in the WHERE clause m Subqueries with IN, ALL & ANY r Joining tables m Learn how SQL joins tables m Join tables using an Equi Join m Join tables using an Inner Join 4. Subqueries and joins CS 1222_W 2018 4 -2

Sub-Queries r A query within a query r Sub-query placed in parentheses r Sub-queries can be placed in m WHERE clause m HAVING clause m SELECT clause m FROM clause 4. Subqueries and joins CS 1222_W 2018 4 -3

Sub-Queries examples (1) r List the name of the oldest member m Find the earliest birthday (1955 -11 -01) Select min(birthday) From Members m Find the member whose birthday is ‘ 1955 -11 -01’ Select firstname, lastname From Members Where birthday = ‘ 1955 -11 -01’ 4. Subqueries and joins CS 1222_W 2018 4 -4

Sub-Queries examples (1) r Combine the two queries SELECT firstname, lastname FROM Members WHERE birthday = (SELECT Min(birthday) FROM Members ) 4. Subqueries and joins CS 1222_W 2018 4 -5

Sub-Query Example (1) Outer query looks for records with Birthday matching Sub-Query value Select Lastname, Firstname From Members Where Birthday = (Select Min(Birthday) From Members) Sub-Query returns ----- 1955 -11 -01 Query returns Lastname Firstname ------------------------- Wong Tony

Sub-Queries examples (2) r List all track titles and lengths of all tracks whose length is longer than the average of all track lengths m Find the average track lenghths (276. 08) SELECT Avg(lengthseconds) FROM Tracks m Find the all track titles and lengths of all tracks whose length is greater than 276. 08 SELECT tracktitle, lengthseconds FROM Tracks WHERE lengthseconds > 276. 08 4. Subqueries and joins CS 1222_W 2018 4 -7

Sub-Queries examples (2) r Combine the two queries SELECT tracktitle, lengthseconds FROM Tracks WHERE lengthseconds > ( SELECT Avg(lengthseconds) FROM Tracks ) 4. Subqueries and joins CS 1222_W 2018 4 -8

Sub-Queries using IN r Report the name of all artists who have recorded a title SELECT artist. Name FROM Artists WHERE artist. ID IN (SELECT artist. ID FROM Titles) 4. Subqueries and joins CS 1222_W 2018 4 -9

ALL & ANY r Used with Sub-Queries r Can be used with greater than and less than tests r If using ANY, then comparison will be true if it satisfies any value produced by the sub-query o SOME is functionally equivalent to ANY r If using ALL, then comparison will be true if it satisfies all values produced by the sub-query. 4. Subqueries and joins CS 1222_W 2018 4 -10

ALL example r List the name, region, and birthday of every member who is older than all of the members in Georgia Select Birthday From Members Where Region='GA' Birthday ------ 1963 -08 -04 1959 -06 -22 1964 -03 -15 Select Lastname, Firstname, Region, Birthday From Members Where Birthday < ALL(Select Birthday From Members Where Region='GA') Lastname Firstname Region Birthday ---------- ------------ Ranier Brian ONT 1957 -10 -19 Kale Caroline VA 1956 -05 -30 Wong Tony ONT 1955 -11 -01 Cleaver Vic VT 1957 -02 -10 4 -11 4. Subqueries and joins CS 1222_W 2018

ANY example r List the name, region, and birthday of every member who is older than any of the members in Georgia SELECT lastname, firstname, region, birthday FROM Members WHERE birthday < ANY (SELECT birthday FROM Members WHERE region = 'GA' ) 4. Subqueries and joins CS 1222_W 2018 4 -12

Exercises r Report the firstname, lastname and birthday of the members who has the same birthday as someone in Georgia r Is the following query correct? SELECT lastname, firstname, region, birthday FROM Members WHERE birthday = ALL (SELECT birthday FROM Members WHERE region = 'GA' ) No! We cannot compare a single value with a LIST of values 4. Subqueries and joins CS 1222_W 2018 4 -13

Exercises (cont. ) r How about this query? SELECT lastname, firstname, region, birthday FROM Members WHERE birthday = (SELECT birthday FROM Members WHERE region = 'GA') No! We cannot compare a single value with a LIST of values We cannot compre 3 with (3, 4, 5, 6) 4. Subqueries and joins CS 1222_W 2018 4 -14

Exercises (cont. ) r This one? SELECT lastname, firstname, region, birthday FROM Members WHERE birthday = ANY (SELECT birthday FROM Members WHERE region = 'GA') Correct! =ANY is equivalent to IN 4. Subqueries and joins CS 1222_W 2018 4 -15

Joins r When SQL joins two tables you can select any column from either table as if they were both in same table m Joins Table 1 Table 2 tables "side-by-side" r Tables must share a common field m Primary Key – Foreign Key 4. Subqueries and joins CS 1222_W 2018 4 -16

How SQL Joins Tables Step 1: Create Cartesian Product All possible combinations of data in both tables

How SQL Joins Tables Step 2: Eliminate Non-Matching Records

Join (another example) r When information is spread over multiple tables m Find the projects which John is working on Projects Employees eid ename pid 01 John 01 T 4 02 Susan 01 X 3 02 S 2 4. Subqueries and joins CS 1222_W 2018 4 -19

Results eid ename eid pid 01 John 01 T 4 01 John 01 X 3 02 Susan 02 S 2 pid T 4 X 3 4. Subqueries and joins CS 1222_W 2018 4 -20

The kinds of join r Cross join r Equi Join r Inner join r Outer join m Left join m Right join r Self join 4. Subqueries and joins CS 1222_W 2018 4 -21

Step 1: Cartesian product Join every row in the first table to every row in the second table in every possible combination Employees eid Projects ename eid pid 01 John 01 T 4 01 John 01 X 3 01 John 02 S 2 02 Susan 01 T 4 02 Susan 01 X 3 02 S 2 02 4. Subqueries and joins CS 1222_W 2018 4 -22

Step 2: Throw out non-matching records eid ename eid pid 01 John 01 T 4 01 John 01 X 3 01 John 02 S 2 02 Susan 01 T 4 02 02 Susan 01 X 3 02 S 2 Non-matching records 4. Subqueries and joins CS 1222_W 2018 4 -23

Cross Join r Builds a Cartesian product r Uses no ON keyword

Tips to join tables 1. Find which tables to join: TA, TB 2. Find the common attributes of those tables: field 3. Specify the relationship in the where clause: TA. field=TB. field 4. Put the two tables’ fields in the select clause as needed 4. Subqueries and joins CS 1222_W 2018 4 -26

Equi Join example 1 r List the CD title and the title of all tracks recorded in Studio 2 Which tables to join SELECT title, tracktitle FROM Titles, Tracks WHERE Titles. title. ID = Tracks. title. ID Which fields to match AND studio. ID=2 (throw non-matching fields 4. Subqueries and joins CS 1222_W 2018 4 -27

Equi Join example 2 List the names of members from Georgia (GA) and their salespeople. Select Members. Lastname, Members. First. Name, Salespeople. Lastname, Salespeople. Firstname From Members, Salespeople Where Members. Sales. ID= Sales. People. Sales. ID And Region='GA' 4. Subqueries and joins CS 1222_W 2018 4 -28

Table Aliases r r Saves typing Table name cannot be used in rest of query Case-sensitive in some systems Example m SELECT FROM WHERE List the names of members from Georgia (GA) and their salespeople M. Lastname, M. First. Name, S. Lastname, S. Firstname Members M, Salespeople S M. Sales. ID= S. Sales. ID And Region='GA' 4. Subqueries and joins CS 1222_W 2018 4 -29

Equi join example 3 r List the names of all artists who have recorded a title and the number of titles they have SELECT artistname, COUNT(Titles. artist. ID) AS Num. Titles FROM Artists, Titles WHERE Artists. artist. ID=Titles. artist. ID GROUP BY artistname 4. Subqueries and joins CS 1222_W 2018 4 -30

Equi Join example 4 List the names of members in The Bullets. Select Members. Lastname, Members. First. Name From Members, XRef. Artists. Members, Artists Where Members. Member. ID = XRef. Artists. Member. ID And Artists. Artist. ID = XRef. Artists. Members. Artist. ID And Artistname = 'The Bullets' 4. Subqueries and joins CS 1222_W 2018 4 -31

Equi join summary r The tables to join are in the FROM clause m Only join just enough tables r The relationship specifications are in the WHERE clause r WHERE clause will get messy if more tables are added 4. Subqueries and joins CS 1222_W 2018 4 -32

Inner Join r Same result as Equi Join r Different Syntax m Tables are listed with the keyword INNER JOIN m Relationship specifications moved from WHERE clause to ON clause • Free WHERE clause for traditional conditions 4. Subqueries and joins CS 1222_W 2018 4 -33

Inner Join example 1 r List the CD title and the title of all tracks recorded in Studio 2 Tables to join SELECT title, tracktitle FROM Titles INNER JOIN Tracks ON Titles. titleid = Tracks. titleid WHERE studio. ID=2 Relationship specification 4. Subqueries and joins CS 1222_W 2018 4 -34

Inner Join example 2 List the names of members from Georgia (GA) and their salespeople. Select From Where Members. Lastname, Members. First. Name, Salespeople. Lastname, Salespeople. Firstname Members Inner Join Salespeople On Members. Sales. ID= Salespeople. Sales. ID Region='GA' 4. Subqueries and joins CS 1222_W 2018 4 -35

Inner Join example 3 r List the names of all artists who have recorded a title and the number of titles they have SELECT artistname, COUNT(Titles. artist. ID) AS Num. Titles FROM Artists INNER JOIN Titles ON Artists. artist. ID=Titles. artist. ID GROUP BY artist. Name 4. Subqueries and joins CS 1222_W 2018 4 -36

Inner Join example 4 List the names of members in The Bullets. Select From Where Members. Lastname, Members. First. Name Members Inner Join Xref. Artists. Members On Members. Member. ID = XRef. Artists. Member. ID Inner Join Artists On Artists. Artist. ID = XRef. Artists. Members. Artist. ID Artistname = 'The Bullets' Note: some systems require joins be grouped with parentheses. List the names of members in The Bullets. Select From Where Members. Lastname, Members. First. Name (Members Inner Join Xref. Artists. Members On Members. Member. ID = XRef. Artists. Member. ID) Inner Join Artists On Artists. Artist. ID = XRef. Artists. Members. Artist. ID Artistname = 'The Bullets' 4. Subqueries and joins CS 1222_W 2018 4 -37

Inner Join example 4 with Aliases List the names of members in The Bullets. Select M. Lastname, M. First. Name From (Members M Inner Join Xref. Artists. Members X On M. Member. ID = X. Member. ID) Inner Join Artists A On A. Artist. ID = X. Artist. ID Where Artistname = 'The Bullets' 4. Subqueries and joins CS 1222_W 2018 4 -38

Outer Join r Left join m Report all of the records of the first (left) of two tables plus matching records in the second (right) table. r Right join m Report all of the records of the second (right) of two tables plus matching records in the first (left) table r The order of tables MATTERS 5. More joins CS 1222_W 2018 5 -39

Inner Joins vs. Outer Joins List the names of all artists who have recorded a title and the titles they have SELECT Artistname, Title FROM Artists A Inner Join Titles T ON A. Artist. ID=T. Artist. ID Artistname Title ---------------- The Neurotics Meet the Neurotics Confused Smell the Glove The Bullets Time Flies The Neurotics Neurotic Sequel Sonata Sonatas Louis Holiday Louis at the Keys 5. More joins CS 1222_W 2018 5 -40

Inner Joins vs. Outer Joins List the names of all artists and the titles(if any) that they have recorded SELECT Artistname, Title From Artists A Left Join Titles T ON A. Artist. ID=T. Artist. ID Artistname Title ----------------- The Neurotics Meet the Neurotics The Neurotics Neurotic Sequel Louis Holiday Louis at the Keys Word NULL Sonata Sonatas The Bullets Time Flies Jose Mac. Arthur NULL Confused Smell the Glove The Kicks NULL Today NULL 21 West Elm NULL Highlander NULL 5. More joins CS 1222_W 2018 5 -41

Another outer Join example r List every genre from the Genre table and a count of the number of recorded tracks in that genre, if any SELECT G. Genre, Count(Tracknum) As Num. Tracks FROM (Genre G LEFT JOIN Titles TI ON G. Genre = TI. Genre) LEFT JOIN Tracks TR ON TI. Title. ID = TR. Title. ID GROUP BY G. Genre 5. More joins CS 1222_W 2018 5 -42

Using Outer Join to Duplicate NOT IN Functionality Report artists who don’t have titles SELECT FROM ON A. Artist. ID, Artistname, T. Artist. ID Artists A Left Join Titles T A. Artist. ID=T. Artist. ID Artistname Artist. ID -------------------- 1 The Neurotics 1 2 Louis Holiday 2 3 Word NULL 5 Sonata 5 10 The Bullets 10 14 Jose Mac. Arthur NULL 15 Confused 15 17 The Kicks NULL 16 Today NULL 18 21 West Elm NULL 11 Highlander NULL 5. More joins CS 1222_W 2018 5 -43

Using Outer Join to Duplicate Not IN Functionality (cont. ) Report artists who don’t have titles SELECT FROM Artistname SELECT Artistname Artists A FROM Artists Left Join Titles T WHERE Artist. ID NOT IN ON A. Artist. ID=T. Artist. ID (SELECT Artist. ID FROM Titles) WHERE T. Artist. ID Is Null Artistname ----------------------------- Word Jose Mac. Arthur The Kicks Today 21 West Elm Highlander 5. More joins CS 1222_W 2018 5 -44

Joining on More Than One Column r Include AND in ON or WHERE clause to link on second, third, etc. columns r Works with either Equi Join or Inner/Outer Join syntax Inner Join Select Track. Title From Tracks T Inner Join Audio. Files A On T. Title. ID=A. Title. ID And T. Tracknum=A. Tracknum Where Audio. Format='MP 3' Equi Join Select Track. Title From Tracks T, Audio. Files A Where T. Title. ID=A. Title. ID And T. Tracknum=A. Tracknum And Audio. Format='MP 3' 5. More joins CS 1222_W 2018 5 -45

Self Joins Joining two tables Table 2 Self Join Table 2 Table 1 5. More joins CS 1222_W 2018 5 -46

Example Self Joining Table 5. More joins CS 1222_W 2018 5 -47

Self-Join r Join a table to itself r Can be either an INNER or OUTER JOIN r A self join always uses two fields in a table. r Need to use table aliases for differentiate two instances of the same table 5. More joins CS 1222_W 2018 5 -48

Self Join Example List the names of all salespeople who have supervisors along with the names of their supervisors. Select Sales. Firstname As Emp. First, Sales. Lastname as Emp. Last, Sup. Firstname as Sup. First, Sup. Lastname as Sup. Last From Salespeople Sales Inner Join Salespeople Sup On Sales. Supervisor=Sup. Sales. ID Emp. First Emp. Last Sup. First Sup. Last ---------------- ------ Bob Bentley Scott Bull Lisa Williams Scott Bull Clint Sanchez Bob Bentley

Joins Compared r Inner (Equi) Joins m Reports only matched rows r Outer Joins m Reports all of records from one table plus matching records from the other table r Self Joins m Column in one table matched to another column in same table r Cross Joins m Cartesian product without dealing with matches