即使记录不存在也返回行 LEFT OUTER JOIN
我有下面 2 个表：
Table_1
[Group No] [Test No] [Description]
123 1 [First Test]
123 2 [Second Test]
123 3 [Third Test]
Table_2
[Sample No] [Test No] [Result Description]
ABC 1 [Some More Result]
ABC 3 [Some Result]
DEF 1 [A Result]
DEF 2 [Results More]
DEF 3 [Bad Results]
这是我的查询：
SELECT Table_1.[Group No], Table_1.[Test No], Table_1.Description, Table_2.[Result Description]
FROM Table_1
LEFT OUTER JOIN Table_2 ON Table_1.[Test No] = Table_2.[Test No]
WHERE (Table_1.[Group No] = '123') AND (Table_2.[Sample No] = 'ABC')
djacobson 的查询：
SELECT Table_1.[Group No], Table_1.[Test No], Table_1.Description, Table_2.[Result Description]
FROM Table_1
LEFT OUTER JOIN Table_2 ON Table_1.[Test No] = Table_2.[Test No]
WHERE (Table_1.[Group No] = '123')
AND (Table_2.[Sample No] IS NULL OR Table_2.[Sample No] = 'ABC')
返回：
[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 3 [Third Test] [Some Result]
但我真正想要的是：
[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 2 [Second Test] NULL
123 3 [Third Test] [Some Result]
这可能吗？我想返回测试编号 2 的记录。但是，如何加入不存在的记录？或者这根本不可能？有哪些替代方案？
I have 2 tables below:
Table_1
[Group No] [Test No] [Description]
123 1 [First Test]
123 2 [Second Test]
123 3 [Third Test]
Table_2
[Sample No] [Test No] [Result Description]
ABC 1 [Some More Result]
ABC 3 [Some Result]
DEF 1 [A Result]
DEF 2 [Results More]
DEF 3 [Bad Results]
Here's my query:
SELECT Table_1.[Group No], Table_1.[Test No], Table_1.Description, Table_2.[Result Description]
FROM Table_1
LEFT OUTER JOIN Table_2 ON Table_1.[Test No] = Table_2.[Test No]
WHERE (Table_1.[Group No] = '123') AND (Table_2.[Sample No] = 'ABC')
djacobson's query:
SELECT Table_1.[Group No], Table_1.[Test No], Table_1.Description, Table_2.[Result Description]
FROM Table_1
LEFT OUTER JOIN Table_2 ON Table_1.[Test No] = Table_2.[Test No]
WHERE (Table_1.[Group No] = '123')
AND (Table_2.[Sample No] IS NULL OR Table_2.[Sample No] = 'ABC')
This returns:
[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 3 [Third Test] [Some Result]
But what I really want is:
[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 2 [Second Test] NULL
123 3 [Third Test] [Some Result]
Is this possible? I would like to return the record with Test No 2. However, how do I join to a record which is non-existent? Or is this simply not possible? What are the alternatives?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尽管正确使用了外联接，但您还是通过在 WHERE 子句中包含该表中的列，将结果集限制为 Table_2 中存在值的情况。如果您想要样本编号为 ABC 的记录，或者 Table_2 中没有记录，则需要执行以下操作：
或者，您可以在连接到 Table_2 时过滤来自 Table_2 的结果（在本例中，读取的内容会多一点）干净地）：
这应该完成同样的事情。这里重要的一点是 WHERE 子句会过滤连接表的结果。如果您使用外连接但想要过滤外连接表，则必须处理连接远端不存在记录的情况。
Despite correctly using an outer join, you are then restricting the resultset to cases where a value is present in Table_2 by including a column from that table in your WHERE clause. If you want records where the Sample No. is ABC, OR there is no record in Table_2, you need to do this:
Alternatively, you can filter the results from Table_2 when joining to it (which, in this case, reads a little more cleanly):
That should accomplish the same thing. The important takeaway here is that the WHERE clause filters the results of joining your tables. If you're using outer joins but want to filter on the outer-joined tables, you must handle the case where no record exists on the far side of the join.