FULL OUTER JOIN Not Working

Problem

FULL OUTER JOIN not returning all records from right table when left table has a Search Condition in the WHERE clause.

-- Full outer join with where clause at bottom

-- Returns all records from MyTable1 where Category is 23.

-- Does not return all records from MyTable2. Only matches with MyTable1.

SELECT t1.[Code],t2.[Code]

FROM [MyTable1] t1

FULL OUTER JOIN [MyTable2] t2

      ON t2.[Code] = t1.[Code]

WHERE t1.[Category] = 23

Solution

Use a Derived Table for the left table. The Search Condition is moved from the WHERE clause to the Derived Table query.

-- Full outer join with derived table, no where clause at bottom

-- Returns all records from MyTable1 where Category is 23.

-- Returns all records from MyTable2.

SELECT t1.[Code],t2.[Code]

FROM ( SELECT * FROM [MyTable1] WHERE [Category] = 23 ) t1

FULL OUTER JOIN [MyTable2] t2

      ON t2.[Code] = t1.[Code]

References

See also SQL Join Types