Left Join & Right Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left Join & Right Join

Author

Topic

pssumesh2003
Starting Member

35 Posts

Posted - 2011-09-09 : 02:09:03

In an interview i got a question

2 tables TABLEA & TABLEB , tABLEA A Left outer join TABLEB B
ON A.ID = B.FK_AID and TABLE B RIGHT OUTER JOIN TABLE A
ON B.FK_AID = A.ID. Is these two query will give same result?
If yes then why left join & right join ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:14:56

yep. they will give same result. read about left join and right join. in former case you get all records from left side of query regardless of match from right side and in latter case you get all the records from right side whether or not they match a record in left. since the base tables in both cases are same (tablea) the query retrieves all records from tableA and matches if any from other. hence two queries are equivalent

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-09 : 02:24:26

Here is a small setup for you. The results will be the same.
What outer join does is it takes all the data from one table (matching or not matching) and join it with another table.
In case of LEFT OUTER JOIN it will take all the data from table present in left side(or first) and JOIN it with another table and in case of RIGHT OUTER JOIN it will take all the data from right sided table and JOIN data.

FOR MORE INFO ON OUTER JOINS REFER..
http://msdn.microsoft.com/en-us/library/ms187518.aspx

CREATE TABLE #tbl1(ID INT)
INSERT INTO #tbl1
SELECt 1 UNION ALL
SELECt 2 UNION ALL
SELECt 3 UNION ALL
SELECt 4 UNION ALL
SELECt 5

CREATE TABLE #tbl2(ID INT)
INSERT INTO #tbl2
SELECt 4 UNION ALL
SELECt 5 UNION ALL
SELECt 6 UNION ALL
SELECt 7 UNION ALL
SELECt 8

SELECT * FROM #tbl1 T1 LEFT OUTER JOIN #tbl2 T2 ON T1.ID=T2.ID

SELECT * FROM #tbl2 T2 RIGHT OUTER JOIN #tbl1 T1 ON T1.ID=T2.ID

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

pssumesh2003
Starting Member

35 Posts

Posted - 2011-09-09 : 02:55:35

ok thank u. result is same.
Then why we use two keyword left outer join & right outer join
we can use left as our application logic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 03:21:38

depends on scenario. in some cases you will have a complex query to which you need to join a table regardless of match and get all records from it. here you will use right join. however, in most cases you will be able to replace one with equivalent query using other

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources