Tres Tres - 2 months ago 5
SQL Question

SQL Server 2008 Preserve rows from table with non-matching values from another table

Suppose I have 2 tables:

FRUITS RECIPE
----------- -----------------
id name ver id1 id2
----------- -----------------
1 apple 1 1 1
2 banana 2 null 3
3 orange 3 3 3
4 peach 4 4 2
5 1 null
6 null null


In order to return the names for id1 and id2 values I tried:

SELECT ver, id1, F1.name, id2, F2.name
FROM RECIPE INNER JOIN FRUITS AS F1 ON id1 = F1.name
INNER JOIN FRUITS AS F2 ON id2 = F2.name


which returns:

------------------------------------
ver id1 name id2 name
------------------------------------
1 1 apple 1 apple
3 3 orange 3 orange
4 4 peach 2 banana


I want the result set to include all RECIPE rows including nulls as shown:

------------------------------------
ver id1 name id2 name
------------------------------------
1 1 apple 1 apple
2 null null 3 orange
3 3 orange 3 orange
4 4 peach 2 banana
5 1 apple null null
6 null null null null


Thanks for all your help...

Answer

Use LEFT JOIN instead of INNER JOIN to preserve entire output generated by RECIPE table with additional information retrieved from FRUITS:

SELECT ver, id1, F1.name, id2, F2.name
FROM RECIPE 
LEFT JOIN FRUITS AS F1 ON id1 = F1.name
LEFT JOIN FRUITS AS F2 ON id2 = F2.name

Quote:

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.