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.
