Inner Joins- Outer Joins

In Access there are INNER JOINS, LEFT (OUTER) and RIGHT (OUTER)

I'll come with an example shortly!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A32A.asp

http://msdn.microsoft.com/library/en-us/office97/html/output/F1/D2/S5A318.asp

Basically an INNER JOIN will return records ONLY when there are related records in both tables. An OUTER (left or right) will return records from either the left or right table even if there are no records in the other table. LEFT or RIGHT just decides which table you are interested in seeing records from.
It might be a good idea to make a database with two tables similar to the example in the link above.

Going back to my first comment
"In Access there are INNER JOINS, LEFT (OUTER) and RIGHT (OUTER)"
The reason OUTER is in () is because it is not needed as it is obviously an OUTER if it isnt an INNER!

So we have three INNER, LEFT and RIGHT

You got also the CROSS JOIN - but be carefull if you try it out. The cross join or cartesian product is each row in each table multiplied with all rows in the other table - i.e. table A has 100 rows, table B has 100 - result is 100*100 rows.

Last you got the FULL OUTER JOIN, that is a combined LEFT and RIGHT OUTER JOIN (all rows from each table with nulls for missing criterias)

So you will have:
INNER JOIN, LEFT/RIGHT OUTER JOIN, FULL OUTER JOIN and CROSS JOIN.

Last there is the UNION and UNION ALL which is not join operators, but just allows you to add to tables together (the ALL keyword allows duplicate rows, without ALL, there is an implicit DISTINCT in a UNION statement.

trer>Do we have CROSS JOIN's and FULL OUTER JOIN's in Access?

ah... damn.. I wasn't aware it was Access only. In that case, there is no FULL OUTER JOIN, that is, I could not find a way of handling it.

About CROSS JOINS; Well, you cannot use the phrase CROSS JOIN, but can still do a one using the "old" syntax - like "SELECT * FROM table1, table2".

For terib; My rule of thumb for using CROSS JOINs is, that it MAY be a solution whenever several outer joins are needed in a query.

The general problem with outer join is, that a outer join performs badly since indexes cannot be used.

A cross join does not have that weakness, so a cross join combined with a inner join (plus a where condition) sometimes performs better than several outer joins. This is the case on real databases, and I think it hold for Access as well.

About SELF JOIN - that is just a case of joining a table to itself.

A very simple example

id parent_id name
1  0        Hans
2  1        Peter
3  2        Jens

from this follows that Jens is the child of Peter who(m?) is the child of Hans. Hans is then the grandfather of Jens

The self join is  between id and parent_id

Tak for hjælpen - trer får 10 point for ulejligheden.