即使记录不存在也返回行 [英] Return rows even if record does not exist 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的查询:

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')

这将返回:

[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的记录.但是，如何加入不存在的记录?还是这根本不可能?有哪些替代方案?

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?

推荐答案

尽管正确地使用了外部联接，然后通过在WHERE子句中包含该表中的列，然后将结果集限制为Table_2中存在值的情况.如果要记录样品编号为ABC或表_2中没有记录的记录，则需要执行以下操作:

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:

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')

或者，您可以在连接到Table_2时过滤掉Table_2的结果(在这种情况下，它的读法会更清晰一些):

Alternatively, you can filter the results from Table_2 when joining to it (which, in this case, reads a little more cleanly):

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] AND Table_2.[Sample No] = 'ABC'
WHERE (Table_1.[Group No] = '123') 

那应该完成同样的事情.这里重要的一点是，WHERE子句会过滤连接表的结果.如果您正在使用外部联接，但要在外部联接的表上进行过滤，则必须处理在联接的另一端不存在任何记录的情况.

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.

这篇关于即使记录不存在也返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！