Question

Course: Fundamentals of Databases

Define Outer Join and List out Various type of Outer Join

Answer 1

OUTER JOIN :- First u have to know what is inner join. if you know inner join u can easily understand what is outer join and advantage. inner join: -"In this join wherever the rows are matching or values are matching those records will be displayed in the output." remember this point -" "non-matching rows are not displayed in the output Example select from emp inner join dept on emp.deptno-dept.deptno; if you run this query, u can display only matching records from emp table & dept table. (we can't display 40 deptno,dname,loc record from dept table.) now coming to outer join :: in inner join there might be a chance of loosing some information (40 deptno,dname,loc from dept table), to recover that loss information we need to go for outer joins." outer joins are broadly divided into 3 type. left outer join right outer join full outer join. 1. lft outer join: this join is a combination of inner join operation + loosing information from the left hand side table. ansi-style ofleft outer join Select from emp left outer join dept on 2. right outer join: this join is a combination of inner join operation+ loosing information from right hand side table. ansi-style ofright outer join Select from emp right outer join dept on emp.deptno dept.deptno; If we run this right outer join query we get loosing information (40 deptno,dname,loc) with inner join operation. 3full outer join: this join is a combination inner join operation+loss information from left hand side table and loss information from right hand side table. ansi-style of full outer join SELECT FROM EMP FULL OUTER JOIN DEPTON EMP.DEPINO-DEPT DEPINO;