{"id":60,"date":"2020-10-20T20:03:01","date_gmt":"2020-10-20T20:03:01","guid":{"rendered":"https:\/\/enter77.ius.edu\/cjkimmer\/?page_id=60"},"modified":"2020-10-20T20:03:01","modified_gmt":"2020-10-20T20:03:01","slug":"self-joins","status":"publish","type":"page","link":"https:\/\/enter77.ius.edu\/cjkimmer\/self-joins\/","title":{"rendered":"Self Joins"},"content":{"rendered":"\n

We\u2019ve seen the INNER JOIN before where two tables are \u201ccombined\u201d based on a primary-foreign key pairing. The foreign key references the primary key, and the primary key uniquely identifies an entire row. In that way, a row containing the foreign key is combined with the row containing the primary key. In the Sailors Reserve Boats database, that works out to be possible only between the Sailors\/Reserves pairing and the Boats\/Reserves pairing. Let\u2019s switch gears and look at the golf club database since it has a little more potential for different pairings. In particular, it has the three tables representing Members Enter Tournaments from the ERD, and it has that nifty property that Members Coach other Members. Members Enter Tournaments is\u00a0exactly<\/em>\u00a0the same as Sailors Reserve Boats, so any query we can write for sailors and boats can be rewritten for golfers and tournaments. That doesn\u2019t mean we can find the color of a tournament a golfer enters the same we can find the color of a boat. It means we join the tables together in the same way, filter by row (WHERE clause), and then return the necessary columns (SELECT) for the final answer. They\u2019re both many-to-many relationships in either database, so they\u2019re “structurally” or conceptually the same.<\/p>\n\n\n\n

A special case of the INNER JOIN is a self join. As far as I know, SQL doesn\u2019t have a keyword for it, so it\u2019s written lower case. When the foreign key references a primary key\u00a0in the same table, we have to use two copies of the table for the join, so the table is\u00a0joined with itself<\/em>. We shouldn\u2019t be scared of two copies of the same table now. We\u2019ve seen it with nested and intersection queries before. If our query is \u201cFind the names of all coaches,\u201d we\u2019re in a bit of a pickle. We can pretty easily find the names of all members, but coaches is a little tougher. To match up a golfer with a coach we use the coach foreign key and join it with the memberID primary key of a second copy of the same Member table. Let\u2019s call one copy S for students and C for coaches, so our query would be something like<\/p>\n\n\n\n

SELECT *\nFROM Member S INNER JOIN Member C ON S.coach = C.MemberID<\/pre>\n\n\n\n
where this would give us all the information about golfers combined with their coaches. Notice that students have the coach property and we use the list of coaches for the primary key. Now we can deduce that S.LastName gives us the names of students, and C.LastName gives us the names of coaches. So our query would be something like<\/p>\n\n\n\n
SELECT C.FirstName, C.LastName\nFROM Member S INNER JOIN Member C ON S.Coach = C.MemberID<\/pre>\n\n\n\n
That\u2019s pretty much all their is to a self join, but relationships-to-self (unary relationships from the ERD) in databases lead to other queries. For instance, \u201cFind the names of golfers with coaches.\u201d Not every golfer has a coach, so this query is really asking us for which golfers (which rows in the Member table) participate in the self join above. We actually don\u2019t need the second table for coaches to answer that question. If a golfer has a coach, their coach attribute has a value, if not, it is NULL. We are being asked which golfers do not have a NULL there.<\/p>\n\n\n\n
SELECT FirstName, LastName\nFROM Member\nWHERE Coach IS NOT NULL<\/pre>\n\n\n\n
That\u2019s all! What about the opposite, \u201cFind the names of golfers that do not have coaches.\u201d<\/p>\n\n\n\n
SELECT FirstName, LastName\nFROM Member\nWHERE Coach IS NULL<\/pre>\n\n\n\n
In either case, this last query asks us for golfers that\u00a0do NOT participate<\/em>\u00a0in that self join. We’ve seen that there\u2019s another special way we can get that information using a LEFT JOIN. So, if we do our self-join query above but replace INNER JOIN with LEFT JOIN we have<\/p>\n\n\n\n
SELECT S.LastName AS StudentName, C.LastName AS CoachName\nFROM Member S LEFT JOIN Member C ON S.Coach = C.MemberID<\/pre>\n\n\n\n
and get the result<\/p>\n\n\n\n
StudentName<\/th>CoachName<\/th><\/tr><\/thead>
McKenzie<\/td>Nolan<\/td><\/tr>
Stone<\/td>NULL<\/td><\/tr>
Nolan<\/td>NULL<\/td><\/tr>
Branch<\/td>NULL<\/td><\/tr>
Beck<\/td>NULL<\/td><\/tr>
Burton<\/td>Nolan<\/td><\/tr>
Cooper<\/td>Nolan<\/td><\/tr>
Spence<\/td>NULL<\/td><\/tr>
Olson<\/td>NULL<\/td><\/tr>
Pollard<\/td>Cooper<\/td><\/tr>
Sexton<\/td>Cooper<\/td><\/tr>
Wilcox<\/td>NULL<\/td><\/tr>
Schmidt<\/td>Nolan<\/td><\/tr>
Bridges<\/td>Cooper<\/td><\/tr>
Young<\/td>NULL<\/td><\/tr>
Gilmore<\/td>Nolan<\/td><\/tr>
Taylor<\/td>Cooper<\/td><\/tr>
Reed<\/td>Cooper<\/td><\/tr>
Willis<\/td>NULL<\/td><\/tr>
Kent<\/td>NULL<\/td><\/tr><\/tbody><\/table>
The Member table outer joined with itself on the coach\/member ID key pairing.<\/figcaption><\/figure>\n\n\n\n
which illustrates how we get all the golfers in the list as students, and only those golfers with coaches have a CoachName value appearing in the answer. <\/p>\n","protected":false},"excerpt":{"rendered":"
We\u2019ve seen the INNER JOIN before where two tables are \u201ccombined\u201d based on a primary-foreign key pairing. The foreign key references the primary key, and the primary key uniquely identifies …<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"featured_image_src":null,"featured_image_src_square":null,"_links":{"self":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/60"}],"collection":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/comments?post=60"}],"version-history":[{"count":4,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/60\/revisions"}],"predecessor-version":[{"id":436,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/60\/revisions\/436"}],"wp:attachment":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/media?parent=60"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}