joins, self join, inner join, outer join

A join combines records from two or more tables in a relational database. In the Structured Query Language (SQL), there are two types of joins: "inner" and "outer". Outer joins are subdivided further into left outer joins, right outer joins, and full outer joins.

Cross joins
Cross joins are aptly named, because if you try to perform one on a large database, your users and systems programmers will get very cross at you. A cross join merges two tables on every record in a geometric fashion – every record of one table is combined with every record from the other table. Two tables of 100 records each in a cross join will create a table of 10,000 (100 times 100) records. Imagine the result set with tables of 20,000 or 30,000 records!

Inner join
This is the default join method if nothing else is specified. An inner join essentially finds the intersection between the two tables. The join takes all the records from table A and finds the matching record(s) from table B. If no match is found, the record from A is not included in the results. If multiple results are found in B that match the predicate then one row will be returned for each (the values from A will be repeated).

Special care must be taken when joining tables on columns that can be NULL since NULL values will never match each other

Left outer join
A left outer join is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" table (A). This means that if the ON clause matches 0 records in B, a row in the result will still be returned—but with NULL values for each column from B.

Right outer join
A right outer join is much like a left outer join, except that the tables are reversed. Every record from the right side, B, will be returned, and NULL values will be returned for those that have no matching record in A.

Full outer join
Full outer joins are the combination of left and right outer joins. These joins will show records from both tables, and fill in NULLs for missing matches on either side.