Mengapa gabung kiri saya tidak menghasilkan nol?

ubah join items i... menjadi LEFT join items i... dan kueri Anda akan berfungsi seperti yang Anda harapkan.

EDIT
Anda tidak dapat memfilter tabel LEFT JOIN dalam klausa Where kecuali Anda memperhitungkan nol, karena gabungan kiri memungkinkan kolom tersebut memiliki nilai atau menjadi nol ketika tidak ada baris yang cocok:

and i.siteid = 132 akan membuang baris mana pun yang memiliki NULL i.siteid, jika tidak ada. Pindahkan ini ke AKTIF:

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

atau buat WHERE menangani NULL:

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

EDIT berdasarkan editan OP 3

SET NOCOUNT ON
DECLARE @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

Saya tidak 100% yakin apa yang diinginkan OP, ini akan mengembalikan semua info yang dapat digabungkan ketika siteid=132 seperti yang diberikan dalam pertanyaan

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

KELUARAN:

categorytitle                  subcategorytitle               itemtitle
------------------------------ ------------------------------ ------------------------------
Cat AAA                        SubCat AAA B                   Item C
NULL                           NULL                           Item F
Cat AAA                        SubCat AAA B                   Item G

(3 row(s) affected)

Ini akan mencantumkan semua kategori, meskipun tidak ada yang cocok dengan 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 AllItems
UNION
SELECT
    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 NULL
ORDER BY categorytitle,subcategorytitle

KELUARAN:

categorytitle                  subcategorytitle               itemtitle
------------------------------ ------------------------------ ------------------------------
NULL                           NULL                           Item F
Cat AAA                        SubCat AAA A                   NULL
Cat AAA                        SubCat AAA B                   Item C
Cat AAA                        SubCat AAA B                   Item G
Cat AAA                        SubCat AAA C                   NULL
Cat BBB                        SubCat BBB A                   NULL
Cat CCC                        NULL                           NULL

(7 row(s) affected)

Kriteria "WHERE" Anda di i.siteid berarti harus ada baris "item" di output. Anda perlu menulis (i.siteid adalah null atau i.siteid = 132) atau memasukkan "i.siteid = 132" ke dalam klausa "ON" - sesuatu yang akan berfungsi untuk bergabung dengan kategori item juga:

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

Mungkin gabung ini juga harus gabung kiri?

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

EDIT:

Sekarang Anda hanya memilih id situs yang ada di klausa Where:

i.siteid = 132

Itu harus mengizinkan nilai nol, coba sesuatu seperti ini:

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

atau Anda dapat memindahkan i.siteid = 132 kembali ke kondisi bergabung

where (ic.isactive = 1 or ic.isactive is null) and i.siteid = 132

Memiliki i.siteID = 132 di klausa Where Anda pada dasarnya membatalkan penggabungan kiri pada item.

Upaya kedua, sepertinya saya mengerti masalah Anda sekarang. Jika saya memahaminya dengan benar maka yang terjadi adalah Anda mendapatkan dua jenis NULL di SiteID dalam kasus Anda (kasus ketika Anda melihat hasil 10.000e, tetapi itu masih di jalur yang benar).

Ada NULL yang berasal dari gabungan kiri dan yang berasal dari siteid tabel Item sebenarnya. Anda ingin yang berasal dari kiri bergabung, tetapi tidak ingin yang berasal dari data.

Ini adalah kesalahan yang sangat umum pada gabungan luar saat menguji keberadaan baris yang cocok.

Ingatlah bahwa jika Anda menginginkan baris yang tidak cocok, Anda harus selalu menguji NULL hanya pada kolom yang didefinisikan sebagai NOT NULL (kunci utama dari tabel luar adalah kandidat alami di sini). Kalau tidak, Anda tidak dapat membedakan antara baris-baris yang NULL karena gabungan KIRI dan baris-baris yang akan menjadi NULL meskipun itu adalah gabungan INNER

Setidaknya ada dua cara untuk melakukan hal ini: a) gabung kiri pada subkueri yang akan memfilter baris dengan siteid is null sebelum gabung kiri dimulai
b) menulis ulang kriteria (dengan asumsi ItemID diperlukan dalam Items) untuk mengatakan

select      
    c.title as categorytitle,
    s.title as subcategorytitle,
    i.title as itemtitle
from categories c
join subcategories s on c.categoryid = s.categoryid
left join itemcategories ic on s.subcategoryid = ic.subcategoryid 
left join items i on ic.itemid = i.itemid
where (ic.isactive = 1 or ic.isactive is null) AND (i.siteid = 132 or i.itemid is null)
order by c.title, s.title

(Saya berasumsi bahwa kueri hingga tabel gabung ke item memberikan apa yang Anda harapkan).

Jika itemid diperlukan dalam item maka kondisi di atas mengatakan - baris dengan siteid 132 atau baris yang benar-benar berasal dari gabungan kiri yang tidak cocok (perhatikan bahwa kondisi pada i.itemid adalah null dan bukan i.siteid adalah null).

Coba ubah juga gabungan Item menjadi gabungan kiri.