SQL 为什么我的左连接不返回空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2479650/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is my left join not returning nulls?
提问by jimj
In sql server 2008, I have the following query:
在 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
I am trying to get items in their subcategories, but I still want to return a record if there are no items in the category or subcategory. Subcategories that have no items are never returned. What am I doing wrong?
我正在尝试获取其子类别中的项目，但如果类别或子类别中没有项目，我仍然想返回一条记录。永远不会返回没有项目的子类别。我究竟做错了什么？
Thank you
谢谢
EDIT
编辑
Modified query with a second left join and where clause, but it's still not returning nulls. :/
使用第二个左连接和 where 子句修改查询，但它仍然不返回空值。：/
EDIT 2
编辑 2
Moved siteid to item left join. When I do this I get way more records than expected. Some items have a null siteid and I only want to included them when they have a specific id.
将站点 ID 移至项目左连接。当我这样做时，我得到的记录比预期的多。有些项目的站点 ID 为空，我只想在它们具有特定 ID 时将它们包含在内。
EDIT 3
编辑 3
Table structure:
表结构：
Categories Table
-------
CategoryID
Title
SubCategories Table
-------
SubCategoryID
CategoryID
Title
ItemCategories Table
-------
ItemCategoryID
ItemID
SubCategoryID
IsActive
Items Table
--------
ItemID
Title
SiteID
回答by KM.
change join items i... to LEFT join items i... and your query should work as you expect.
将join items i...更改为LEFT join items i... 并且您的查询应该按您的预期工作。
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:
编辑
除非您考虑空值，否则您不能在 where 子句中过滤 LEFT JOIN 表，因为左连接允许这些列具有值或在没有行匹配时为空：
and i.siteid = 132will throw away any of your rows that have a NULL i.siteid, where none existed. Move this to the ON:
and i.siteid = 132将丢弃您的任何具有 NULL 的行i.siteid，其中不存在。将其移至 ON：
left join items i on ic.itemid = i.itemid and i.siteid = 132
left join items i on ic.itemid = i.itemid and i.siteid = 132
or make the WHERE handle NULLs:
或使 WHERE 处理 NULL：
WHERE ... AND (i.siteid = 132 OR i.siteid IS NULL)
WHERE ... AND (i.siteid = 132 OR i.siteid IS NULL)
EDITbased on OP's edit 3
编辑基于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
I'm not 100% sure what the OP is after, this will return all info that can be joined when the siteid=132as given in the question
我不是 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
OUTPUT:
输出：
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)
This will list all categories, even if there is no match to the siteid=132
这将列出所有类别，即使没有匹配 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
OUTPUT:
输出：
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)
回答by araqnid
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:
i.siteid 上的“WHERE”条件意味着输出中必须有“items”行。您需要编写（i.siteid 为 null 或 i.siteid = 132）或将“i.siteid = 132”放入“ON”子句中 - 这也适用于 itemcategories join：
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
回答by Andrew Bezzub
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:
现在您只在 where 子句中选择现有站点 ID：
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 = 132back to the join condition
或者你可以i.siteid = 132回到加入条件
回答by JupiterP5
where (ic.isactive = 1 or ic.isactive is null) and i.siteid = 132
Having the i.siteID = 132 in your where clause essentially nullifies doing a left join on items.
在您的 where 子句中使用 i.siteID = 132 基本上会使对项目进行左连接无效。
回答by Unreason
2nd attempt, I think I got your problem now. If I understand correctly then what happens is that you end up with two kind of NULLs in SiteID in your case (the case when you see 10,000e results, but that is still on the right track).
第二次尝试，我想我现在遇到了你的问题。如果我理解正确，那么在您的情况下，您最终会在 SiteID 中得到两种 NULL（当您看到 10,000e 结果时的情况，但这仍然在正确的轨道上）。
There are NULLs that come from left join and the ones that come from actual Item table siteid. You want to have the ones that come from left join, but don't want the ones from data.
有来自左连接的 NULL 和来自实际项目表 siteid 的 NULL。您想要来自左连接的那些，但不想要来自数据的那些。
This is a very common mistake with outer joins when testing for existence of matched rows.
在测试是否存在匹配的行时，这是外连接的一个非常常见的错误。
Keep in mind that you if you want unmatched rows that should always test for NULL only on the columns which are defined as NOT NULL (primary key from the outer table is a natural candidate here). Otherwise you can not distinguish between the rows that are NULL due to LEFT join and rows which would be NULL even if it was an INNER join
请记住，如果您想要不匹配的行，则应始终仅在定义为 NOT NULL 的列上测试 NULL（来自外部表的主键是此处的自然候选）。否则，您无法区分由于 LEFT 连接而为 NULL 的行和即使是 INNER 连接也会为 NULL 的行
At least two ways to go about this:
a) left join on subquery that will filter out rows with siteid is null before the left join kicks in
b) rewrite criteria (assuming ItemID is required in Items) to say
至少有两种方法可以解决这个问题：a) 子查询上的左连接将在左连接开始之前过滤掉 siteid 为 null 的行
b) 重写标准（假设 ItemID 在 Items 中是必需的）说
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
(I am assuming that the query up to the join to items table was giving you what you expected).
（我假设连接到 items 表的查询为您提供了您所期望的）。
If itemid is required in items then the above condition says - rows with siteid 132 or rows that really come from unmatched left join (do note that the condition is on i.itemid is null and not i.siteid is null).
如果项目中需要 itemid，则上述条件表示 - siteid 为 132 的行或真正来自不匹配左连接的行（请注意，条件是 i.itemid 为空，而不是 i.siteid 为空）。
回答by Gratzy
Try changing the join of Items to a left join as well.
尝试将 Items 的连接也更改为左连接。
