Please explain the difference between outer join and inner
join? With examples would be appreciable..!
Answers were Sorted based on User's Feedback
Answer / mahima
Inner join: displays only matching record from 2 tables
Outer join:
Left outer join display all the records from 1st table even
if dont have matching record in 2nd table
rignt outer join display all the record from the 2nd table
even if there is not matching record in 1st table
Is This Answer Correct ? | 40 Yes | 2 No |
Answer / vamsi krishna
INNER JOIN DISPLAYS ONLY THE RECORDS WHICH ARE SATISFYING
THE JOINING CONDITION,WHERE AS IN AN OUTER JOIN IT DISPLAYS
THE RECORDS WHICH ARE SATISFYING THE CONDITION AND ALSO
WHICH ARE NOT SATIFYING THE CONDITION ALSO.
FOR EXAMPLE TAKE EMP AND DEPT TABLE.
INNER JOIN QUERY:
SELECT EMPNO,ENAME,SAL,DEPT.DEPTNO,DNAME,LOC FROM EMP INNER
JOIN DEPT ON(EMP.DEPTNO=DEPT.DEPTNO)
OUTER JOIN QUERY:
SELECT EMPNO,ENAME,SAL,DEPT.DEPTNO,DNAME,LOC FROM EMP RIGHT
OUTER JOIN DEPT ON(EMP.DEPTNO=DEPT.DEPTNO).
IN INNER JOIN IT DISPLAYS ONLY 10,20 & 30 DEPARTMENTS.
IN OUTER JOIN IT DISPLAYS 10,20,30 AND 40 DEPARTMENTS.
TAKE DEFAULT TABLES FOR EXECUTION
Is This Answer Correct ? | 39 Yes | 4 No |
Answer / khalek
In Inner Join Only Matching Records come while in Outer Join
All Matching Records + All Non Matching Record(Based on
Outer Join type).
Outer join may be any type from following.
1) Left Outer Join
2) Right Outer Join
3) Full Outer Join
You can see full detail at
<a
href="http://interview-preparation-for-you.blogspot.com/2010/11/difference-between-inner-joinouter-join.html">http://interview-preparation-for-you.blogspot.com/2010/11/difference-between-inner-joinouter-join.html</a>
Is This Answer Correct ? | 4 Yes | 3 No |
Answer / sunny
Inner join means it display all matching records in both tables depends upon condition....
Outer joins are three types..
1.left outer
2.right outer
3.full outer
In left outer:it will display all columns of right side table and matching columns of both tables.
In right outer:it will display all columns of left side table and matching columns of both tables.
In full outer:all matching non matching columns of both tables are displayed.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ashish sajwan
In Inner join the result is show only the matching columns which are common in both tables,In outer join the results show matched and unmatched columns from both tables
Is This Answer Correct ? | 3 Yes | 4 No |
Answer / sripathi venkata ramesh
I am Sripathi Venkata Ramesh from Gudivada
Answer is below
Joins
a) Inner join or default join or Equi Join:
It selects all the matching aswellas non matching records
from both the tables.
b) Outer join: It selects all the matching
aswellas non matching records from both the tables
depending upon the join type.
1) Left outer join:
It selects all the records from the left table but only the
matching records from the right table.
2) Right outer join: It
selects all the records from the right table but only the
matching records from the left table.
3) Full outer join:
It’s a union of left and right outer join.
c) Cross join or Cartesian join: Every row
in the first table joins with every row in the second
table. It does not have any condition. It is also called as
a Cartesian join.
d) Self join: Joining a table by itself is
called Self join. Here we have to use alias table.
Is This Answer Correct ? | 12 Yes | 24 No |
What is a package in oracle?
how many outer joins are used in a single sql query? is there any limitations for using of outer joins?
2 Answers BOB Technologies, Cap Gemini, IBM,
Explain compound trigger in oracle?
How much memory your 10g xe server is using?
How do we create privileges in oracle?
How to update values in a table in oracle?
1) What is dense ranking? 2) Difference between Substring and Instring? 3) Case and Decode? 4) Pseudo column? 5) View vs Materialized views? 6) SQl performance tuning?
if i have a procedure with 1000 line.when i run this in 61 line i got the error but i want to continue the programe without error.can anyone tell me how to handle that error.
What is a synonym? What are its various types?
How to create a stored function in oracle?
Explain an exception and its types?
query optmization techniques and quwry analyser+projects+ppts