I am linking two tables with left outer join. I need to keep all the records in the left table even though there are no matches in the right one. I thought that's the purpose of using left outer join. Here is the example: tab1 ID=ranges from 01 to 10 and tab2 ID ranges from 01 to 10 with missing IDs. select ID, Namefrom tab1 t1left outer join tab2 t2 on t1.id=t2.id

results:t1.id,t1.name01,A02,B03,C06,F08,I

since t2.ids 4,5,7,9,,10 are missing, these records are excluded from the result set. Any help will be greatly appreciated.

helal.mobasher 13209 (11/27/2012)I am linking two tables with left outer join. I need to keep all the records in the left table even though there are no matches in the right one. I thought that's the purpose of using left outer join. Here is the example: tab1 ID=ranges from 01 to 10 and tab2 ID ranges from 01 to 10 with missing IDs. select ID, Namefrom tab1 t1left outer join tab2 t2 on t1.id=t2.id

results:t1.id,t1.name01,A02,B03,C06,F08,I

since t2.ids 4,5,7,9,,10 are missing, these records are excluded from the result set. Any help will be greatly appreciated.

Are you using a where clause based on tab2? Where clause is applied after the left outer join. This means that the left outer join will return all records from tab1 with non joining records in tab2 having null on all fields. When you put a where clause on a tab2 field, you have to account for the nulls.When doing this type of join and filter, I usually put the all filter conditions for tab2 during the on clause. Something like "on t1.id = t2.id AND t2.name = 'A'".