Shoutbox

SQL Help (Joins) - Printable Version

-Shoutbox (https://shoutbox.menthix.net)
+-- Forum: MsgHelp Archive (/forumdisplay.php?fid=58)
+--- Forum: Skype & Technology (/forumdisplay.php?fid=9)
+---- Forum: Tech Talk (/forumdisplay.php?fid=17)
+----- Thread: SQL Help (Joins) (/showthread.php?tid=62075)
SQL Help (Joins) by lordy on 06-30-2006 at 12:28 AM

Hey guys

I'm learning some MySQL stuff for future website developments... and one thing I'm having trouble understanding is joins.

I understand HOW to do them, I just don't understand WHAT they do exactly...

This book i'm reading atm mentions left joins and inner joins, but i don't understand the difference between them... Would someone be able to explain to me the different types of joins and what the differences are between them?

and what they actually do :P

RE: SQL Help by Jhrono on 06-30-2006 at 12:38 AM

I found this page at w3schools.com, maybe it'll help you ;). I don't have the time to read it right now though :(

http://www.w3schools.com/sql/sql_join.asp

RE: SQL Help by lordy on 06-30-2006 at 01:24 AM

I think I get it:

Inner Join - Returns rows from both tables where the condition(s) are matched.

Left Join - Returns all rows from the first table, and those rows from the second table where there is a match with the condition(s).

Right Join - Returns all rows from the second table and those rows from the first table where there is a match with the condition(s).

That is what I'm concluding from what I've read on that page... someone please confirm

RE: SQL Help (Joins) by Adeptus on 06-30-2006 at 02:10 AM

Your understanding is essentially correct.  I will give you my definitions anyway, though.

quote:
Originally posted by lordy
Inner Join - Returns rows from both tables where the condition(s) are matched.
An INNER JOIN returns a single joined row from the individual rows of joined tables, where the join conditions are met.  Nothing is returned for rows in either table that do not meet the join conditions.

quote:
Originally posted by lordy
Left Join - Returns all rows from the first table, and those rows from the second table where there is a match with the condition(s).
Right Join - Returns all rows from the second table and those rows from the first table where there is a match with the condition(s).
The full names for these are LEFT OUTER JOIN and RIGHT OUTER JOIN.  An OUTER join returns all rows from one of the tables (LEFT or RIGHT relative to JOIN keyword and, of course, subject to any applicable WHERE clause).  Where the other table has no rows that meet the join conditions (and WHERE clause, if any), the row from the designated (LEFT or RIGHT) table will still be returned in your results, but the values of joined columns from the other table will be NULL.

Finally: 

1) There is also a CROSS JOIN, which takes no conditions and returns all possible combinations of joined rows in both tables, subject to any applicable WHERE clause.

2) You don't necessarily have to SELECT any columns to return from a table involved in a join.  You can still use INNER JOIN against another table to limit the returned results to those that match your join condition against said table.  This trick often enables one to avoid using a subquery (although there is nothing wrong with using subqueries on databases that support them).

3) Using table aliases, you can join the same table to itself as many times as needed.  This comes in handy when you have rows with a parent-child relationship contained in the same table, as well as for finding duplicates.
RE: SQL Help (Joins) by lordy on 06-30-2006 at 02:36 AM

Thanks heaps Adeptus :)