sql joins - Joining more than one table

I am trying to understand 开发者_如何学Cjoins and im a bit confused. I know how to join tables using

=
<=

IN exists and not exists

I was trying to try and understand the use of INNER JOIN, LEFT OUTER JOIN, USING etc but it is so confusing. The major problem i am having is that different people refer to them using different names. Is there a simple explanation of the different types of joins and what other names they are known as. For example, while googling i came across the following types

♦ Simple Join
♦ Equi join
♦ Natural Join
♦ Outer Join
♦ Self Join
♦ Cartesian join
♦ Inner join
♦ Nonequi join
♦ Theta join
♦ Self join 
♦ Cross join
♦ Cross Joins
♦ Natural Joins
♦ Inner Join with USING Clause
♦ Inner Join with ON Clause
♦ Left Outer Join
♦ Right Outer Join
♦ Full OuterJoin

The majority of the above are duplicates, i.e. its the same type of join but with a different name. I am sure all the above can be recreated using one of (=, !=, not in, in exists etc) but I am struggling to undestand which is which and the difference between them. A diagram would probably help :)

Jeff Atwood to the rescue

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

For some of the ones that weren't explained by Matthew's post:

Simple Join - A non explicit join, it defaults to an inner join

a join b on a.id = b.id

Natural Join - This does an inner join on all columns with the same name

a natural join b

Self Join - This is joining a table to itself, it can any other type of join (inner self join, outer self join, etc)

a "a1" join a "a2" on "a1".id = "a2".id

Cartesian join - This is every possible combination of rows, you'll always end up with the product of the number of rows from both tables. You do it with an inner join without specifying a join condition

a join b

Cross join - This is a synonym for a Cartesian join

Inner Join with USING Clause - This is an alternate syntax for join conditions, you can use it if both tables have matching column names

a join b using (id)

Inner Join with ON Clause - This is the same as what I showed for a simple join, the only other syntax is to join in the where clause (as below)

a join b where a.id = b.id

Left Outer Join - Same as a left join Right Outer Join - Same as a right join. It's like a left join, but you get nulls on the first table rather than the second one

by the way, just reading about JOIN. Something change on a MySQL Syntax? Oracle use PLSQL syntax it is a little different from other databases..

I have the follow situation here

Fisrt Query

SELECT * FROM tbl1 LEFT JOIN (tbl2, tbl3, tbl4)
    ON (tbl2.field1=tbl1.field1 AND tbl3.filed2=tbl1.field2 AND tbl4.field3=tbl1.field3)

Second Query

SELECT * FROM tbl1 LEFT JOIN (tbl2 CROSS JOIN tbl3 CROSS JOIN tbl4)
    ON (tbl2.field1=tbl1.field1 AND tbl3.filed2=tbl1.field2 AND tbl4.field3=tbl1.field3)

The Fisrt Query is the same of Second Query.

Thanks Everyone!

EDIT: Just came across http://www.gplivna.eu/papers/sql_join_types.htm which, although technical, provides a rather good overview of sql join types. Worth a look

Here is another page with some of the joins explained (http://blog.noobtech.com/index.php/2009/02/sql-joins-visual-cheat-sheet/)

The page I was just about to post was the one which @matthew vines already posted :)

The Oracle SQL Language Reference has a good section on what joins are and the different join types.