SQL Joins Explained

 http://en.wikipedia.org/wiki/Join_(SQL)
JOINs allow to match tables in which one or several columns can be correlated (so a table.column is considered a FOREIGN KEY of other table).

Inner Join

This two expressions are equivalent:

SELECT *
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

vs

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

In this case the Join simply provides a pre-filter of the data, clarifying syntax and may be useful when having additional conditions in the Where clause (that will be applied only to filtered rows).

Left/Right/Full Outer Join

The main difference between Inner and Outer Join is that the later will return also entries with no matching; returning just key and NULL values for those from the non.matching table.

SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

You can use either LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN.

Self Join

An INNER JOIN can be used on a same table to have like an expanded-group-by:

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F
INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

It needed the use of aliases S and F. It would return every employee linked to its conationals, the '<' comparer in IDs avoids duplication.
The group_concat will return similar results but with conationals group in one row:

SELECT F.Country, group_concat(F.LastName) AS LastNames
FROM Employee F
GROUP BY F.Country;

Cross Join

Will return a cartessian product, all-with-all, for all entries in both tables.

SELECT *
FROM employee
CROSS JOIN department;

Union / Intersect

Results of different selects can be unified, although casting may be needed.

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID
 
UNION ALL
 
SELECT employee.LastName, employee.DepartmentID, CAST(NULL AS VARCHAR(20))
FROM employee
WHERE NOT EXISTS (SELECT * FROM department WHERE employee.DepartmentID = department.DepartmentID)

UNION will remove duplicated rows while UNION ALL will preserve them.
INTERSECT will return instead only the rows that appear in both queries.
EXCEPT will return only those that do not appear in both.