Say you have 2 tables that share a key field (which I'll call "key") and you want a set of fields in rows which have a matching key in both tables. This is an "Inner Join" and is the most common:
SELECT table1.field1, table2.field2
FROM table1
INNER JOIN table2 ON table1.key = table2.key
[WHERE criteria could go here...]
If you want <b>all</b> rows from table1 plus the field values from table2 where there is a match, this is an "Outer Join". The field values returned where table2 has no matching key will be NULL:
SELECT table1.field1, table2.field2
FROM table1
LEFT OUTER JOIN table2 ON table1.key = table2.key
[WHERE criteria could go here...]
Note that this is a LEFT JOIN because we want all the rows from the left-hand table (table1) and only the matching rows from the right-hand table (table2). If we had made this a RIGHT OUTER JOIN then all values from table2 would have appeared, with only the matching rows from table1.
Finally, some DBMS's support a FULL OUTER JOIN which returns <b>all</b> rows from either table, matched where possible; unmatched rows from either table result in NULL fields.
There are some other joins like CROSS JOINs, INTERSECTs, UNIONs, EXCEPTs, NATURAL JOINs etc. but they tend to be obscure. See a SQL textbook for more info.
