The second statement you wrote above is not correct SQL since the JOIN
syntax is part of the FROM clause, not the WHERE clause.
The kinds of joins I know are:
Cross join: cartesian product of two tables (taking each row of table1
and appending each row of table2).
Inner join: "normal" join (take each row of table1 matching certain
criteria and append each row of table2 matching certain criteria).
Outer join: "preserving" join (take each row of table1 and append each
row of table2 matching certain criteria); here the difference between
LEFT and RIGHT (and possibly FULL) is which table is going to be
preserved (both in case of FULL); the columns of the non preserved table
will be filled with null values if there is no matching row in the non
preserved table.
The ways to specify the criteria (or conditions) I know are:
Natural: match columns with the same name
Key: match PK/FK columns
ON: explicit condition(s) after the JOIN statement
WHERE clause: specify condition(s) in WHERE clause
OK, now to make the whole thing a bit more perceivable:
Table A
idA (PK) valueA
1 a
2 b
3 c
Table B
idB (PK) idA (FK) valueB
1 1 Z
4 2 Y
5 6 X
Cross join:
SELECT * FROM A, B;
idA valueA idB idA valueB
1 a 1 1 Z
1 a 4 2 Y
1 a 5 6 X
2 b 1 1 Z
2 b 4 2 Y
2 b 5 6 X
3 c 1 1 Z
3 c 4 2 Y
3 c 5 6 X
Inner join:
SELECT * FROM A NATURAL JOIN B; match A.idA and B.idB (same name)
SELECT * FROM A KEY JOIN B; match A.idA and B.idB (PK/FK)
SELECT * FROM A JOIN B ON A.idA = B.idA; explicit
SELECT * FROM A JOIN B WHERE A.idA = B.idA;
idA valueA idB idA valueB
------------------------------------------------
1 a 1 1 Z
2 b 4 2 Y
It might be that a natural join eliminates one of the idA columns.
Left outer join (all four ways like inner join possible):
SELECT * FROM A LEFT OUTER JOIN B ON A.idA = B.idA;
idA valueA idB idA valueB
1 a 1 1 Z
2 b 4 2 Y
3 c null null null
Right outer join (all four ways like inner join possible):
SELECT * FROM A RIGHT OUTER JOIN B ON A.idA = B.idB;
idA valueA idB idA valueB
1 a 1 1 Z
2 b 4 2 Y
null null 5 6 X
Full outer join (all four ways like inner join possible):
SELECT * FROM A FULL OUTER JOIN B ON A.idA = b.idB;
idA valueA idB idA valueB
1 a 1 1 Z
2 b 4 2 Y
3 c null null null
null null 5 6 X
