FYI, there are different types of JOINs;inner, outer, right and left. Don't ask me what the difference is because I don't know. Trial and error when it comes to joins.
Thank you for confirming what I wrote Singh...
Now a left or right join are outer joins... so there are actually 3 not 4 types of joins.
Now as to what they do.... Lets say we have 2 tables with these key values:
Table1
Key1
1
3
5
7
9
Table2
Key2
1
2
3
4
5
An Inner join between these 2 tables will return
Key1 - Key 2
1 - 1
3 - 3
5 - 5
i.e only the two keys that exist in both tables
Left join Table1 to Table2 (arrow in the designer going from table1 to table2)
Key1 - Key2
1 - 1
3 - 3
5 - 5
7 - N
9 - N
N = Null value
i.e. All values from table1, with additions from table2 where available.
Right join Table1 to Table2 (arrow in the designer going from table2 to table1)
Key1 - Key2
1 - 1
N - 2
3 - 3
N - 4
5 - 5
The reverse of the same left join, all information from table2 with additions from table1 where available.
Note that a left and right join are mostly a matter of visuality...
Table1 -> Table2 (Left outer join)
Will return the same as
Table2 <- Table1 (Right outer join)
Actually there is a forth way of joining tables, which in VERY rare occations it very usefull. NOT joining them.
In this case that will return
1 - 1
1 - 2
1 - 3
1 - 4
1 - 5
3 - 1
3 - 2
3 - 3
etc.
Joining each record in table1 to each record in table2, this is also called a "cartagian product". The times to use this (properly) are rare and far between, but when you do need it it can be immensly powerfull.
Dont hesitate to ask if my explenation was not clear enough.
P.S. It would be nice to know if it is clear enough as well.
