Why is my left join not returning nulls?

change join items i... to LEFT join items i... and your query should work as you expect.

EDIT
You can not filter LEFT JOIN tables in the where clause unless you account for nulls, because the left join allows those columns to have a value or be null when no rows matches:

and i.siteid = 132 will throw away any of your rows that have a NULL i.siteid, where none existed. Move this to the ON:

left join items i on ic.itemid = i.itemid and i.siteid = 132

or make the WHERE handle NULLs:

WHERE ... AND (i.siteid = 132 OR i.siteid IS NULL)

EDIT based on OP's edit 3

SET NOCOUNT ONDECLARE @Categories table (CategoryID int,Title varchar(30))INSERT @Categories VALUES (1,'Cat AAA')INSERT @Categories VALUES (2,'Cat BBB')INSERT @Categories VALUES (3,'Cat CCC')DECLARE @SubCategories table (SubCategoryID int,CategoryID int,Title varchar(30))INSERT @SubCategories VALUES (1,1,'SubCat AAA A')INSERT @SubCategories VALUES (2,1,'SubCat AAA B')INSERT @SubCategories VALUES (3,1,'SubCat AAA C')INSERT @SubCategories VALUES (4,2,'SubCat BBB A')DECLARE @ItemCategories table (ItemCategoryID int, ItemID int, SubCategoryID int, IsActive char(1))INSERT @ItemCategories VALUES (1,1,2,'Y')INSERT @ItemCategories VALUES (2,2,2,'Y')INSERT @ItemCategories VALUES (3,3,2,'Y')INSERT @ItemCategories VALUES (4,4,2,'Y')INSERT @ItemCategories VALUES (5,7,2,'Y')DECLARE @Items table (ItemID int, Title varchar(30), SiteID int)INSERT @Items VALUES (1,'Item A',111)INSERT @Items VALUES (2,'Item B',111)INSERT @Items VALUES (3,'Item C',132)INSERT @Items VALUES (4,'Item D',111)INSERT @Items VALUES (5,'Item E',111)INSERT @Items VALUES (6,'Item F',132)INSERT @Items VALUES (7,'Item G',132)SET NOCOUNT OFF

I'm not 100% sure what the OP is after, this will return all info that can be joined when the siteid=132 as given in the question

SELECT    c.title as categorytitle        ,s.title as subcategorytitle        ,i.title as itemtitle        --,i.itemID, ic.SubCategoryID, s.CategoryID    FROM @Items                          i        LEFT OUTER JOIN @ItemCategories ic ON i.ItemID=ic.ItemID        LEFT OUTER JOIN @SubCategories   s ON ic.SubCategoryID=s.SubCategoryID        LEFT OUTER JOIN @Categories      c ON s.CategoryID=c.CategoryID    WHERE i.siteid = 132

OUTPUT:

categorytitle                  subcategorytitle               itemtitle------------------------------ ------------------------------ ------------------------------Cat AAA                        SubCat AAA B                   Item CNULL                           NULL                           Item FCat AAA                        SubCat AAA B                   Item G(3 row(s) affected)

This will list all categories, even if there is no match to the siteid=132

;WITH AllItems AS(SELECT    s.CategoryID, ic.SubCategoryID, ItemCategoryID, i.ItemID        ,c.title AS categorytitle, s.title as subcategorytitle, i.title as itemtitle    FROM @Items                          i        LEFT OUTER JOIN @ItemCategories ic ON i.ItemID=ic.ItemID        LEFT OUTER JOIN @SubCategories   s ON ic.SubCategoryID=s.SubCategoryID        LEFT OUTER JOIN @Categories      c ON s.CategoryID=c.CategoryID    WHERE i.siteid = 132)SELECT    categorytitle, subcategorytitle,itemtitle    FROM AllItemsUNIONSELECT    c.Title, s.Title, null    FROM @Categories                     c        LEFT OUTER JOIN @SubCategories   s ON c.CategoryID=s.CategoryID        LEFT OUTER JOIN @ItemCategories ic ON s.SubCategoryID=ic.SubCategoryID        LEFT OUTER JOIN AllItems         i ON c.CategoryID=i.CategoryID AND  s.SubCategoryID=i.SubCategoryID    WHERE i.ItemID IS NULLORDER BY categorytitle,subcategorytitle

OUTPUT:

categorytitle                  subcategorytitle               itemtitle------------------------------ ------------------------------ ------------------------------NULL                           NULL                           Item FCat AAA                        SubCat AAA A                   NULLCat AAA                        SubCat AAA B                   Item CCat AAA                        SubCat AAA B                   Item GCat AAA                        SubCat AAA C                   NULLCat BBB                        SubCat BBB A                   NULLCat CCC                        NULL                           NULL(7 row(s) affected)

Your "WHERE" criteria on i.siteid means that there has to be an "items" row in the output. you need to write (i.siteid is null or i.siteid = 132) or put the "i.siteid = 132" into the "ON" clause- something that will work for the itemcategories join too:

select          c.title as categorytitle,    s.title as subcategorytitle,    i.title as itemtitlefrom categories cjoin subcategories s on c.categoryid = s.categoryidleft join itemcategories ic on s.subcategoryid = ic.subcategoryid and ic.isactive = 1left join items i on ic.itemid = i.itemid and i.siteid = 132order by c.title, s.title

Maybe this join also should be a left join?

join items i on ic.itemid = i.itemid and i.siteid = 132

EDIT:

Now you are selecting only existing site ids in the where clause:

i.siteid = 132

It should allow null values, try something like this:

(i.siteid = 132 or i.siteid is null)

or you could move i.siteid = 132 back to the join condition