Jeff Atwood, the author of the Coding Horror blog, has a nice visual explanation of SQL joins. For the non-visual explanation, I refer you to the following articles:
However, ANSI SQL join syntax does not always work in Oracle, at least up to version 10gR2.