Return rows even if record does not exist LEFT OUTER JOIN

Question

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?

Answer 1

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

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

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.