Advanced SQL Joins and Problem Solving Techniques in Relational Databases

1. Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 4: Joins Part II

2. Miscellany • Lab and Homework #2 • Questions? • Instructor = Pretty Happy

3. Topics for Today • Problem Solving (Page 80)‏ • Joining on Multiple Columns (Pages 83 - 84)‏ • Self Joins (Pages 84 - 86)‏ • Left and Right Joins (Pages 74 - 80)‏ • Mixed Joins (Pages 81 - 82)‏

4. Problem Solving • Step #1: Analyze the question • Single table? Multiple tables? • Step #2: Determine the relationships • Think logically using the relationship diagram • Step #3: Join tables • Join tables in logical order • Step #4: Filter results • Use the WHERE clause to filter results

5. Joining on Multiple Columns • Select all artists and studios from the same city. • SELECT ArtistName, StudioNameFROM Artists A, Studios SWHERE A.City = S.City AND A.Region=S.Region; • SELECT ArtistName, StudioNameFROM Artists JOIN Studios USING(City, Region); • SELECT ArtistName, StudioNameFROM Artists A JOIN Studios SON A.City = S.City AND A.Region=S.Region;

6. Self Joins • A self join is a table that is joined to itself • Can be used when a primary key and a foreign key exists within the same table • Lyric Database: SalesPeople table • Useful for modeling hierarchical relationships withing a single entity • Folders: Parent-Child Hierarchy • Employees: Employee Hierarchy • Only works with Equi-Join and Join On syntax

7. Self Join Example • List the first name of all sales people along with the first name of their supervisors. • Look at the SalesPeople tableSELECT *FROM SalesPeople;SELECT FirstName, SupervisorFROM SalesPeople; • Then build your solutionSELECT S1.FirstName, S2.FirstNameFROM SalesPeople S1, SalesPeople S2WHERE S1.Supervisor = S2.SalesID;

8. Outer Joins: The Problem • Question: Display a list of artists who do not have a title. • Associates each artist with his own titles...SELECT Title, ArtistNameFROM Titles T, Artists AWHERE T.ArtistID = A.ArtistID; • Associates each artist with titles by other artists...SELECT Title, ArtistNameFROM Titles T, Artists AWHERE T.ArtistID <> A.ArtistID; • Neither query give us what we want!

9. Outer Joins: The Solution • Use an outer join • SELECT ArtistNameFROM Artist NATURALLEFTJOIN TitlesWHERE Title IS NULL; • SELECT ArtistNameFROM Artist LEFTJOIN Titles USING(ArtistID)WHERE Title IS NULL; • SELECT ArtistNameFROM Artist A LEFT JOIN Titles TON A.ArtistID = T.ArtistIDWHERE T.Title IS NULL; • Can't use equi-join

10. Outer Join Types • Left Join • Every record from the left (first) table will always be listed at least once • If a matching record is found in the right (second) table, it is listed normally (same as inner join)‏ • If there are no matching records to be found in the right (second) table (zero-matching rows), the record from the left table is still reported, albeit it is associated with NULL values in the right table. • Right Join • Same as left join, but swapping left and right

11. Left Join Syntax • Two tables • SELECT attribute_listFROM table1 LEFT JOIN table2ON join_condition; • More than two tables • SELECT attribute_listFROM table1 LEFT JOIN table2 ON join_condition LEFT JOIN table3 ON join_condition ...

12. Right Join Syntax • Two tables • SELECT attribute_listFROM table1 RIGHT JOIN table2ON join_condition; • More than two tables • SELECT attribute_listFROM table1 RIGHT JOIN table2 ON join_condition RIGHT JOIN table3 ON join_condition ...

13. More Left/Right Join Syntax • NATURAL JOIN syntax • SELECT attribute_listFROM table1 NATURAL LEFT JOIN table2; • SELECT attribute_listFROM table1 NATURAL RIGHT JOIN table2; • JOIN USING syntax • SELECT attribute_listFROM table1 LEFT JOIN table2 USING(attribute); • SELECT attribute_listFROM table1 RIGHT JOIN table2 USING(attribute);

14. Left Join Examples • Left Join Example • SELECT *FROM Artists A LEFT JOIN Titles TON A.ArtistID = T.ArtistID; • SELECT *FROM Titles T LEFT JOIN Artists AON A.ArtistID = T.ArtistID; • As you can see... table order matters!

15. Right Join Examples • Right Join Example • SELECT *FROM Artists A RIGHT JOIN Titles TON A.ArtistID = T.ArtistID; • SELECT *FROM Titles T RIGHT JOIN Artists AON A.ArtistID = T.ArtistID; • As you can see... table order matters!

16. How Outer Joins Work I • Do you really want to know? • SELECT A.ArtistName AS 'ArtistName', A.ArtistID AS 'ArtistID1', T.ArtistID AS 'ArtistID2', T.Title AS 'Title' FROM Artists A, Titles T WHERE A.ArtistID = T.ArtistID UNION SELECT DISTINCT A.ArtistName, A.ArtistID, NULL, NULL FROM Artists A, Titles T WHERE A.ArtistID <> T.ArtistID AND A.ArtistID NOT IN (SELECT A.ArtistID FROM Artists A, Titles T WHERE A.ArtistID = T.ArtistID) ORDER BY ArtistID1; • Let's quickly go through this to see how a LEFT JOIN really works (not the code, but the results)‏

17. How Outer Joins Work II • Now compare that to this, which gives you the same results • SELECT A.ArtistName AS 'ArtistName', A.ArtistID AS 'ArtistID1', T.ArtistID AS 'ArtistID2', T.Title AS 'Title'FROM Artists A LEFT JOIN Titles TON A.ArtistID = T.ArtistIDORDER BY ArtistID1; • So now I hope you see that an outer join includes all the records from an inner join, plus records from unmatched rows

18. Mixed Joins • It is OK to use a mixed join syntax • SELECT M.FirstName, M.LastName, CASE WHEN G.Genre IS NULL THEN 'unknown' ELSE G.Genre END AS 'Genre'FROM Members MINNER JOIN XrefArtistsMembers X ON M.MemberID = X.MemberIDINNER JOIN Artists A ON X.ArtistID = A.ArtistIDLEFT JOIN Titles T ON A.ArtistID = T.ArtistIDLEFT JOIN Genre G ON T.Genre = G.Genre;

19. Outer Join Tips and Techniques • Always construct joins one-by-one, on a left-to-right basis, asking yourself, “What type of join do I need here?” • When deciding on a join type, ask yourself the following question: • Do I need to show zero-matching records? If yes, use an outer join. If no, use an equi- or inner join.

20. Sample Problems • Report the names of all artists that came from e-mail that have not recorded a title. • For ALL members, list thier first name, last name, and their associated genre. If a member doesn't have an associated genre, show the genre as 'unknown.'