Hi,
Here are the main ones:
1 - INNER JOIN - shows all the records where the two tables have a matching value in the joined fields.
e.g.
SELECT *
FROM tblCustomer
INNER JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
This will show all of the customers who have made an order, along with any order information contained in the tblOrder table.
2 - LEFT OUTER JOIN - shows all of the records from the first table listed, regardless of whether there is a matching value in the second table.
e.g.
SELECT *
FROM tblCustomer
LEFT OUTER JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
WHERE tblOrder.OrderID IS NULL
This will show all of the records from the customer table who do not have a corresponding record in the order table, i.e. all of the customers who have not made an order.
3 - RIGHT OUTER JOIN - this will show all of the records from the second listed table regardless of whether they have a matching value in the first listed table.
e.g.
SELECT *
FROM tblCustomer
RIGHT OUTER JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
WHERE tblCustomer.CustomerID IS NULL
This will show all of the records from the Order table who have no customer record associated with them - although you could do this much more easily by saying:
SELECT * FROM tblOrder WHERE tblOrder.CustomerID IS NULL
You would more commonly use RIGHT OUTER JOINs when you wanted to compare very similar tables to find the records that exist in one table but not the other.
4 - FULL OUTER JOIN - this will show all of the records from both tables involved in the join.
e.g.
SELECT *
FROM tblCustomer
FULL OUTER JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
This will show all of the customer and order records including customers with no orders, and orders with no customers.
5 - CROSS JOIN - this shows each record in the fisrt table, combined with each record in the second table, also known as the Cartesian product of the tables.
e.g.
SELECT *
FROM tblMonth
CROSS JOIN tblYear
If tblMonth contains the list of month names January to December, and tblYear contains a list of years from 2000 to 2011 the results will show every combination of month and year possible.
Hope all that helps!