Simply SQL: The FROM Clause

Outer Joins

Next, we’ll look at outer joins. Outer joins differ from inner joins in that unmatched rows can also be returned. As a result, most people say that an outer join includes rows that don’t match the join condition. This is correct, but might be a bit misleading, because outer joins do include all rows that match. Typical outer joins have many rows that match, and only a few that don’t.

There are three different types of outer join: left, right, and full. We’ll start with the left outer join.

The Left Outer Join

For a left outer join, all rows from the left table are returned, regardless of whether they have a matching row in the right table. Which one’s the left table, and which one’s the right table? These are simply the tables mentioned to the left and to the right of the OUTER JOIN keywords. For example, in the following statement, A is the left table and B is the right table and a left outer join is specified in the FROM clause:

SELECT  
 a, b  
FROM  
 A LEFT OUTER JOIN B  
   ON a=b

The figure below shows the results of this join. Remember—left outer joins return all rows from the left table, together with matching rows of the right table, if any.

Notice that all values from A are returned. This is because A is the left table. In the case of 107, which did not have a match in B, we see that it is indeed included in the results, but there is no value in that particular result row from B. For the time being, it’s okay just to think of the value from B as missing—which, of course, for 107 it is.

The Right Outer Join

For a right outer join, all rows from the right table are returned, regardless of whether they have a match in the left table. In other words, a right outer join works exactly like a left outer join, except that all the rows of the right table are returned instead:

SELECT  
a, b  
FROM  
 A RIGHT OUTER JOIN B  
   ON a=b

In the example above, A is still the left table and B is still the right table, because that’s where they are mentioned in relation to the OUTER JOIN keywords. Consequently, the result of the join contains all the rows from table B, together with matching rows of table A, if any, as shown in Figure 3.4, “A RIGHT OUTER JOIN B”.

The right outer join is the reverse of the left outer join. With the same tables in the same positions—A as the left table and B as the right table—the results of the right outer join are very different from those of a left outer join. This time, all values from B are returned. In the case of 101 and 108, which did not have a match in A, they are indeed included in the results, but there is no value in their particular result rows from A. Again, those values from A are missing, but the row is still returned.

The Full Outer Join

For a full outer join, all rows from both tables are returned, regardless of whether they have a match in the other table. In other words, a full outer join works just like left and right outer joins, except this time all the rows of both tables are returned. Consider this example:

SELECT  
 a, b  
FROM  
 A FULL OUTER JOIN B  
   ON a=b

Once again, A is the left table and B is the right table, although this time it doesn’t really matter. Full outer joins return all rows from both tables, together with matching rows of the other table, if any, as shown below.

The full outer join is a combination of left and right outer joins. (More technically, if you remember your set theory from mathematics at school, it's the union of the results from the left and right outer joins.) Matching rows are—of course—included, but rows that have no match from either table, are also included.

The Difference between Inner and Outer Joins

The results of an outer join will always equal the results of the corresponding inner join between the two tables plus some unmatched rows from either the left table, the right table, or both—depending on whether it is a left, right, or full outer join, respectively.

Thus the difference between a left outer join and a right outer join is simply the difference between whether the left table’s rows are all returned, with or without matching rows from the right table, or whether the right table’s rows are all returned, with or without matching rows from the left table.

A full outer join, meanwhile, will always include the results from both left and right outer joins.

The Cross Join

For a cross join, every row from both tables is returned, joined to every row of the other table, regardless of whether they match. The distinctive feature of a cross join is that it has no ON clause—as you can see in the following query:

SELECT  
 a, b  
FROM  
 A CROSS JOIN B

Cross joins can be very useful but are exceedingly rare. Their purpose is to produce a tabular structure containing rows which represent all possible combinations of two sets of values (in our example, columns from two tables) as shown in Figure 3.6, “A CROSS JOIN B”; this can be useful in generating test data or looking for missing values.

Old-Style Joins

There’s another type of join, which has a comma-separated list of tables in the FROM clause, with the necessary join conditions in the WHERE clause; this type of join is sometimes called the "old-style" join, or "comma list" join, or "WHERE clause" join. For example, for the A and B tables, it would look like this:

SELECT  
 a, b  
FROM  
 A, B  
WHERE  
 a=b

These old-style joins can only ever be inner joins; the other join types are only possible with very proprietary and confusing syntax, which the database system vendors themselves caution is deprecated. Compare this with the recommended syntax for an INNER JOIN:

SELECT  
 a, b  
FROM  
 A INNER JOIN B  
   ON a=b

You may see these old-style joins in the wild but I’d caution you against writing them yourself. Always use JOIN syntax.

To recap our quick survey of joins, there are three basic types of join and a total of five different variations:

• inner join
• left outer join, right outer join, and full outer join
• cross join

Now for some more realistic examples.