SQL Outer Join with a condition on the outer table

Here is a situation that confronts me frequently. I need to create an outer join that requires a condition on one of the columns in the outer table. Using the conventional syntax, meaning the join conditions are in the WHERE clause, causes the outer join to become an inner join and return only the rows that match the condition on the outer table.

The example below shows the bad SQL, and two solutions for it:

/* this SQL is bad: the PreferredFlag condition turns the 
     LEFT JOIN into an INNER JOIN
     which returns only those tblPerson records 
     matched to a tblPersonAddress having the
     PreferredFlag set to 1
*/
FROM  tblPerson AS p,
      tblPersonAddress AS a
WHERE p.PersonKey = a.PersonKey (+)
  AND a.PreferredFlag = 1
  AND p.PersonKey = 1234

/* my old solution: a nested subquery */
FROM  tblPerson AS p,
      (SELECT PersonKey,  
         FROM tblPersonAddress 
         WHERE PreferredFlag = 1) AS a
WHERE p.PersonKey = a.PersonKey (+)
  AND p.PersonKey = 1234
 
/* using ANSI 92: the PreferredFlag condition 
   becomes part of the JOIN, 
   not part of the WHERE clause */
FROM  tblPerson AS p
      LEFT OUTER JOIN tblPersonAddress AS a
        ON p.[PersonKey] = a.[PersonKey] 
           AND a.[PreferredFlag] = 1
WHERE p.PersonKey = 1234

Except and Intersect

Here's a powerful feature of SQL Server 2005:
EXCEPT and INTERSECT are operands that return distinct results from automatic joins on every column in two selects.

SELECT ProductID
FROM TableA
EXCEPT
SELECT ProductID
FROM TableB

is equivalent to

SELECT ProductID
FROM TableA
LEFT OUTER JOIN TableB ON TableA.ProductID = TableB.ProductID
WHERE TableB.ProductID IS NULL

the advantage of EXCEPT would grow with more columns to compare.

INTERSECT returns the equivalent of the distinct results of an inner join on every column selected on both sides of the INTERSECT operand.

Verbatim String Literal

As a novice C# user, I was recently happy to discover verbatim string literals, which are indicated by the presence of the @-symbol just to the left of
Here's the skinny; when you want to have a large block of text (SQL or XML markup, for example) in your C# code, you can have it formatted exactly the way it is formatted in your SQL or XML editor. This allows for excellent readability and rapid switching between your respective editors and your C# code page. Here is an example; the below SQL equally well in a C# page and an SSMS query editor. It has really sped up my debugging of in-line SQL. As far as I know, VB.NET doesn't have an equivalent feature.

     DataSet ds = MyClass.GetData(@"
       SELECT 0 AS Field1, '<>' AS Field2
       UNION ALL
       SELECT
               Field1,
               Field2
       FROM
               A_Table
       WHERE
               [Year] = udfFiscalYear()
       ORDER BY
               Field2
     ", "A_Table");