Could I get some help with JOINs?

I see a lot of questions out there that suggest a lack of basic understanding of how tables in a relational database, well, relate to each other. Let's say we have the following schema of authors and the stories they have written: 
 

USE TempDB  GO    SET NOCOUNT ON    CREATE TABLE dbo.Authors  (      AuthorID INT PRIMARY KEY,      FirstName VARCHAR(15) NOT NULL,      LastName VARCHAR(15) NOT NULL  )  GO    CREATE TABLE dbo.Stories  (      StoryID INT PRIMARY KEY,      AuthorID INT FOREIGN KEY          REFERENCES dbo.Authors,      dt SMALLDATETIME NOT NULL,      Title VARCHAR(32) NOT NULL  )  GO    INSERT dbo.Authors      SELECT 1, 'Aaron', 'Bertrand'      UNION SELECT 2, 'Jimmy', 'Hoffa'      UNION SELECT 3, 'Tommy', 'Hilfiger'      UNION SELECT 4, 'Brett', 'Hull'  GO    INSERT dbo.Stories      SELECT 100, 1, '20050227', 'SQL Server JOINs 101'      UNION SELECT 110, 2, '19740201', 'How to Hide Forever'      UNION SELECT 120, 4, '20050101', 'Slap Shot Fever'      UNION SELECT 130, 4, '20050219', 'What happened to hockey?'      UNION SELECT 140, 1, GETDATE(), 'How much can I type?'      UNION SELECT 150, NULL, '20041231', 'Anonymity Rules'  GO

 
Notice that we have at least one author with no stories, and we also have at least one story with no author. 
 
First, let's handle the basic INNER JOIN. This returns every row from the "left" table with at least one matching row in the "right" table. (By "left" I mean the table mentioned on the left side of the JOIN expression.) 
 

SELECT           a.FirstName, a.LastName, s.Title, s.dt      FROM          Authors a      INNER JOIN          Stories s      ON          a.AuthorID = s.AuthorID      ORDER BY          a.LastName, a.FirstName

 
This returns all of the authors and each of their stories, one row per story. Notice that multiple authors have multiple rows in the result, and that there are no rows for any author without a story, or any story without an author. 
 
A common question is, "Should I use this ANSI-style JOIN, or should I use the old style with the JOIN criteria in the WHERE clause?" Here is the old-style syntax: 
 

SELECT          a.FirstName, a.LastName, s.Title, s.dt      FROM          Authors a, Stories s      WHERE          a.AuthorID = s.AuthorID      ORDER BY          a.LastName, a.FirstName

 
You will note that this produces the exact same result as the above query. Personally, I prefer the ANSI-style JOIN because it will last longer (the old style is deprecated) and because keeping the JOIN criteria separate from the filter criteria is much more intuitive and readable, IMHO. And while you should see no difference in the performance or in the results from INNER JOIN queries using either syntax, this is not always true for OUTER JOINs (I will provide examples later). So, since you should definitely use ANSI-style JOIN syntax for all OUTER JOINs, it would be consistent to use this syntax throughout all of your queries. 
 
Another common request I see for this kind of schema is, "How do I return one row per author, including the name of a story they wrote?" To which I always respond, "Which story do you want?" See, SQL Server doesn't know which story you want, so you have to be specific. There is no ANY() function in SQL Server, so you have to tell the engine whether you want the latest story they wrote, the first story title alphabetically, or something else that can be quantified — rather than expecting an arbitrary / non-deterministic result. And each of these results can be derived in a slightly different way. You must also account for cases where an author has not yet written any stories; do you include such authors in the result? If so, what do you want in the story column? 
 
This query list authors and their first title alphabetically: 
 

SELECT          a.FirstName, a.LastName, MIN(s.title)      FROM          dbo.Authors a      INNER JOIN          dbo.Stories s      ON          a.AuthorID = s.AuthorID      GROUP BY          a.firstName, a.LastName      ORDER BY          a.LastName, a.FirstName

 
This query lists authors and their latest story, including dt (this assumes that authorID + dt is unique). 
 

SELECT          a.FirstName, a.LastName, s.title, s.dt      FROM          dbo.Authors a      INNER JOIN       (          SELECT              AuthorID, dt = MAX(dt)          FROM              dbo.Stories          GROUP BY               AuthorID      ) sub      ON          a.AuthorID = sub.AuthorID      INNER JOIN          dbo.Stories s      ON          s.AuthorID = sub.AuthorID          AND s.dt = sub.dt      ORDER BY          a.LastName, a.FirstName

 
What if you want to include authors that don't have stories? You'll need to replace the INNER JOINs with LEFT OUTER JOINs so that story-less authors aren't left out: 
 

SELECT          a.FirstName, a.LastName, s.title, s.dt      FROM          dbo.Authors a      LEFT OUTER JOIN       (          SELECT              AuthorID, dt = MAX(dt)          FROM              dbo.Stories          GROUP BY              AuthorID      ) sub      ON          a.AuthorID = sub.AuthorID      LEFT OUTER JOIN          dbo.Stories s      ON          s.AuthorID = sub.AuthorID          AND s.dt = sub.dt      ORDER BY          a.LastName, a.FirstName

 
What if you want to return all stories, and their authors (if available)? 
 

SELECT          a.FirstName, a.LastName, s.title, s.dt      FROM          dbo.Authors a      RIGHT OUTER JOIN          dbo.Stories s      ON          a.AuthorID = s.AuthorID      ORDER BY          a.LastName, a.FirstName

 
Note that the only difference between a LEFT and RIGHT JOIN is the order of the tables that are being JOINed. Basically, the table on the mentioned side of the JOIN expression (e.g. the table mentioned first in a LEFT JOIN and last in a RIGHT JOIN) is the table that is going to produce real data for all rows, and the other table will be populated with NULLs when no matching row is found. 
 
The other types of JOINs are FULL OUTER and CROSS. 
 
FULL OUTER returns the every row from the left table *and* every row from the right table. If no match exists on one side, that side contains NULL values in all columns. To put it simply, a FULL OUTER JOIN is the UNION of a LEFT JOIN and RIGHT JOIN. 
 

SELECT          a.FirstName, a.LastName, s.title, s.dt      FROM          dbo.Authors a      FULL OUTER JOIN          dbo.Stories s      ON          a.AuthorID = s.AuthorID      ORDER BY          a.LastName, a.FirstName

 
A CROSS JOIN takes every row in the left table, and matches it with every row in the right table. A CROSS JOIN is generally referred to as the Cartesian product. 
 

SELECT          a.FirstName, a.LastName, s.title, s.dt      FROM          dbo.Authors a      CROSS JOIN          dbo.Stories s        -- note that no 'ON' clause is required        ORDER BY          a.LastName, a.FirstName

 
I don't know of many practical uses for this type of query, in terms of actually looking at the results, except a quick way to generate many menial rows from two tables and not trying to form any relationship between the two sides. 
 
I've probably left out quite a bit of information, but I'm tired. Please feel free to leave feedback about any errors above or about any JOIN topics you think should be included here. And don't forget to clean up: 
 

DROP TABLE dbo.Stories, dbo.Authors