Since I saw a request, I'll post a short overview of the outer join vs inner join. There is nothing really complex about them and you can read about them in a million SQL books, but I'll take a stab at it anyway.

The most common scenario where outer joins are needed is when you have a "one-to-many" relationship between two tables. Take "authors" and "titleauthor" in the pubs database. You can join them on au_id column to figure out all titles that any particular author has:

select * from authors a join titleauthor t on a.au_id = t.au_id

This query returns 25 rows. Some of the authors will be repeated - those that have more than one title in the database. By the way, you'll get exactly the same result from the following query:

select * from authors a, titleauthor t where a.au_id = t.au_id

This is because the "comma" in the FROM clause is considered a "cross product" operator, that will give you all possible combinations of "authors" rows and "titleauthor" rows. Filter those where au_id is the same on two sides - and you get your join.

There is a slight problem with it - those authors that did not get to writing any titles yet will not show up in the results. It may be OK, or it may be not - depends on the requirements. If you'd like to see the outers without titles, you need to change your query somehow. This is where outer join comes in. If you write:

select * from authors a left outer join titleauthor t on a.au_id = t.au_id

you'll see 3 more rows returned. They correspond to authors that don't have corresponding rows in the titleauthor table. For each author, the "titleauthor" part of the result will contain fake rows that consist of NULLs in all columns.

Outer joins have the following interesting properties:

all rows from "outer" side of the join will be present in the result, even those that don't satisfy the "ON" condition.

• columns coming from the inner side will always be nullable, even those that are declared as "not nullable" in the base table. This is needed to accomodate "fake" NULL values.
• outer joins cannot be expressed using comma and WHERE clause (as opposed to inner joins).
• as opposed to inner joins, in case of multiple-table joins, it matters for the result in what order the joins are performed. For inner joins, you may change the order of execution freely.

Note that SQL Server still supports an older (non-ANSI) notation for expressing outer joins that uses operators "*=" and "=*" to designate the outer side with a star. Unfortunately, this notation is ambiguous, because it does not specify the join order, and does not fully specify what conditions are part of the ON clause and what conditions are in the WHERE clause. Handling these "old-style" outer join operators puts a serious maintenance burden on the product, so beware that they will be deprecated very soon.