July 28, 2009 at 11:16 pm
I have a small question, probably a silly one.. When we join 2 tables using left join like below:
select t1.*
from table1 as t1
left outer join table2 as t2
on t2.id = t1.id
Here, it will select all the rows of table1. Unless I am selecting any fields from table2, is there any need to do this left join at all? In what cases, left join is useful if we are not selecting anything from table2?
July 29, 2009 at 12:19 am
If u want all the records from table 1 whether it does match or doesn't match with table2, Then u consider left outer join or right outer join. But if u want only matching records between both tables then use inner join.
In your example you are not showing any records of table 2, but if u want to show each and every record of table 1 then apply left outer join.
July 29, 2009 at 12:24 am
If I dont want any fields from table2 then left join doesn't make a difference here, right?
July 29, 2009 at 2:41 am
/*
This example will clear your doubts.
*/
---Creating tables
CREATE TABLE tbl1
(
IdINT,
FNameVARCHAR(1)
)
CREATE TABLE tbl2
(
IdINT,
FNameVARCHAR(1)
)
--Insert records to table
INSERT INTO tbl1
VALUES(1,'A')
INSERT INTO tbl1
VALUES(2,'B')
INSERT INTO tbl1
VALUES(3,'C')
INSERT INTO tbl1
VALUES(4,'D')
INSERT INTO tbl2
VALUES(1,'A')
INSERT INTO tbl2
VALUES(2,'B')
--Inner join will return only matching records i.e. t1.Id = t2.Id
SELECT t1.*
FROM tbl1 t1
INNER JOIN tbl2 t2
ON t1.Id = t2.Id
--Left outer join will return all the records of t1
SELECT t1.*
FROM tbl1 t1
LEFT OUTER JOIN tbl2 t2
ON t1.Id = t2.Id
July 29, 2009 at 2:17 pm
gyessql (7/29/2009)
If I dont want any fields from table2 then left join doesn't make a difference here, right?
Wrong. If the tables are in a one-to-one relationship then a left outer join will not make a difference. The same is true if table1 is on the many side of a one-to-many relationship, but if table 2 is on the many side of the one-to-many relationship, then you will get "duplicates" in your results that you would not get without a join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 30, 2009 at 7:57 am
Ok got it. Hadn't thought of the one-to-many relationship scenario. Thank you very much Drew.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply