NEW INTERVIEW QUESTIONS.COM DATABASE INTERVIEW QUESTIONS SQL SERVER INTERVIEW QUESTIONS DETAILS

Add to My Questions | Forward | Print

Question

What is the difference between Inner join and full outer join in terms of the Number of rows returned?

Answer

Inner Join will return rows that have matching records in both table, but in case of full outer join it will return all records from left table and from right table. In Left-Outer join all the record from the left-most table will be fetched and if records found in the 2nd table then field values will be displayed else it will display Null in the 2nd table fields. Ex. Create table EMPNAME (ID int, EMPNAME varchar(20)) CREATE table EMPADDRESS (ID int, ADDRESS varchar(20)) INSERT INTO EMPNAME VALUES(1, 'DEEPAN') INSERT INTO EMPNAME VALUES(2, 'RAMESH') INSERT INTO EMPNAME VALUES(3, 'PRADEEP') INSERT INTO EMPADDRESS VALUES(1, 'BANGALORE') INSERT INTO EMPADDRESS VALUES(2, 'DELHI') INSERT INTO EMPADDRESS VALUES(4, 'DELHI') SELECT * FROM EMPNAME ID EMPNAME ----------- -------------------- 1 DEEPAN 2 RAMESH 3 PRADEEP SELECT * FROM EMPADDRESS ID ADDRESS ----------- -------------------- 1 BANGALORE 2 DELHI 4 DELHI Example : INNER JOIN =================================================================== SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID Output of the above JOIN Query (i.e Inner Query) ----------------------------------------------------- ID EMPNAME ADDRESS ----------- -------------------- -------------------- 1 DEEPAN BANGALORE 2 RAMESH DELHI Example : FULL OUTER JOIN =================================================================== SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME FULL OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID Output of the above FULL OUTER JOIN Query ----------------------------------------------------- ID EMPNAME ADDRESS ----------- -------------------- -------------------- 1 DEEPAN BANGALORE 2 RAMESH DELHI 3 PRADEEP NULL NULL NULL DELHI Example : LEFT OUTER JOIN =================================================================== SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME LEFT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID Output of the above LEFT OUTER JOIN Query ----------------------------------------------------- ID EMPNAME ADDRESS ----------- -------------------- -------------------- 1 DEEPAN BANGALORE 2 RAMESH DELHI 3 PRADEEP NULL Example : RIGHT OUTER JOIN =================================================================== SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME RIGHT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID Output of the above RIGHT OUTER JOIN Query ----------------------------------------------------- ID EMPNAME ADDRESS ----------- -------------------- -------------------- 1 DEEPAN BANGALORE 2 RAMESH DELHI NULL NULL DELHI merge means combine the two data floders adding Answered By: nagaraj    Date: 4/2/2007 © NewInterviewQuestions.com

Answer:*
Name:*
Email Address:*
Verification Code	Not readable? Load New Code
Enter the above shown code:*

Category	SQL Server Interview Questions
Rating	(5.0) By 2 users
Added	8/12/2006
Views	153742
Rate it!	* * * * * * * * * * * * * * *
Free Offers:	300 Free Magazines for you. No credit card required. No Shipping Charges. Absolutely Free for 1 Year.

Please Note: We keep on updating better answers to this site. Subscribe to our newsletter to get notified when better answer is posted.

Notify me when better answer is posted!
Email: