sql - 为什么我的左连接不返回空值？

Question

在 sql server 2008 中，我有以下查询:

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 and i.siteid = 132
where (ic.isactive = 1 or ic.isactive is null)
order by c.title, s.title

我正在尝试获取子类别中的项目，但如果类别或子类别中没有项目，我仍然想返回记录。没有项目的子类别永远不会返回。我做错了什么？

谢谢

编辑

使用第二个左连接和 where 子句修改了查询，但它仍然不返回空值。 :/

编辑2

将 siteid 移至项目左连接。当我这样做时，我得到的记录比预期多得多。有些项目的 siteid 为空，我只想在它们具有特定 id 时包含它们。

编辑3

表结构:

Categories Table 
-------
CategoryID
Title

SubCategories Table
-------
SubCategoryID
CategoryID
Title

ItemCategories Table
-------
ItemCategoryID
ItemID
SubCategoryID
IsActive

Items Table 
--------
ItemID
Title
SiteID

Answer 1

将join items i...更改为LEFT join items i...，您的查询应该按您的预期工作。

编辑
除非考虑空值，否则无法在 where 子句中过滤 LEFT JOIN 表，因为左联接允许这些列在没有行匹配时具有值或为空:

和 i.siteid = 132 将丢弃任何具有 NULL i.siteid 的行，而这些行不存在。将其移至 ON:

在 ic.itemid = i.itemid 和 i.siteid = 132 上左连接项目 i

或者让 WHERE 处理 NULL:

其中 ... AND(i.siteid = 132 或 i.siteid 为 NULL)

编辑基于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

我不能 100% 确定 OP 的目的，这将返回问题中给出的 siteid=132 时可以加入的所有信息

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

输出:

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)

这将列出所有类别，即使没有与 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

输出:

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)