Chapter 7 - Joining Tables

1. Chapter 7 - Joining Tables • No Explicit Join in SQL • Joins are implicitly expressed in theWHERE clause

2. Two Tables at a Time • Can Join Only Two Tables at a Time • Use 'Connecting' Columns • datatypes must match • or convert correctly • More than one Join can be specified in a Single SELECT statement

3. Join Syntax • SELECT select_listFROM table_1, table_2WHERE [table_1.]column join_operator [table_2.]column

4. Example • SELECT title, pub_nameFROM titles, publishersWHERE titles.pub_id = publishers.pub_id; • Qualify the column names with the table names with there is ambiguity

5. Aliases • Remember you can use short names as aliases for the table names • SELECT title, pub_nameFROM titles t, publishers pWHERE t.pub_id = p.pub_id;

6. Example • Find the editors of the Secrets of Silicon Valley • SELECT ed_lname, ed_fname, ed_posFROM editors e, titleditors te, titles tWHERE e.ed_id = te.ed_idAND t.title_id = te.title_idAND title = 'Secrets of Silicon Valley';

7. Unplanned Joins • Joins may be planned • Primary Keys • Foreign Keys • Joins do not have to be planned • e.g. you may join the "city" column on the authors and editors table

8. Join Operators • You can use any of the relational operators to express the relationship between the Join columns • Equality most often used • SELECT title, pub_nameFROM titles, publishersWHERE pub_name = ‘New Age Books’AND title.pub_id = publishers.pub_id;

9. Column Compatibility • Join columns need not have the same name • They should be of the same datatype (or convert to the same datatype) • The join should be meaningful • Nulls found in connecting columns will never join.

10. Data Replication • Rows are returned for every successful join • Returned rows may replicate data for display. (This does not change the data.)

11. Connecting Columns • You don’t have to put connecting columns in the select list • You do need to qualify connecting columns that have the same name in the select list.

12. Example • SELECT ed_name, editors.cityFROM editors, publishersWHERE editors.city = publishers.cityAND pub_name = ‘Algodata Infosystems’;

13. Exercise • Show a list of the authors whose last name is Ringer and the city they live in, but only if they have written any books. Put in last name order.

14. Answer • SELECT au_lname, au_fname, cityFROM authors a, titleauthors taWHERE a.au_id = ta.au_id; • If no join is made from an author (au_id) to the titleauthors table then the author has not written any books.

15. Join Operators • > greater than • >= greater than or equal to • < less than • <= less than or equal to • < > not equal to • *= Left Outer Join • =* Right Outer Join

16. Example • SELECT DISTINCT s.sonum, s.stor_id, s.sdate, sd.date_shippedFROM sales s, salesdetails sdWHERE s.sale_date < sd.date_shippedAND s.sonum = sd.sonum;

17. Joining More than Two Tables • We can only join two tables at a time… … but we can do more than one ‘two-table’ join in a Select statement.

18. Exercise • Show a list of books and the authors who’ve written them. Use aliases. • Remember: Many-to-Many relationships are modeled with an association table • Need to join three tables to get information from both associated tables.

19. Solution • SELECT title, au_lname, au_fnameFROM titles t, authors a, titleauthors taWHERE t.title_id = ta.title_idAND a.au_id = ta.au_idORDER BY 1, 2; • Notice that the Primary keys are joined, but not displayed in the Select list.

20. Another Exercise • Show a list of books (by book title) and the number of authors that have written each book, if more than one author wrote the book.

21. Joins that don’t match • Joins only return the rows that satisfy all the conditions in the WHERE clause……Unless you use an “Outer Join”

22. Outer Joins • Outer Joins… • display all the rows from one table (that meet the WHERE clause criteria) • display information from the other table only if it matches the criteria, otherwise it displays NULL

23. Left vs. Right Outer Joins • A left outer join displays all the criteria matching rows from the first named table and the matching information only from the second named table. • A right outer join displays all the criteria matching rows from the second named table and the matching information only from the first named table.

24. Example - Left Outer Join • SELECT au_fname, au_lname, pub_nameFROM authors, publishersWHERE authors.city *= publishers.city; • This displays all the rows from the authors table and displays the pub_name in the results set where the authors city is equal to a city that the publisher is headquatered in.

25. Example - Right Outer Join • SELECT au_fname, au_lname, pub_nameFROM authors, publishersWHERE authors.city =* publishers.city; • This displays all the rows from the publishers table and displays the authors name columns in the results set where the publishers city is equal to a city that the author resides in.

26. Exercise • Part 1 - Show a list of the authors who are also editors. Display their last name and editor position. • Part 2 - Show a list of all the authors. Display their last name and display their editor position if they are also an editor.

27. Answer • Part 1-SELECT au_lname, au_fname, ed_posFROM authors a, editors eWHERE au_id = ed_id; • Part 2-SELECT au_lname, au_fname, ed_posFROM authors a, editors eWHERE au_id *= ed_id;

28. Last Slide - Chapter 7 • Assignment #5 due next week.