This is based on excerpts from Wikipedia’s “Join (SQL)” article, which can be found at http://en.wikipedia.org/wiki/Join_(SQL). That article contains way more detail than most people really need to know, and also touches on about 20 different types of joins ... where in reality the majority of people just use two: inner and left outer. So I tried to filter the info down to just the basics that you need to know.
A SQL JOIN clause combines records from two tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each.
All subsequent explanations on join types in this article make use of the following two tables. The rows in these tables serve to illustrate the effect of different types of joins and join-predicates. In the following tables, Department.DepartmentID is the primary key, while Employee.DepartmentID is a foreign key.
Inner Joins
An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate. This type of join occurs most commonly in applications, and represents the default join-type.
Programmers should take special care when joining tables on columns that can contain NULL values, since NULL will never match any other value (or even NULL itself), unless the join condition explicitly uses the IS NULL or IS NOT NULL predicates.
As an example, the following query takes all the records from the Employee table and finds the matching record(s) in the Department table, based on the join predicate. The join predicate compares the values in the DepartmentID column in both tables. If it finds no match (i.e., the department-id of an employee does not match the current department-id from the Department table), then the joined record remains outside the joined table, i.e., outside the (intermediate) result of the join.
SELECT *
FROM Employee
INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
Notice that the employee "Jasper" and the department "Marketing" does not appear. Neither of these has any matching records in the respective other table: "Jasper" has no associated department and no employee has the department ID 35. Thus, no information on Jasper or on Marketing appears in the joined table. Depending on the desired results, this behavior may be a subtle bug. Outer joins may be used to avoid it.
Outer Joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
Left Outer Joins
The result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
For example, this allows us to find an employee's department, but still to show the employee even when their department does not exist (contrary to the inner-join example above, where employees in non-existent departments are excluded from the result).
Example of a left outer join, with the additional result row italicized:
SELECT *
FROM Employee
LEFT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
