Using Relational Databases and SQL

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

2. Topics for Today • Self Joins (Pages 84 - 86)‏ • Left and Right Joins (Pages 74 - 80)‏ • Mixing Inner and Outer Joins (Pages 81 - 82)‏

3. Self Joins • A self join is a table that is joined to itself • Typical case is when a foreign key references a primary key in the same table • Non-typical case is when you want to find pairs of things from within the same table • Useful for modelling hierarchical relationships within a single table • Folders: Parent-Child Hierarchy • Employees: Employee Hierarchy

4. Typical Self Joins • Employee Table: • EmployeeID (primary key) • FirstName • LastName • JobTitle • Salary • SupervisorID (foreign key references EmployeeID)

5. Typical Self Joins #1 List all employees (first and last names) who are supervised by no one. #2 List all employees whose supervisors are supervised by no one. #3 List all employees whose supervisor’s supervisors are supervised by no one.

6. Typical Self Joins • Solutions (for #2 and #3): • SELECT E.FirstName, E.LastNameFROM Employee E JOIN Employee SON E.SupervisorID = S.EmployeeIDWHERE S.SupervisorID ISNULL; • SELECT E.FirstName, E.LastNameFROM Employee EJOIN Employee S ON E.SupervisorID = S.EmployeeIDJOIN Employee SS ON S.SupervisorID = S.EmployeeIDWHERE SS.SupervisorID ISNULL;

7. Self Joins in the Movie Database • Good news! There are no hierarchical table relationships, so no typical cases • There are, however, some non-typical self join possibilities, dealing with pairings

8. Self Joins in the Movie Database • Example • -- List all pairs of movie-related people who were born in the same city, state/province, and country. Do not pair any people with themselves and do not list duplicate pairs. • SELECT P1.FirstName, P1.LastName, '...', P2.FirstName, P2.LastNameFROM People P1 JOIN People P2ON (P1.BirthCity = P2.BirthCityAND P1.BirthStateProvince = P2.BirthStateProvinceAND P1.BirthCountry = P2.BirthCountry)WHERE P1.PersonID < P2.PersonID;

9. Self Joins in the Movie Database • Alternate (shorter) solution: • -- List all pairs of movie-related people who were born in the same city, state/province, and country. Do not pair any people with themselves and do not list duplicate pairs. • SELECT P1.FirstName, P1.LastName, '...', P2.FirstName, P2.LastNameFROM People P1 JOIN People P2USING(BirthCity, BirthStateProvince, BirthCountry)WHERE P1.PersonID < P2.PersonID;

10. Outer Joins: The Problem • Question: Display a list of movie-related people who are not actors.

11. Outer Joins: The Problem • Question: Display a list of movie-related people who are not actors. • Let’s try an inner join • SELECT FirstName, LastName, MovieID, CharacterNameFROM People P JOIN XRefActorsMovies AON P.PersonID = A.ActorID; • Incorrect! An inner join associates people (in P) with movie characters (in A) for which they played.

12. Outer Joins: The Problem • Question: Display a list of movie-related people who are not actors. • Let’s try negating the inner join condition • SELECT FirstName, LastName, MovieID, CharacterNameFROM People P JOIN XRefActorsMovies AON P.PersonID <> A.ActorID; • Incorrect as well! An inner join with a negated join condition associates people (in P) with movie characters (in A) for which they did not play.

13. Outer Joins: The Problem To solve the problem, we need to know which PersonIDs in the People table ARE NOT IN the ActorID column of the XRefActorsMovies table?

14. Outer Joins: The Solution • Use an outer join • SELECT FirstName, LastNameFROM People P LEFTJOIN XRefActorsMovies AON P.PersonID = A.ActorIDWHERE ActorID ISNULL; • SELECT FirstName, LastNameFROM XRefActorsMovies A RIGHT JOIN People P ON P.PersonID = A.ActorIDWHERE ActorID ISNULL; • Can't use equi-join

15. Outer Join Definition • Outer Join Definition • Outer Join = Matching Records (Inner Join) + Zero-Matching Records (Anti Join + NULL) • Provides information about records in one table that ARE and ARENOT in the second table • Since we must always have a complete table, NULL values are concatenated to the anti join results

16. Outer Join Visualization

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

18. Outer Join Tips • If you need to use an outer join, always use a LEFT JOIN in MySQL • LEFT JOIN is same as RIGHT JOIN • Difference is do you prefer: • Joining left to right? (LEFT JOIN) • Joining right to left? (RIGHT JOIN) • Mathematical expression and the English grammar read from left to right… therefore I always prefer the LEFT JOIN!

19. Left Join Syntax • Two tables • SELECT attribute_listFROM table1 LEFTJOIN table2ON join_condition; • More than two tables • SELECT attribute_listFROM table1LEFTJOIN table2 ON join_conditionLEFTJOIN table3 ON join_condition ... *Note that you may also use JOIN USING syntax as well…

20. Right Join Syntax • Two tables • SELECT attribute_listFROM table1 RIGHTJOIN table2ON join_condition; • More than two tables • SELECT attribute_listFROM table1RIGHTJOIN table2 ON join_conditionRIGHTJOIN table3 ON join_condition ... *Note that you may also use JOIN USING syntax as well…

21. More Left/Right Join Syntax • NATURAL JOIN syntax • SELECT attribute_listFROM table1 NATURALLEFTJOIN table2; • SELECT attribute_listFROM table1 NATURALRIGHTJOIN table2; • JOIN USING syntax • SELECT attribute_listFROM table1 LEFTJOIN table2 USING(attribute); • SELECT attribute_listFROM table1 RIGHTJOIN table2 USING(attribute);

22. Outer Join Order • Join order makes a big difference • A LEFTJOIN B is not the same as B LEFTJOIN A • The expression “People (People table) who are not actors (XRefActorsMovies table)” does not say the same thing as, “Actors (XRefActorsMovies table) who are not people (People table)” • Why not? • Only SOME people are actors (A LEFT JOIN B) • However, ALL actors are people (B LEFT JOIN A) • Thus, you will get different results!

23. Outer Join Order Example Try it and you will see that you do not get the same results!

24. Extracting Data From Outer Joins • To extract the “zero-matching” results from an outer join, you must test the primary key (from the opposite side of the outer join) for NULL • For example, for A LEFT JOIN B, to extract the records in A that have no matches in B, you must test the primary key in B (the right table) for NULL in the WHERE clause

25. Extracting Data From Outer Joins

26. Extracting Data From Outer Joins

27. Mixing Inner and Outer Joins • It is OK to mix inner joins with outer joins • Once you start an outer join, you usually have to keep doing outer joins (if you join more tables) • NULL does not match up with anything • Try the following query with mixed join types: • -- List the first and last names of ALL movie-related people along with the titles of any movies that they have starred in. If a person has not acted in any movies, just display their first and last name along with a NULL movie title.

28. Mixing Inner and Outer Joins • Solution #1: Correct • SELECT FirstName, LastName, TitleFROM People PLEFTJOIN XRefActorsMovies AON P.PersonID = A.ActorIDLEFTJOIN Movies MON A.MovieID = M.MovieID;

29. Mixing Inner and Outer Joins • Solution #2: Incorrect • SELECT FirstName, LastName, TitleFROM People PLEFTJOIN XRefActorsMovies AON P.PersonID = A.ActorIDINNER JOIN Movies MON A.MovieID = M.MovieID;

30. Sample Problems • #1 Produce a listing of all genres that are not associated with any movie. • #2 Report the first and last names of all movie-related people born in the US who are not producers, directors nor actors.

31. Sample Solutions • #1 • SELECT G.GenreFROM Genres GLEFTJOIN XRefGenresMovies XGMON G.Genre = XGM.GenreWHERE MovieID ISNULL;

32. Sample Solutions • #2 • SELECT PersonID, FirstName, LastNameFROM People PLEFTJOIN XRefActorsMovies AON P.PersonID = A.ActorIDLEFTJOIN XRefDirectorsMovies DON P.PersonID = D.DirectorIDLEFTJOIN XRefProducersMovies RON P.PersonID = R.ProducerIDWHERE A.ActorID ISNULLAND D.DirectorID ISNULLAND R.ProducerID ISNULL;