How to implement LEFT OUTER JOIN?

What is LEFT OUTER JOIN? Where we have to use LEFT OUTER JOIN? Show a sample sql for LEFT OUTER JOIN?

Have a look at the following two tables. EMP TABLE EMP_ID EMP_NAME ------ -------- 23 Raja 24 Roja 25 Pooja COMPLAINTS TABLE EMP_ID COMPLAINT ------ --------- 24 Salary Not Received Now, our objective is we have to list out all the Staff Names along with their complaints,if any. =================================================== WRONG APPROACH =================================================== Select e.emp_name,c.complaint from emp e, complaints c where e.emp_id=c.emp_id The Result for the above sql will be like this: EMP_NAME COMPLAINT -------- --------- Raja Salary Not Received BUT THIS IS NOT THE RESULT WE EXPECTED. We need the output like this: EMP_NAME COMPLAINT -------- --------- Raja Roja Salary Not Received Pooja Carefully observe the above data. For this kind of situtations, We have to use LEFT OUTER JOIN. This type of Join will include the NON-MATCHING ROWS also. The values for such non-matching rows will be NULL. This is the correct sql: Select e.emp_name,c.complaint from emp e left outer join complaint c on e.emp_id=c.emp_id

By Kalaimathi Shivaji OCA 17 Dec 2008